Oracle-L: RE: data modeling question - child table with multiple parents

From: Magaliff, Bill <Bill.Magaliff_at_lendware.com>
Date: Thu, 01 Aug 2002 10:18:38 -0800
Message-ID: <F001.004A9226.20020801101838@fatcity.com>

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: Igor Neyman
  INET: ineyman_at_perceptron.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: 
  INET: Jared.Still_at_radisys.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: 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).

Received on Thu Aug 01 2002 - 13:18:38 CDT