Re: Nullable columns or Separate table ?

>> 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 no managers. <<

Okay, "Not Applicable" is one kind of missing value. SPARC detailed 16 kinds of missing data values in a paper. For example, my number of pregnancies is "not meaningful" before of my sex; the number of my wife's pregenancies is "Not Applicable" when people are asking about her grade point average. Imagine that I go to a hospital. My temperature is "Unknown" -- nobody took my temperature at admission, but they assume I have one (I hope!). The nurse puts the thermometer next to alight bulb to read it and gets 178 degrees -- "Erronous data". There is also underflowe and overflow, etc.

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

Does your mother know you point-and-click?!! <g> Seriously, that is one reason several of us are pushing people to post DDL; you really do not kow what you have done with point-and-click and neither the poor bastard who gets to maintain it.

>> 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 INTEGER 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 ? <<

Slight goof in your split -- the DRI is setting a NULL in a NOT NULL column in the Managed_Departments table.

I would l have to fall back on the data model; does a department exists without a manager? Yeah, probably so. Does a manager exists withoutn a department to manage; possible, I suppose, but not likely. I would go with the NULL and assume that we are waiting for an employee to fill the slot in that department.

But in general, you want to avoid nulls and when you have them make suire that they mean one and only one thing in the context of the tables.

>> P.S. Also, since you are a DB guru, can I interest you to comment on
my reply to Jeffrey ? <<

Let me look at it ...

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

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