Re: What does this NULL mean?

Eric Junkermann wrote:
> The number of long threads about NULLs indicates that they are a source
> of difficulty and disagreement.
>
> I have been prompted to start yet another by the www.dbdebunk.com quote
> of the week for December 10, 2005 . It says
>
> "There have been, there are, and there
> always will be NULLS in the real world."
>
> and is attributed to "Pete Brown, dbmonster.com",

mountain man, right?

> but it is from a
> comp.databases.theory thread "Does Codd's view of a relational database
> differ from that of Date & Darwin?" on June 4, 2005 .
>
> At the time, I followed up with a request for a definition and examples,
> but I never pursued the answer I got (which I thought less than
> satisfactory). My favourite bit out of the rest of the thread was from
> Ged Byrne, who said
>
> ... If my Manager points to a NULL in a report, I don't say 'That is a
> NULL.' I say something like 'That customer is retired, and does not
> have a work address' or 'The user left that field blank.' That is if I
> am familiar with the data. If I am unfamiliar then the NULL tells me
> nothing, I can only shrug my shoulders and say 'Sorry, I don't know.'
> ...
>
> Finally, this leads to where I want to be with this post:
>
> A NULL does not actually provide enough information to interpret it
> properly, additional knowledge is required.
>
> 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.

> (This is like vertical decomposition in
> http://web.onetel.com/~hughdarwen/TheThirdManifesto/Missing-info-without-
> nulls.pdf)
>
> All this situation is telling us is that there is no C value in the
> database for this value of A. It does not matter how many possible
> reasons there are for the existence of this situation (the
> unsatisfactory answer I referred to above), we do not know why there is
> no value. Actually presenting lots of possible reasons was a very bad
> argument because only if there was exactly one reason would we know what
> was going on.
>
> There is no such thing as an acceptable implementation of NULL, because
> each occurrence of a potential NULL in a database design may have many
> possible reasons, each of which must be represented in the database
> somehow. There is no guarantee that there is a single form of
> representation for each of the reasons, and hence no general way of
> implementing a NULL.
>
> So in most cases, the meaning of NULL is "This database has no value
> here, the design must be incomplete"

There are often denomalization decisions in completed designs, so I don't think you can say the design is incomplete, but that the design is not fully normlized. Then you still need to understand it as a lack of data and deal with it that way, I would think. Just because something is not in BCNF, you do not simply decide the design is incomplete and have no way of understanding or dealing with it, right? --dawn Received on Sun Dec 11 2005 - 02:05:04 CET