Re: What does this NULL mean?

From: Hugo Kornelis <hugo_at_pe_NO_rFact.in_SPAM_fo>
Date: Mon, 12 Dec 2005 01:10:40 +0100
Message-ID: <jifpp19f2o875nupe0sg491v763hc0rp4n_at_4ax.com>

On Sun, 11 Dec 2005 08:15:23 -0500, John Eggert wrote:

(snip)
>This information (the fact that the operator missed a round) is indicated
>by the blank cell in the hand written report sheet. I would interpret this
>as a NULL value. How can I capture the same data (the missing information
>IS the data) in a database? Just to be clear, the empty cell in the
>completed operator's report sheet IS USEFULL DATA. It isn't 0, it isn't
>unknown, it is missing data. The fact that >>the data is missing<< is the
>data I need to capture. I need to be able to quantify how frequently the
>data is missing, if it is any particular operator or any particular part of
>the process etc. I am using "NULL" as a default value. Given the long
>debate on the good bad and ugly of NULL, what is wrong with that? Isn't
>this exactly the situation where a NULL value is needed, appropriate and
>functionally usefull?

Hi John,

If there is only one possible reason why the information is missing, or if there mightr be more reasons but nobody cares WHY the information is missing, then yes - this is exactly the situation where a NULL value is appropriate and functionally usefull.

It's not "needed" though. Instead of the design   CREATE TABLE MyTable

        (KeyColumn somedatatype NOT NULL,
         Data1     somedatatype NOT NULL,
         Data2     somedatatype NOT NULL,
         PossiblyMissing somedatatype,
         PRIMARY KEY (KeyColumn))

You could use for instance this design
  CREATE TABLE MyTableWithPossiblyMissing

        (KeyColumn somedatatype NOT NULL,
         Data1     somedatatype NOT NULL,
         Data2     somedatatype NOT NULL,
         PossiblyMissing somedatatype NOT NULL,
         PRIMARY KEY (KeyColumn))
  CREATE TABLE MyTableWithoutPossiblyMissing
        (KeyColumn somedatatype NOT NULL,
         Data1     somedatatype NOT NULL,
         Data2     somedatatype NOT NULL,
         PRIMARY KEY (KeyColumn))

and insert data in either the first or the second table, depending on whether the operator missed his round or not.

Or you could use
  CREATE TABLE MyTable

        (KeyColumn somedatatype NOT NULL,
         Data1     somedatatype NOT NULL,
         Data2     somedatatype NOT NULL,
         PRIMARY KEY (KeyColumn))
  CREATE TABLE PossiblyMissing
        (KeyColumn somedatatype NOT NULL,
         PossiblyMissing somedatatype NOT NULL,
         PRIMARY KEY (KeyColumn),
         FOREIGN KEY (KeyColumn) REFERENCES MyTable(KeyColumn))

and insert each round in the first table, but only those where the operator collected the possibly missing fact in the second.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)

Received on Mon Dec 12 2005 - 01:10:40 CET