Re: NULLs: theoretical problems?

On Wed, 8 Aug 2007 06:43:53 -0400, sinister wrote:

Many discussions point out one deficiency of NULLs: that they collapse
multiple, distinct concepts into one ("no value possible," "value missing,"
"value not available at this time", etc).

Hi sinister,

The deficiency in these discussions is that they fail to normalize the
design before starting to look at NULLs.

The ANSI standard defines NULL as follows:

"null value: A special value that is used to indicate the
absence of any data value."

Thus, the only concept collapsed into NULL is that of "no value here".

The concepts you mention are, indeed, various reasons why a value might
be missing. The specific reason might or might not be of interest to the
users of a database. If it's not, there's no need to let it bother us;
if it is, we need to properly model it.

<Sidestep>

How would you react if someone showed you a database model where the
contracts table had a single column to store both termination date and
the reason why the contract was terminated - I'm pretty sure that you'd
send the junior DBA who made that model back to class to study
normalisation rules, right?

How then would you react if you saw a model that tries to combine the
termination date and the reason why the contract is NOT terminated in a
single column?

</Sidestep>

And yet, that is exactly what some people try to do in the discussion of
NULLs. They see that a value might be missing (NULL) for various
reasons, which is of course correct. They assume that the reason why the
value is missing should be somehow stored or encoded in the database,
which is not necessarily correct but might be requireed for some
applications. And then they go on to state that this reason should be
stored or encoded in the same column where the missing value should have
been - i.e. they want to use a single column to store both the
termination date of a terminated contract and the reason why there is no
termination date of a (probably not terminated, though that's not even
sure) contract. And that is of course a violation of 1NF.

Unfortunately, many database theorists, including Codd and Date, either
failed to see this basic flaw in the discussion or deliberately
sidestepped it for convenience. This has led to many pointless
discussions on using A-marks and I-marks, and how that would lead to
fourvalued logic and still not suffice, and so on....

I made this same point, but in different words, about a month ago on my
blog:
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx

Best, Hugo
.

Relevant Pages

  • Re: NULLs: theoretical problems?
    ... be missing. ... The specific reason might or might not be of interest to the ... the reason why the contract was terminated - I'm pretty sure that you'd ... termination date and the reason why the contract is NOT terminated in a ...
    (comp.databases.theory)
  • Re: Liking for their skin color
    ... >> Advice about legal resources is acceptable, ... meaning of "termination" is intended when I say she can't give advice ... used in this house because my wife is a nurse and the term is what ... I don't deny that it might be a reason, but I don't accept it as *the* ...
    (alt.usage.english)
  • Re: Conspiracies theory surrounding the quitting of international co-prosecutor Robert Petit fro
    ... Either I quitted for one reason or another or the company terminated ... This is the nature of contract work. ... We know that the Cambodian government has been trying to get ... The remnant band of the Khmer Rouge community ...
    (soc.culture.cambodia)
  • Langkawi firm sues Defence Ministry
    ... Langkawi firm sues Defence Ministry for breach of contract ... the minister and the government as defendants. ... sub-contractor had led to the early termination in violation of the 10-year ... The plaintiff contracted to undertake avionics upgrading and refurbishment ...
    (soc.culture.malaysia)
  • Re: Sheffiel interview.
    ... start bitching after he signs the contract. ... his reason for it I'm happy about that. ... perhaps rotate Jorge, Jason and Andy. ... Who are the free agent catchers? ...
    (alt.sports.baseball.ny-yankees)