15

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?

tugberk
  • 263
  • 2
  • 7

3 Answers3

15

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
  • ...
gbn
  • 69,809
  • 8
  • 163
  • 243
5

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.

nvogel
  • 3,777
  • 20
  • 25
  • ”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.” - But if we do as you suggest, then that may lead to having an excessive amount of tables that just hold a couple of columns, which can massively complicate DML and queries (what with needing extra joins) which in-turn negatively impacts performance (especially w.r.t. data-locality). Secondly, always using a new table for optional relationships means now you can’t use a CHECK constraint to prevent an illegal FK. – Dai Jul 11 '23 at 23:31
2

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?)

StilesCrisis
  • 137
  • 2
  • Thanks! Well, I am not quite sure on that but I had a project like that couple of years ago and I remember that I was having headaches on something. So, I am not clear as you see:s what's why I asked the question. –  Feb 20 '12 at 07:50