Re: Use/Abuse of Nulls

Folks,

First off, we should probably really be having this discussion on the SQL
list.

Well, there are two seperate issues with NULLs:

1) The tri-value problem;
2) Abuse of normalization

1) Is the problem that NULLs were implemented in SQL89 to *strictly* mean
"unknown" or "undefined"; that is, values that existed but were not available
to the database. Unfortunately, the ANSI committee ignored the need for a
"Not Applicable" value despite the rather primitive support for fk
relationships at the time. This has resulted in people using NULL to
represent *both* "unknown" and "not applicable", meaning that you can't tell
what is actually meant by looking at the NULL. We really should have had
two values, UNKNOWN and IGNORE.
Certainly, with text fields it's easy to enforce not-nullness and make the
user select "Unknown" and "Ignore" as string values. However, it's difficult
to come up with similar values that work for numbers, dates, or network
addresses.

2) Given that the above abuse of NULLs is already built into the SQL standard,
DBAs feel free to further abuse NULLs. For example, a couple of weeks ago a
developer posted a performance problem to the PERFORM list. As it turns
out, he had a table with 635 columns, of which 75% were NULL for any given
row (this, BTW, was the source of his performance problem). While this sort
of not normalized design is not required by the NULL standard, it is made
available and many junior DBAs exploit it.

--
-Josh Berkus
Aglio Database Solutions
San Francisco