A Design Question
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: <ODTUG-DEV2K-L@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Feb 2005 21:56:03 -0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
