Nullable Foreign Keys

[Jonathan Wood]

I just wondered if anyone here has had experience with nullable foreign
keys.

I have a number of items and I want some of the items associated with a user
while other items are not associated with any user. After thinking about
this, it seems like the easiest approach is to put all items in the same
table and give that table a foreign key to the user's table, but allow that
foreign key to be NULL for when the item is not associated with a user.

This seems pretty straight forward but there's a few things that bother me.
Just thought I'd check if anyone sees any potential problems to this
approach.

Thanks.

 

[Rick]

Just my opinion, but I would put an extra row in the Items table for "not
assigned" and use that for the foreign key in the User table.

A nullable foreign key, if allowed, in your db does not seem like a proper
design. It is in effect saying "The User table as a dependency on an Unknown
row in the Items table".

Rick

 

[Kerry Moorman]

Rick,

A null value in a foreign key column is a completely proper database design.
In fact the very definition of a foreign key is that it must point to a
unique row in another table or be null.

Actually, using a "not assigned" row is improper design.

Kerry Moorman

 

[Jonathan Wood]

I'm confused by this reply. Putting an extra row means adding another record
(did you mean column?). How can another record address this issue?

 

[Jonathan Wood]

Thanks for your comments.

 

[Radek Cerny]

I think what he means is exactly what I do, in that a PK value of 0 (not
null) is the virtual null row, and so an FK that is virtually null has a
value of 0.

This is a policy I have used for over 10 years with great success. You will
never 'lose' data because of a missing outer join.

 

[Rick]

Kerry,

I realize it CAN be done, I was just saying I would do it differently so
that a null FK never happens.

Radek makes a good point that Inner and Outer Joins will never "loose" data
because of a null FK.

Anyway it is just a matter of personal preference and coding style.

Rick

 

[Kerry Moorman]

Rick,

Surely in your database design course you were taught that the techinique
you are describing is poor database design?

This isn't any more a matter of personal preference than designing a table
with repeating groups would be.

Unlike much of programming, relational database design really does have a
set of well-defined design principles that distinguish good design from poor
design.

Of course, poorly designed databases can work just fine. Until they don't.

Kerry Moorman

 

[Jonathan Wood]

If I follow, you are talking about, in effect, creating a "false" user that
is linked to items not associated with any users.

If that is correct, it makes good sense. I could even have the database then
enforce referential integrety. (Although I may have some issues to work out
in the practical sense.)

Thanks.

 

[Jonathan Wood]

Right. Thanks.

 

[Jonathan Wood]

Kerry,

Surely in your database design course you were taught that the techinique
you are describing is poor database design?

This isn't any more a matter of personal preference than designing a table
with repeating groups would be.

Perhaps you can be encouraged to address the specific problems that you see
with this approach. I do not see how this is the same as repeating
groups--what is being repeated? Why is this a problem, specifically? And how
would you address it?