Re: NULLs
Date: Sun, 30 Dec 2007 17:46:59 -0500
Message-ID: <EdVdj.3431$lo5.623_at_newssvr19.news.prodigy.net>
"JOG" <jog_at_cs.nott.ac.uk> wrote in message
news:c9ec9790-f950-4407-b1f8-e5b8f4d07e91_at_v4g2000hsf.googlegroups.com...
> On Dec 30, 7:23 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
>> "stevedtrm" <steved..._at_hotmail.com> wrote in message
>>
>> news:a3716365-7bc4-4a75-ab58-ee7ac957c299_at_n20g2000hsh.googlegroups.com...
>>
>> >> > If everyone is clear NULLS shouldn't be used, why the debate as to
>> >> > what
>> >> > to do about them ?
>>
>> >> Because SQL allows NULL and even promotes the idea that NULL solves
>> >> some
>> >> problem instead of introducing many.
>>
>> > So everyone is agreed that NULLs shouldn't appear anywhere, and its
>> > just a matter of time before NULLS become a legacy problem and a
>> > relational language supercedes SQL?
>>
>> No, Steve, not everyone is agreed.
>>
>> > Are the two solutions I suggested before the widely accepted as
>> > resolutions to the two problems NULLs were introduced to eradicate?
>>
>> >>Missing information? Then surely there should just be no tuple?
>>
>> A row may contain a lot more information that would otherwise be useful.
>> Would you tell a potential customer that you won't serve them because you
>> don't know everything about them? I think not! Haven't you been asked
>> for
>> your e-mail address at a retailer? What would you think if the cashier
>> told
>> you to get lost for refusing to supply it?
>>
>> >>To indicate that there can be no value? Why not a seperate table with
>> >>a boolean value in the non-key column?
>>
>> In my opinion, null should only ever be used to indicate "There should be
>> a
>> value here, but it hasn't been supplied." That there should be a value
>> or
>> that there must be a value are structural issues, and therefore should
>> not
>> be subject to interpretation. "No value here" invites interpretation,
>> because it begs the question, "Should there have even been a value?"
>>
>> Structural information is lost by separating a nullable column into its
>> own
>> table: the fact that there should always be a value, even if it hasn't
>> been
>> supplied is lost.
> > I think this could be put better. It is not that there should always > be a value, but rather that "a value exists in the real world". > However I don't really see what is structurally lost by complete > decomposition to eliminate nulls, that can't be remedied via > constraints. >
If you complete the interpretation such that all missing values are supplied, then the resulting database should be a set of relations. When there is a nullable column on a table, there will a 1:1 correspondence between values in the completed relation. The problem with separating a column that can contain nulls into another table is that when all missing values are supplied, there is no requirement that for every tuple in the referenced relation there must be one and only one tuple in the referencing relation. Adding a constraint to guarantee that 1:1 correspondence would be the same as not permitting the information to be missing in the first place. Thus the only ways that I can see to remedy that would be either to add a boolean column, or to add another table.
>> That's not to say that it isn't possible to eliminate
>> nulls altoghether, it's just that you need several separate tables
>> instead
>> of one: one table for the values that must always be present, one table
>> for
>> each nullable column (or set of columns in those cases where whenever one
>> value is supplied, another value must also be supplied) that indicates
>> whether or not there should be a value (or set of values), and one table
>> with a column (or set of columns) for those values that just happen to
>> have
>> been supplied.
>>
>> Note also that the spectre of 3VL disappears when null indicates only
>> "there
>> should be a value here, but it hasn't been supplied." Since there should
>> be
>> a value, and only one value, a null can simply be replaced by an
>> exclusive
>> disjunction that encompasses all of the values in the attribute's domain.
>> So there is no need for 3VL, 2VL suffices. (For infinite domains, a
>> quantifier could be used that means instead of "there is...," "there is
>> exactly one....")
>
Received on Sun Dec 30 2007 - 23:46:59 CET