Nullable columns or Separate table ?

From: P.A. <john.doe_at_nospam.org>
Date: 2000/04/27
Message-ID: <39085F7C.6625E57B@nospam.org>#1/1

Hi all,

Can someone give me a pure theoretical viewpoint on following ?

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. An alternate way is to define another table with same primary key and optional attribute(s) and the table will contain rows only when applicable.

E.g.

Table -> Employee
Attribute -> Manages_Dept is only applicable for managers

Approach (1)

Employee

---

Emp_ID 		not null primary key,
Emp_Name	not null

.
.
.

Manages_Dept

Approach (2)

Employees

---

Emp_ID		not null primary key,
Emp_name	not null

.
.
.

Managers

---

Emp_ID		not null primary key,
Manages_Dept	not null

In practice, the decision will be made based on the "cost" of each approach. In situation where there are very few Managers compared to large number of Employees, approach (2)o will be most cost effective. Of course applications will determine what constitutes the "cost". But, I was wondering, is there any database theory which says either of the above approach is always preferable ? Normalization works only at table level, so to me both are normalized.

BTW, is there a theory which works at "Schema" level that discourages more than one relation with same primary key ? It appears to me, if every attribute of an entity was put in a separate table with same primary key, it would still be normalized, since normalization looks at a table at a time.

TIA P. Adhia Received on Thu Apr 27 2000 - 00:00:00 CDT