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
