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

David Portas schrieb:

>>Yes, I acknowledge that we can deal with data without nulls but such an
>>approach would be very restricted and inconvenient.

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?

The second question: Does this design solves our problems? Inded, the design with only one table is compact, clear and elegant. We hope that with 4 table we bring some structure and will be easier to work with our data. First, presumably, these 4 tables will guarantee that objects with right type will get right columns. However, somebody needs to determine the object type before it can be added to the databse. And here is the crucial point:

• determining object type (i.e., to what table it has to be added in 4-table design) is equivalent to determining if it may have nulls in the 1-table design.

In other words, we still have to check either object type or its possibility to take null values. In this sense 4-table design does not solve any problem.

The third question: Has the 4-table design less information? Obviously, no. It has 4 times more tables that are expensive resource. These tables have to be linked somehow because these objects are not isolated and an object from one table represet an entity from another table. In other words, each entity is distributed among many tables and we need to keep track of them via links. So we add additional columns. But why we then tried to remove null columns if then we add even more auxiliary columns?

You simply follow some dogma and do not want to think yourself. In many cases it is really desirable to try to avoid nulls. In other cases they can be tolerated. It is always a trade off. What I wanted to say is that

• nulls are a normal data modeling construct like link records and should not paranoically avoid them. We simply have to learn how to use it correctly.

> If you just mean "complex" or "difficult" then say so - I don't really
> have any more to say on relative complexity than I've already said.
> "Restricted" implies that the model without nulls has limitations but
> you still haven't explained what they are.
>

-- 
http://conceptoriented.com