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_at_newsread3.news.pas.earthlink.net>
>
> 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.
Date: Thu, 24 Nov 2005 04:33:56 GMT
Message-ID: <Uobhf.219$YT3.167_at_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 Thu Nov 24 2005 - 05:33:56 CET