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

On Mon, 21 Nov 2005 15:23:30 +0100, Alexandr Savinov wrote:

>David Portas schrieb:
>>>Yes, I acknowledge that we can deal with data without nulls but such an
>>>approach would be very restricted and inconvenient.
>>
>>
>> How "restricted"? You haven't provided an example of information that
>> cannot be modelled without nulls (because no such information exists).
>
>I really like your argument: it does not exist because it cannot exist
>(never).
>
>Actually I provided such an example. Here it is again in simplified
>form. Assume that your problem domain consists of objects with two
>attributes Colour and Weight. However, for any object it can be absent.
>If we try to avoid nulls then we get a kind of 4 table design: 1 table
>for colourless weighless objects, one for objects with both colour and
>weight, and two tables for objects with one attribute absent.
>
>The first question: Is this design (4 tables) right price for having no
>nulls? Do nulls deserve such an urgly design?
(snip)

Hi Alexandr,

There is an alternative way. One that needs "only" three tables.

1. Objects - one column, the primary key for objects.
2. ObjectColors - two columns: one is both primary key and foreign key into Objects; the other holds a color
3. ObjectWeights - similar to ObjectColors.

An object with no color and no weight gets a row in the Objects table only. Add a weight, and you get a row in ObjectWeights. Add a color, and a row is added to ObjectColors as well.

This would enable us to model the data without the need for nulls and without exponential growth of the number of tables as the number of optional data elements grows.

Of course, you'll have the NULL probel right back as soon as you start joining the tables for your reporting procedures...

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)