Re: when to use NULL and when to NOT NULL DEFAULT ''

On Oct 8, 2004, at 8:12 PM, David Garamond wrote:
> Speaking of NULLs, what does the relational model ideal suggest for
> missing information?
>
> a) no NULL at all;
> b) NULL and N/A;

I've read both of those as well. Date has a pretty good section
regarding NULLs in his Introduction to Database Systems. The upshot is
you shouldn't use NULL. Either your domain (data type) should include
values to indicate N/A (and all other values, as needed), or make an
additional relation referencing the first, giving values for the keys
you *do* know. For example;

CREATE TABLE employees (
    emp_id serial not null unique
    , emp_name text not null
    , birthdate date
);

For employees you don't have birthdates for, you could use NULL in SQL.
However, as relationally one shouldn't use NULL, you would do the
following:

CREATE TABLE employees (
    emp_id SERIAL NOT NULL UNIQUE
    , emp_name TEXT NOT NULL
);

CREATE TABLE employees_birthdates (
    emp_id INTEGER NOT NULL REFERENCES employees (emp_id)
    , birthdate DATE NOT NULL
);

In any case, one would never use NULL. Either the domain includes a
value for all possible values (including N/A) or you set up the db
schema appropriately.

Cheers,

Michael Glaesemann
grzm myrealbox com