01 November 2006

Null

I have adopted the practice of not allowing nulls in lookup tables and foreign keys to those lookups. The reasons for this are:
  1. Application logic is simplified for drop downs and the like that allow a blank value. For example, drop downs used on search screens to select from a finite and known list should allow a user to reset the selection by picking the blank row. Data access and business layer code also benefits greatly as anyone who has implemented typical "if (dRow("colName") != DBNull.Value) ..." knows.
  2. Query logic is simplified. Outer joins are not required.
  3. Query performance and indexing is faster because nulls are not allowed.
So in a typical lookup, I create a "zero" row where the ID is 0 and the value is an empty string. Life is so much better this way.

In fact, if it were not for a couple specific issues, I would extend my reasoning to say that nulls in any database table are bad. The DateTime issue is unfortunate, because there may be times when you legitimately do not have a date value and "1/1/1753" won't cut it as an indicator for that. Also, calculations on columns when data is legitimately not known really needs to allow nulls so that null data is excluded from the calculation. I can't readily think of other exceptions though. Something to ponder.

No comments: