Re: So what's null then if it's not nothing?

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Thu, 24 Nov 2005 04:33:56 GMT
Message-ID: <Uobhf.219$YT3.167@newsread3.news.pas.earthlink.net>

Alexandr Savinov wrote:
> Jon Heggland schrieb:

>> In article <438456fe$1_at_news.fhg.de>, spam_at_conceptoriented.com says...
>>>> You mean you would create a table for each *combination* of 
>>>> attributes? What on earth for?
>>> In order to avoid nulls (there is an opinion that they need to be 
>>> removed from schema).
>> I get that part. But why one for each combination? Why not just one 
>> per attribute?

>
> Yes, there are several alternative designs:
>
> 1. For each combination of attributes. In this case each entity is
> guaranteed to be isolated (not distributed among many tables).
>
> 2. One table for one attribute (as you propose). In this case one such
> table will store object ids for the objects (from the main table) that
> take this attribute (non-null) as well as its value. If the value of
> this attribute is absent then this table will not have a record for this
> object (physical deletion as opposed to NULL in a slot).
>
> 3. A kind of EAV desingn. Here we store object-attribute-value triples.
> If an object does not have a value for an attribute then the
> corrosponding triple does not exist physically.
>
> Probably there could be also other designs that allow us to avoid nulls.
> The above cases could be considered standard patterns while in general
> case we would mix them and apply in a generalized form.

Nice summary.

My understanding is that Fabian Pascal espouses approach 1, Hugh Darwen espouses approach 2 (more or less), but I've not come across any theorist who espouses approach 3 (though that probably means I've simply not been reading in the correct places).

References:
1. http://www.dbdebunk.com/page/page/1396241.htm

     (The Final NULL in the Coffin - available at a fee). 2. http://www.thethirdmanifesto.com/

     (How to Handle Missing Information Without Using Nulls).

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Received on Wed Nov 23 2005 - 22:33:56 CST