3

I'm trying to learn about proper database management and am having a hard time with designing a database that doesn't rely on NULLs. If you think I'm crazy for embarking on this, it is because of SQL and Relational Theory: How to Write Accurate SQL Code (ch.4, p.79), where the author makes the case that simply because having NULLs produces unexpected results in certain situations, they should be avoided.

The alternative to NULL-able fields, as I understand, is to create 1::0-1 relationships for each field. This seems like it would make querying information very difficult. Is this the correct approach? Are NULLs, in fact, much safer than the author leads me to believe?

Would using a VIEW to represent the many 1::0-1 relationships be applicable and any more beneficial than simply having the NULLs in table in the first place?

My scenario is a user table with optional fields that may, or may not, be foreign key relationships to other tables. Obviously, making those fields NOT NULL means they must be required, but I can't put 0 in there since it breaks the foreign key. Other situations occur when trying to put 0000-00-00 for, say, a birthdate field, since MySQL doesn't allow me to do that.

Thus, I'm back to wondering if eliminating NULLs are, indeed, a best practice and provide the greatest flexibility and performance than the alternative.

jeffjenx
  • 131
  • 1
  • 4

1 Answers1

2

I beg to disagree with this author. The results are expected, but may not be the desired values. Null values don't work well with logical comparisons as the value is unknown, as is the result of a comparison.

If you are using nullable columns as query criteria, you do need to handle them appropriately. These are some of method I have used:

WHERE   (nullable = ? OR nullable IS NULL)
WHERE   coalesce(nullable, -1) = ?
WHERE   nvl(nullable, -1) = ?

EDIT: I do agree with the author that avoiding NULL values on searchable fields is desirable, but designing a database without NULL columns can result in other issues. As design goals, avoiding duplicate and NULLs are appropriate, However a database row is a representation of something else, sometimes an imperfect representation.

For code lists values like "Unspecified", "Unknown", "Not applicable" and "To be determined" may be appropriate. If NULL has meaning, then it is may be appropriate to use that meaning rather than NULL for the column value.

You may also want to consider whether the data has value. I do encounter data which has been gathered because it could be, but is neither used nor useful. Consider the privacy issues or other legal issues resulting from gathering the data.

Nullable columns are common when subtypes occur in the same table. Understanding the subtypes helps in deciding on the appropriate approach to handling these columns. Using separate tables for each subtype can make the design more complex.

BillThor
  • 4,518
  • 20
  • 12