Question :
On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: OrdersForProductAs, OrdersForProductBs, OrdersForProductCs. All orders table has one to many relationship on Customers table.
I also have another table which is Payments and will hold the payment details inside. But I have doubts here on how to structure it.
As I have multiple product types and a customer may have orders for multiple products at the same time, I need to relate those three order tables to Payments table.
The other issue is that a customer may have an order for only one type of product. So, the FK columns on Payments table needs to be nullable.
My question is whether those nullable FK columns would be a headache for me on the long run or not? Generally speaking, would it be considered as a bad practice to have nullable FK columns on a table?
Answer :
I’d question why you have OrdersForProductX tables at all
It’s possible the FK problem you’ve asked about can be designed out…
If these tables have the same structure, then you simply need a ProductType column on some OrderProduct table. Then Payment just links to that with one FK
If the table have different structures, I assume they have some common attributes. So, you can have a common OrderProduct table then specific child table per product type (see below) Again, Payment just links to the commone table with one FK
This is the “superkey/subtype pattern”
- UQ1 is the “super key” used a foreign key on the subtype tables
- Each subtype table has a composite PK and FK on
(OrderID, ProductType) - Each subtype table has a CHECK constraint to restrict types in that table
OrderProduct
- OrderID, PK, UQ1
- ProductType, UQ1
- CommonThing1
- …
OrderProductA
- OrderID, PK, FK
- ProductType, PK, FK, CHECK ProductType = A
- ProductAThing1
- …
OrderProductB
- OrderID, PK, FK
- ProductType, PK, FK, CHECK ProductType = B
- ProductBThing1
- …
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 or who don’t know the logic being applied by your particular DBMS.
Some query optimization features allowing query rewrites and other optimizations may be unavailable when a foreign key is nullable.
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.
Nullable foreign keys are completely unnecessary. You can always either decompose the foreign key to a new table or use a supertype/subtype pattern so that nulls aren’t needed. In the interests of simplicity and accuracy it is therefore better to leave nulls out than put them in.
I have never heard it considered bad practice to use nullable FK columns. They’re an ideal fit for a column which references another table but might not be filled in (i.e. it’s optional data).
(Why do you think it would be a problem?)