Re: Nullable columns or Separate table ?

A theoretical viewpoint arguing for your "Approach 2" is given in the following article:

Jeffrey Parsons and Yair Wand, "Emancipating Instances from the Tyranny of Classes in Information Modeling," forthcoming in ACM Transactions on Database Systems (tentatively scheduled for June 2000). A preliminary version of the paper can be downloaded (by ACM members) at http://www.acm.org/tods/Upcoming.html

"P.A." <john.doe_at_nospam.org> wrote:
>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 CEST