Re: [GENERAL] when to use NULL and when to NOT NULL DEFAULT ''

[Michael Glaesemann]

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings