Re: Nullable columns or Separate table ?

Joe Celko wrote:
> That attribute better be meaningful, but not known.
>

Why is that a requirement ? It must be the diffenrence in our English, I meant "Not Applicable" when I wrote "not meaningful". Do you mean the same, when you say meaningful but unknown ? In my example, it is perfectly known if an employee manages a department or not - "Manages_Dept" is just not applicable for employees who are not 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?

Point well taken Mr. Celko. That's the price I paid for using point-and-click (I almost forgot DDL) :)

> 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);
I agree with your approach(3), but I meant my example as an illustration and not a real life database problem. So now, if you had a choice between (1) and (2) which one will you choose ? Alternatively I can break up your Departments table into two tables as

  CREATE TABLE Departments
  (dept_nbr INTEGER NOT NULL PRIMARY KEY,    dept_name CHAR(30) NOT NULL);

  CREATE TABLE Managed_Departments
  (dept_nbr INTEGER NOT NULL PRIMARY KEY,

              REFERENCES Departments(dept_nbr),    dept_manager NOT NULL

              REFERENCES Employees(emp_id)
              ON DELETE SET NULL
              ON UPDATE CASCADE);

This one doesn't have any nulls - will you still go with your original design, not taking performance of either design into consideration ?

Thanks

P. Adhia

P.S. Also, since you are a DB guru, can I interest you to comment on my reply to Jeffrey ? Received on Fri Apr 28 2000 - 00:00:00 CEST