Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: data modeling question - child table with multiple parents

Re: data modeling question - child table with multiple parents

From: Thomas Day <tday6_at_csc.com>
Date: Wed, 31 Jul 2002 13:29:05 -0800
Message-ID: <F001.004A8193.20020731132905@fatcity.com>

Your unstated business rule is that no employee can have the same address as a supplier.

My first thought is that the ADDRESS table has a primary key, generated via a sequence. EMPLOYEE and SUPPLIER each has a foreign key to that primary key. Do you really care whether an ADDRESS is the address of an employee or a supplier? Won't you be selecting addresses based on a pre-selected supplier or employee? All you are concerned with is that each address is unique.

If that's not true and non-unique addresses are possible and you do care whether a given address is for an employee or a supplier and you will be selecting employees and suppliers based on their addresses, then your model is correct.

                                                                                                                   
                    "Magaliff,                                                                                     
                    Bill"                To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    <Bill.Magalif        cc:                                                                       
                    f                    Subject:     data modeling question - child table with multiple parents   
                    @lendware.com                                                                                  

>
Sent by: root 07/31/2002 04:43 PM Please respond to ORACLE-L

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: Thomas Day
  INET: tday6_at_csc.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 Wed Jul 31 2002 - 16:29:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US