A Design Question

Suppose one has a table, People and a table Work_Categories.  A person =
can be responsible for many work categories, and a work category can =
have several persons responsible for it.  Thus the many:many relation =
ship between People and Work_Categories which is resolved by creating a =
junction table holding the People_PK and the Work_Categories_PK which =
are foreign keys referencing the primary keys of their respective parent =
tables. =20

What if a work category must have at least one and no more than one =
primary manager, but can have many secondary manager? A person can be =
the primary manager for  a number of categories.  Now the cardinality of =
the relationship for a primary supervisor that is between =
Work_Categories and People is no longer many:many,  but many:1.  The =
WORK_Category table needs a FK which references the Person_PK to =
designate a primary manager.  However secondary supervisors still have =
the many:many relationship described above and hence the junction table =
is still required

This makes it easier to enforce the constraint that a work category must =
have one and only one supervisor.  It becomes more complex to insure =
that a person is not both a primary and a secondary supervisor for the =
same category.  The PERSON_PK/FK FIELD Of the junction table must =
reference the People to ensure such a value exists, but that field must =
also be compared with the
PERSON_FK field of the Work_Category field to make sure it doesn't =
exist.  But a person can   be "primary" for one work category and =
secondary for another. There are no declarative "anti"-constraints, if =
that's the proper term, thus  this would have to be coded.

The problem is that one tends to view the managers and a single entity =
despite some being secondary and some being primary.  Users want to see =
all the managers listed together including those who have privileges to =
change the primary/secondary  designations.
-------------------------------------------------------------------------=
----------------
Another place where this shows up is recording multiple email addresses =
for an individual.  Again with one marked primary and others secondary.  =
Here there is a 1:1 relation between a person and the primary email =
address.  But a person can have several secondary email addresses.  A =
person does not necessarily have an email address, but if they have only =
one, that has to be primary.  Again users like to see all email =
addresses for a person presented together.

I was wondering how others were facing this problem.  Another solution =
would be to not enforce the rules at data entry time, but to have =
exception reports.  Despite the difference in cardinality of the =
relationships involving primary and secondary.  They are treated the =
same way in the database.


Ian MacGregor
Stanford Linear Accelerator Center
ian@xxxxxxxxxxxxxxxxx


=20
--
http://www.freelists.org/webpage/oracle-l

Other related posts: