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

From: Alexandr Savinov <>
Date: Thu, 24 Nov 2005 10:21:24 +0100
Message-ID: <43858627$>

Jonathan Leffler schrieb:

> Alexandr Savinov wrote:

>> Jon Heggland schrieb:
>>> In article <438456fe$>, 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).

Maybe it is because the idea of EAV is much younger than the postulate of avoiding nulls.

Another reason is that EAV is mainly aimed at custom dimensionality modeling rather than the problem of nulls. In other words, we use the patterns of EAV if we are not satisfiied with the standards mechanism the database operates dimensions (properties). In EAV we can define our own custom mechanism for property management (and hence in great extent our own database system). The EAV design can be met in almost any more or less complex database system.

> References:
> 1.
>     (The Final NULL in the Coffin - available at a fee).
> 2.
>     (How to Handle Missing Information Without Using Nulls).

Received on Thu Nov 24 2005 - 10:21:24 CET

Original text of this message