Database Design: FKs that are NULL - good or bad?

Question

Assume I have a "message" table wherein I want to store all possible messages that my application supports. For example, in a FB-like application, there might be a "media message" (e.g. photo, video, audio), "media album message", "private message", etc. One way to model this is this:

Table: message
- messag_id (PK)
- user_id (FK, this is the sender of the message)
- message

TABLE: media_message
- media_message_id (PK, receiver of the message implied by the owner of the media)
- message_id (FK)

TABLE: media_album_message
- media_album_message_id (PK, receiver of the message implied by the owner of the media album)
- message_id (FK)

TABLE: private_message
- private_message_id (PK)
- message_id (FK)
- user_id (FK, receiver of the message)

... etc.

So, for every "type" of message, I would need to create some sort of "mapping table".

Would something like this work:

TABLE: message
- message_id (PK)
- receiver_user_id (FK)
- sender_user_id (FK) 
- message
- media_album_id (FK, NULL allowed)
- media_id (FK, NULL allowed)

I think with this design I could still enforce referential integrity. If media_album_id and media_id are both NULL, then I can assume it's a "private" message (or implement some similar logic in the application layer). The down side, if it's even a down side, is that there will always be columns not used. Then again, maybe this will scale better -- less JOINs, etc.

Thoughts?

Answer 1

Bad. 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.

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!

Finally, it's unnecessary. You can always construct the tables so that a null isn't needed. In the interests of simplicity and accuracy it is therefore better to leave nulls out than put them in.

Answer 2

FK is a referential constraint setup between 2 tables to ensure the data integrity. Having say that, FK can not be NULL. If media_album_id & media_id are nullable, then they should not be FK.