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
