Re: data modeling question - child table with multiple parents

On a system that you expect to have heavy inserts and/or deletes, prototyping this and testing it would be a good idea.

I haven't done that myself. If it ever caused me a problem. I was blissfully ignorant. :)

Jared

On Thursday 01 August 2002 11:18, Magaliff, Bill wrote:
> jared:
>
> any thoughts on the point at which this becomes a potential performance
> bottleneck? pretty simple if there are two potential fk's as in your
> example - but what about 4 or 5 fk id's?
>
> -biill
>
> -----Original Message-----
> Sent: Thursday, August 01, 2002 1:48 PM
> To: Multiple recipients of list ORACLE-L
>
> > btw, in your first solution, how are you going to implement mutual
> > exclusiveness of EMPLOYEE_ID and SUPLIER_ID? trigger? - not very
>
> elegant.
>
> Actually quite simple and elegant:
>
> alter table add constraint only_one
> ( check (
> ( employee_id is null and supplier_id is not null )
> or
> ( supplier_id is null and employee_id is not null )
> )
> )
>
> Jared
>
>
>
>
>
>
> "Igor Neyman" <ineyman_at_perceptron.com>
> Sent by: root_at_fatcity.com
> 07/31/2002 02:20 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
> Subject: Re: data modeling question - child table with
> multiple parents
>
>
> yet, another solution:
>
> add another table, called i.e. "ACTOR" (actor_id, actor_type);
> sub-entity tables "EMPLOYEE", "SUPLLIER", "CONTRACTOR" will store
> sub-entity
> specific information, and their PK (employee_id, supplier_id, ...) will be
> foreign keys to actor_id in "ACTOR" table;
> table "ADDRESS" will reference "ACTOR" table (not multiple sub-entities),
> and you can enforce this relationship in the database;
> thus, adding new sub_entity (like "VENDOR") will not require any changes
> in
> existing tables;
> also, you can implement "TELEPHONE" table the same way (referencing
> "ACTOR"
> table).
>
> btw, in your first solution, how are you going to implement mutual
> exclusiveness of EMPLOYEE_ID and SUPLIER_ID? trigger? - not very elegant.
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, July 31, 2002 4:43 PM
>
> > Good day, all:
> >
> > Am curious to hear opinions on how to model a child table that has
>
> multiple
>
> > parent tables (i.e., foreign key to multiple parents)
> >
> > Example:
> > There's a table that stores Addresses (table ADDRESS) for both employees
> > (table EMPLOYEE) and suppliers (table SUPPLIER).
> >
> > Each of these tables has a Primary Key field called ID.
> >
> > One way to set this up would be for the ADDRESS table to have 2 fields,
> > EMPLOYEE_ID and SUPPLIER_ID, which would be mutually exclusive (i.e.,
>
> one
> or
>
> > the other, to indicate the parent record of the address).
> >
> > Another solutions if for the ADDRESS table to have two fields to
>
> indicate
>
> > the parent table name and parent table pk value.
> >
> > The first method enables me (the dba) to create foreign keys from the
> > address table to each of the parent tables to validate data. The second
> > method does not enable me to create such foreign keys (leaving it to the
> > developers to validate date and insure referential integrity) but would
>
> also
>
> > easily facilitate the addition of other parent tables (e.g., CONTRACTOR,
> > VENDOR, etc.) without altering the ADDRESS table itself.
> >
> > Any and all thoughts, comments, opinions, experiences are most welcome.
> >
> > Thanks!
> > bill magaliff
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Magaliff, Bill
> > INET: Bill.Magaliff_at_lendware.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).