A Design Question

From: MacGregor, Ian A. <ian_at_slac.stanford.edu>
Date: Fri, 25 Feb 2005 21:56:03 -0800
Message-ID: <7F24308CD176594B8F14969D10C02C6C3B7E2E@exch-mail2.win.slac.stanford.edu>

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_at_SLAC.Stanford.edu

=20

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

Received on Sat Feb 26 2005 - 00:59:25 CST