Foreign key in one table have high read performance or another table with two primary key?

Question

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

Answer 1

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.