RE: A Design Question

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Sat, 26 Feb 2005 10:02:09 +0100
Message-Id: <20050226090212.AA97EB3BC58@ha-smtp0.tiscali.nl>

Hi Ian,
I think you need an intersection entity (as you describe) between PEOPLE and WORK_CATEGORIES, where the combination of the two foreign keys are part of the primary key; the third component of the primary key is the TYPE column. that's the starting point. you are right that the remaining constraints cannot be expressed declaratively, at least not in Oracle SQL.

What I would do is create a function-based index to enforce "conditional unicity", along the lines "if the responsibility TYPE is P then the combination of the two foreign keys must be unique:

SQL> create index blah on RESPONSIBILITIES 2 ( case TYPE when 'P' then P_FK else null end 3 , case TYPE when 'P' then W_FK else null end);

kind regards,

Lex.

Visit my website at http://www.naturaljoin.nl

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MacGregor, Ian A.
Sent: Saturday, February 26, 2005 06:56
To: ODTUG-DEV2K-L_at_fatcity.com; oracle-l_at_freelists.org Subject: 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_at_SLAC.Stanford.edu

=20
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Sat Feb 26 2005 - 04:05:17 CST