Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Feb 20 '12 at 15:20

This question came from our site for professional and enthusiast programmers.

2  
Make sure you include a check constraint so that at least one of those FKs isn't null. –  Damien_The_Unbeliever Feb 20 '12 at 8:10
    
Nice advice! Thanks! –  tugberk Feb 20 '12 at 8:12
2  
One nullable foreign key is one too many. –  sqlvogel Feb 20 '12 at 22:33

3 Answers 3

up vote 11 down vote accepted

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
  • ...
share|improve this answer
3  
@tugberk: Notice that you will have no NULL FK columns with this approach. –  ypercube Feb 20 '12 at 9:21
    
"If these tables have the same structure" They do not have the same structure. –  tugberk Feb 20 '12 at 17:10

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

share|improve this answer
    
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. –  tugberk Feb 20 '12 at 7:50
2  
+1. There is nothing technical bad or bad from a relational theorem point. Whether the OP's specific design is rotten, stupid or elegant I can not judge here - and it is not part of the question. Your answer is correct. At the end, the model follows the reality and if there is a need for that, it follows the relational theory and servers have no problems with that. –  TomTom Feb 20 '12 at 7:53
1  
@lugberk what is a pain is sql server "all null are equal" which makes indices hard. That said since 2008 you can define a filter on indices, so you have a non-unique index on fields with null and a non-unique on the fields without. SQL standard would demand null != null for indices (unknown is not equal unknown) and not have this problem - it is a leftover from the sybase days (sql server started as sybase database). –  TomTom Feb 20 '12 at 7:55
2  
@TomTom: "There is nothing technical bad or bad from a relational theorem point... it follows the relational theory" -- you are mistaken. "Relations never contain nulls, because the body of a relation is a set of tuples, and tuples in turn never contain nulls" -- stated in any Chris Date book you care to read. –  onedaywhen Feb 21 '12 at 16:14

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.