1

I want to know which one is better for high read performance. Foreign key in one table have high read performance or another table with composite key?

In Figure1 "idInvoice" is nullable some Sanads come without Factor

Q1- For example I want to list all Notes Invoice or Find is there any Invoice for special Note.

Q2- Which one is better if "idInvoice" can not be null.

Figure1

Table Invoice:

idInvoice //Primary key
invoiceNumber
value
costumerId

Table Note:

idNote //Primary key
type
liable
credit
idInvoice // Foreign key (Nullable)

OR

Figure 2

Table Invoice:

idInvoice //Primary key
invoiceNumber
value
costumerId

Table Note:

idNote //Primary key
type
liable
credit

Table InvoiceNote:

idNote //composite key - Foreign key
idInvoice //composite key - Foreign key
5
  • Which version of sql server are you using?
    – SqlZim
    Dec 18 '16 at 17:14
  • @SqlZim 2014 or 2016 version
    – Sinoheh
    Dec 18 '16 at 17:27
  • Can you add example queries for Q1 and Q2 to clarify? Can you explain why in one scenario idFactor could be null and in one instance it couldn't -- e.g. by design or are you considering a dummy id to take the place of null?
    – SqlZim
    Dec 18 '16 at 17:34
  • @SqlZim This is accountancy table one is invoice(factor) an another is note(sanad) payable/receivable table. All note not contain invoice ID but all invoice have note id. Therefore many queries are about summation of invoice table or request details of special user invoices or special invoice at all. I am at design step and prefer to select best method
    – Sinoheh
    Dec 18 '16 at 18:13
  • @SqlZim question updated
    – Sinoheh
    Dec 18 '16 at 18:19
2

These two schemas represent different business rules. The first, with idFactor as a FK in Sanad, allows each Sanad to be connected to at most one Factor (if the foreign key column is nullable) or exactly one Factor (not nullable). The second, with a separate table, allows a Sanad to connect to zero, one or many Factors. Of course the second model is a superset of the first and can be limited to zero-or-one. There is no trivial way to do this in SQL Server, however. It will required additional constraints and triggers, meaning extra programing, debugging and maintenance.

You should implement the model which matches your business rules. This will force the data to be correct. It also self-documents the intent of the application. Sufficient performance can be achieved through sensible SQL and indexes.

To answer the actual question, it is likely to vary according to the ratio of reads to writes and whether Sanad field1, field2 etc. are returned. In schema 2 each row inserted in the intersection table will require two FKs to be checked i.e. two table reads. In schema 1 there will be 1 FK read. To check only the existence of a Sanad/Factor connection may be faster in schema 2 because the rows are shorter, there will be more rows per page and hence a better chance that the required page is in memory. This only applies if there is a small working set of "hot" rows. To read the data columns in scenario 2, however, will need reads of both interesction and Sanad tables; double to IO.

So, if working set size is bigger than RAM, and the vast majority of reads only check for existence of a link, and there are relatively few writes, and you're confident to do the extra programing to ensure data integrity, or you do actually have a many-to-many relationship, go with schema 2, otherwise stick with schema 1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.