Nullable foreign keys vs relational tables for N:M relations

I am a database designer with an existencial doubt.

If you have a table1 that must have a relation with table2 or (exclusive or, one or another) table3,

• which aproach and why would you choose for high read performance?开发者_C百科

  

Is knokn that nullable indexed fields (option A table1) are a bad decision (see O'Reilly High Performance MySQL Chapter 3 or MySQL manual), but also is known that a join would take its time to execute (option B)...

Academical choice would be B, but i would like a real world explanation if it is really better for high performance or not.

Thanks in advance!!

---

Avoid nullable "foreign keys". They have multiple disadvantages.

The constraint on a referencing row is not always enforced when the foreign key contains a null. However, that default behaviour is not consistent between different DBMSs. Some DBMSs support configuration options to change the behaviour of nullable foreign keys and some do not. SQL developers and users may therefore be unclear about what a nullable foreign key constraint actually means from a data integrity perspective. Porting the database between DBMS products or even between different servers using the same product could give inconsistent results.

Database design tools, integration tools and other software don't always support them correctly and the results they produce may be wrong.

Foreign keys are frequently used in joins and other query logic, compounding the problems for users who think the constraint is in effect when it isn't.

In logical terms, a nullable "foreign key" constraint doesn't make much logical sense. According to the SQL standard such a constraint may not be violated even if the table being referenced is empty. That contradicts one of the most common alleged justifications for using a null - that it represents the "unknown" case. If there are no valid values of X then any "unknown" X certainly cannot be a valid value - and yet SQL will permit it.

It's unnecessary. You can always construct the tables so that a null isn't needed. In the interests of simplicity and accuracy it is therefore better to leave nulls out than put them in.

---

In practice, the suitable design for performance depends on how "weight" your data is accessed.

Using "Table Inheritance" is suitable when the part of data(table2 or table3) is accessed frequently. Using "Nullable FK" is suitable if all of the data(whatever table2 or table3) is accessed frequently.

---

The "Nullable FK", however, could be established by view based on "Table Inheritance".