I'm a database designer by having an existencial doubt.
For those who have a table1 that has to possess a relation with table2 or (exclusive or, one or two) table3,
- which aproach and why can you decide for high read performance?
Is knokn that nullable indexed fields (option A table1) really are a bad decision (see O'Reilly High End MySQL Chapter 3 or MySQL manual), but additionally is famous that the join would take it is time to complete (option B)...
Academical choice could be B, but i'd like a real life explanation if it's really better for top performance or otherwise.
Thanks ahead of time!!
Avoid nullable "foreign secrets". They've multiple disadvantages.
The constraint on the referencing row isn't necessarily enforced once the foreign key consists of a null. However, that default behavior isn't consistent between different DBMSs. Some DBMSs support configuration choices to alter the behavior of nullable foreign secrets plus some don't. SQL designers and customers may therefore be puzzled by exactly what a nullable foreign key constraint really means from the data integrity perspective. Porting the database between DBMS items as well as between different servers utilizing the same product could give sporadic results.
Database design tools, integration tools along with other software don't always support them properly and also the results they produce might be wrong.
Foreign secrets are often utilized in joins along with other query logic, adding to the issues for customers who think the constraint is within effect if this is not.
In logical terms, a nullable "foreign key" constraint does not make much logical sense. Based on the SQL standard this type of constraint might not be violated even when the table being recommended is empty. That opposes probably the most common alleged justifications for implementing a null - it signifies the "unknown" situation. If you will find no valid values of X then any "unknown" X certainly can't be a legitimate value - but SQL will grant it.
It's unnecessary. You could construct the tables to ensure that a null is not needed. Within the interests of simplicity and precision therefore, it is easier to leave nulls out than insert them in.
Used, the appropriate design for performance is dependent how "weight" your computer data is utilized.
Using "Table Inheritance" is appropriate when negligence data(table2 or table3) is utilized frequently. Using "Nullable FK" is appropriate if all the data(whatever table2 or table3) is utilized frequently.
The "Nullable FK", however, might be established by view according to "Table Inheritance".