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: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Thu, 01 Aug 2002 04:38:20 -0800
Message-ID: <F001.004A8957.20020801043820@fatcity.com>


Bill,

you said :
"gather the option of parent_table/parent_pk doesn't work for you?"

if you mean, one column in the address table that holds the parent FK values, how would you assign the foreign key to it? the only way to do that would be programatically (thru the application) and not thru formal FK constraints. I like that idea less than multiple columns - one for each parent table.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Wednesday, July 31, 2002 5:10 PM
To: Multiple recipients of list ORACLE-L

thanks, tom . . .

your second option is one I had suggested below, which in may ways is preferable - easy to track FK relationships, cascade delte, enforce r/i, etc.

your first option won't work in our case - the parent tables are too dissimilar to enable us to combine them - more real life example is storing the address of a person and the address of an asset (e.g., property) . . . both addresses need to contain the same type of info, but they belong to two entirely different beasts.

I'm working on separating them out - preferable to have multiple address tables, I think, that each store a unique type of data - but in the meantime we've created a mishmash (how exactly do you spell that?)

gather the option of parent_table/parent_pk doesn't work for you?

thanks

-bill

-----Original Message-----

Sent: Wednesday, July 31, 2002 4:00 PM
To: 'ORACLE-L_at_fatcity.com'
Cc: 'Bill.Magaliff_at_lendware.com'

Bill,

simply combine the employee, supplier, contractor and vendor tables into one such table titled "people". Add a column to indicate what type of record it is (a code indicating one of the above). solves your problem.

if this is not possible, then the address table could have multiple columns, one for each of the tables above with a FK pointing to the master table. however, none of these columns can be the PK for the address tables - you will need to create a column (supported by a sequence) that will contain the PK. I would also then add a column in the address table indicating what type of record this address is for - employee, supplier, etc.

I would go with the first option if at all possible.

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Wednesday, July 31, 2002 4:43 PM
To: Multiple recipients of list 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: 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: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us
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 - 07:38:20 CDT

Original text of this message

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