Re: Nullable columns or Separate table ?

>> Sometime an attribute in an entity is not always meaningful, so we
 define it as Nullable and store NULL value when it is not applicable. <<

That attribute better be meaningful, but not known.

>> Table -> Employee

   Attribute -> Manages_Dept is only applicable for managers <<

Those arrows mean "determines" in relational algebra, so table and attribute are really poor choices for names. What if you use reall DDL instead of your personal pseudo-code?

Approach (1);

 CREATE TABLE Employees
 (emp_id INTEGER NOT NULL PRIMARY KEY,
  emp_name CHAR(30) NOT NULL,
  manges_dept INTEGER -- null means is not a manager

              REFERENCES Departments(dept_nbr));

 Approach (2)

 CREATE TABLE Employees
 (emp_id INTEGER NOT NULL PRIMARY KEY,
  emp_name CHAR(30) NOT NULL);

CREATE TABLE Managers
(emp_id INTEGER NOT NULL PRIMARY KEY

        REFERENCES Employees (emp_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
 manges_dept INTEGER NOT NULL
             REFERENCES Departments(dept_nbr)
             ON DELETE CASCADE
             ON UPDATE CASCADE,

How about Approach (3)?

 CREATE TABLE Employees
 (emp_id INTEGER NOT NULL PRIMARY KEY,
  emp_name CHAR(30) NOT NULL);

 CREATE TABLE Departments

 (dept_nbr INTEGER NOT NULL PRIMARY KEY,
  dept_name CHAR(30) NOT NULL,
  dept_manager INTEGER -- null means no manager
             REFERENCES Employees(emp_id)
             ON DELETE SET NULL
             ON UPDATE CASCADE);

See? It is more complex than just a simple choice. I prefer (3) myself, as it seems more natural.

--CELKO--
Joe Celko, SQL and Database Consultantr

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 28 2000 - 00:00:00 CEST