Nullable columns or Separate table ?
Date: 2000/04/27
Message-ID: <39085F7C.6625E57B_at_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
Approach (1)
Employee
Approach (2)
Employees
Managers
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.
Attribute -> Manages_Dept is only applicable for managers
Emp_ID not null primary key,
Emp_Name not null
.
.
.
Manages_Dept
Emp_ID not null primary key,
Emp_name not null
.
.
.
Emp_ID not null primary key,
Manages_Dept not null
TIA P. Adhia Received on Thu Apr 27 2000 - 00:00:00 CEST