| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: A Design Question
sorry -- to avoid confusion, I realize I didn't solve the complete problem;
I only gave *one* example of a function-based index to solve *one* of the
problems.
I think that using this type of indexes on the intersection entity gives you the
most
chances to solve your problem without writing procedural triggers -- which is
something we all know we should avoid to implement DIY integrity, right?
see AskTom for some strong arguments :-)
kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On
Behalf Of Lex de Haan
Sent: Saturday, February 26, 2005 10:02
To: ian_at_slac.stanford.edu; ODTUG-DEV2K-L_at_fatcity.com; oracle-l_at_freelists.org
Subject: RE: A Design Question
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.
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.
-------------------------------------------------------------------------=
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
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Feb 26 2005 - 04:12:35 CST
 |
 |