Re: What does this NULL mean?

From: John Eggert <finarfinjge_at_hotmail.com>
Date: Sun, 11 Dec 2005 08:15:23 -0500
Message-ID: <11po9j4qormaleb_at_corp.supernews.com>


dawn wrote:

>
> paul c wrote:

>> dawn wrote:
>> > Eric Junkermann wrote:
>> >
>> >>...
>> >>
>> >>The best way to look at a NULL is as a sort of denormalisation. If we
>> >>have a table X {A, B, C} where A is the key, B is a column we are not
>> >>currently interested in, and C is the column which is NULL in at least
>> >>one row, then we are really talking about two tables X1 {A, B} and Y
>> >>{A, C}, where at least one row in X1 does not have a corresponding row
>> >>in Y.
>> >
>> >
>> > Within relational theory, I think you are exactly right that it is a
>> > normaliztion issue and there is no place for nulls in a fully
>> > normalized model.  That is the position I have taken within the context
>> > of relational theory.  Outside of the RM (where I prefer to live), I
>> > see it differently.
>> >
>> >
>>
>>
>> So nulls are okay in a not-"fully normalized' relation, eg. one in 2NF?

>
> By most definitions it isn't even in 1NF, right? I incorrectly used
> "fully" to refer to the breadth of the normalization across a domain
> rather than the depth.
>
> If the dbms tools permit implementations that are not normalized then
> someone will make a call at some point that this "feature" is worth
> exploiting. There should be an interpretation for every possible
> implementation, I would think. --dawn

Ok, I've been somewhat ignoring this entire topic lately, but I have a real world situation that I'd like to see a solution for. I am currently moving the operator reporting for an industrial process from a paper system to a computer based system. One thing that is occuring with the paper system is:

An operator does what are called 'rounds'. They do certain things at a certain time. Most of these are manual checks of various circuit parameters. Take one of these as an example: The amount of activated carbon per tank ( I like this one because there is no automated method for this measurement at this time ). They then write this number down. On occasion, it is not possible to get the value. They are usually busy somewhere else. 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?

With respect to the normalization aspect. How can one better normalize a system so that one can capture the fact that someone did not fill in something that should normally have been filled in but for unknown reasons did ( and possibly could ) not? If I have single attribute tables I can still have the missing data. How can I further normalize a single number?

Cheers.

JE Received on Sun Dec 11 2005 - 14:15:23 CET

Original text of this message