Database Design Theory?

[Dan Hanks]

On Tue, 8 Nov 2005, Dan Hanks wrote:

> On Tue, 8 Nov 2005, Jonathan Ellis wrote:
>
>> On Tue, 8 Nov 2005 16:38:13 -0700 (MST), "Dan Hanks" <hanksdc at plug.org>
>> said:
>>> - Avoid nulls as much as possible

I'll clarify even further:

In my own databases I strive for null-free as much as possible. About the 
only time I'll ever consider a null is in fields like 'comments', 
'description', i.e., fields that are never likely to be joined on, or used 
in query search criteria. Rather than using nulls in most cases, I'll break
the field out into its own table, with a foreign key pointing back to the 
original table. This follows the good design principle (that I forgot in 
my original response) of making sure that you only model 1 entity-type per 
table (Fabian Pascal's book "Pracitcal Issues in Database Management" 
elaborates on this idea very nicely). If you have nulls in a table, 
chances are you're trying to model more than one entity-type in a single 
table.

For example, let's imagine we have a table which enumerates people in an 
enterprise. Some people (US citizens) have an SSN. Other people in the 
database (non-US citizens) usually don't. Rather than have a nullable ssn 
field:

person
------
id
ssn (nullable)
first_name
etc.


I'd have a people table:

person
------
id int
first_name varchar
etc...

And an auxillary table:

person_ssn
----------
person_id int references person(id)
ssn varchar (or whatever datatype you decide works best)

This makes things a bit more complicated from a programming point of view, 
but I trust my query results more by doing so (I never have to second 
guess myself on whether I've remembered to treat the NULLs as I 
should--which treatment varies from vendor to vendor, depending on what 
kind of query or operation the nullable field in question is being used 
in).

Fwiw,

-- Dan