Re: 4 the FAQ: Are Commercial DBMS Truly Relational?

"Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:irs8kc.9nv.ln_at_mercury.downsfam.net...

> Hmmm, have never needed NULLS myself, and the customers have never
> complained. I believe they can always be engineered out. Let the flames
> begin :)
>

I use NULLS freely myself, RDM be damned. Sometimes it's a fairly trivial feature of the data, like people with no middle initial. In that case, I'm either going to plug in a dummy value, like blank, or let SQL make it a NULL. And in that case, I really don't care which it does.

The more interesting case is foreign keys in optional relationships. Optional relationships abound. Who is the spouse of a single person? (Aha, grasshopper! Do you see the mountain? ;-) ). When an optional relationship is many-to-many, you have two foreign keys in a table, and there's no need for nulls. You just omit a row.

But when an optional relationship is many-to-one, you can put one foreign key in with the other primary key and end up with one less table that way. Now you need NULLS, to express the cases where the optional relationship is not present.

You are right. NULLS can always be engineered out. The way you do it is by "decomposing an outer join", resulting in one more table. Keep doing that, and eventually the NULLS all go away. Another way of saying the same thing is to say that in a fully normalized database, there is no need for NULLS.

But that's not the way I design. Call me an unrepentant sinner, but I use NULLS. Received on Sat Oct 09 2004 - 17:47:37 CEST