Use/Abuse of Nulls

In the last exciting episode, Jon(dot)Ericson(at)jpl(dot)nasa(dot)gov (Jon Ericson) wrote:
> Christopher Browne <cbbrowne(at)acm(dot)org> writes:
>> At one extreme, Chris Date holds to the position that NULLs ought to
>> be forbidden outright. He does make a fairly credible case for it,
>> albeit with the problem that when you forbid NULLs, you have to
>> replace them by making the data model a little more complex. (He
>> recently released a paper on how to do this; there's not much
>> surprise to it; whenever a column "could be NULL," you have to split
>> it off to a separate table so that its omission amounts to not
>> bothering to populate the new table...)
>>
>> The other "major" position is that there should be multiple sorts of
>> 'NULL' values to indicate different forms of missing information.
>> (One problem with NULL is that you can't easily distinguish between
>> "I left that NULL because I didn't know the value" and "That's NULL
>> because that's how we say it's 'empty.'")
>>
>> I fall more into the pragmatic position that "NULL columns have the
>> potential to cause a lot of confusion; use NOT NULL when you can,
>> and be wary when you can't."
>
> Interesting. The bullet caught my eye because I am currently working
> with a table that has, in my opinion, poorly thought out NOT NULL
> constraints. When I do the initial insert I have to use 0 to mean
> both "I don't know the value yet" and "this is how we say empty"! It
> occurs to me that adding a cross-reference table would not only let me
> avoid NULL, but also solve a couple of other problems as well.

Actually, I misattributed that.

The paper on dealing with 'missing information' without using NULLs is
by Hugh Darwen. (He and Date do a lot of work together, so it's
honest confusion :-).)

<http://www.hughdarwen.freeola.com/TheThirdManifesto.web/Missing-info-without-nulls.pdf>

It's worth a read. I am not sure it TRULY gets around the problems
with NULLs, but there certainly are some ideas there worth looking at.
Food for thought, if not a perfect prescription for a permanent
doctrine on the matter.

The problem I see with the "make another table" approach is that you
wind up with another table for everyone to manage. More data to join;
more tables to add data to; none of that comes for free, even if it is
cheap, performance-wise.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://www.ntlug.org/~cbbrowne/x.html
Did you hear about the Buddhist who refused his dentist's novocaine
during root canal work? He wanted to transcend dental medication.