RE: A Design Question

  • From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
  • To: <ian@xxxxxxxxxxxxxxxxx>, <ODTUG-DEV2K-L@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 26 Feb 2005 10:09:28 +0100

 
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.
 
---------------------------------------------
Visit my website at http://www.naturaljoin.nl
---------------------------------------------

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Lex de Haan
Sent: Saturday, February 26, 2005 10:02
To: ian@xxxxxxxxxxxxxxxxx; ODTUG-DEV2K-L@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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.
 
---------------------------------------------
Visit my website at http://www.naturaljoin.nl
---------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of MacGregor, Ian A.
Sent: Saturday, February 26, 2005 06:56
To: ODTUG-DEV2K-L@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx


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



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



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

Other related posts: