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

From: Magaliff, Bill <Bill.Magaliff_at_lendware.com>
Date: Wed, 31 Jul 2002 13:21:00 -0800
Message-ID: <F001.004A8111.20020731132100@fatcity.com>

yes it does help - thanks
only potential issue with my second option is that the different parent tables may be distributed and not located in the same node - in that case using a straight FK is out, right? (both parent and child would have to be on the same node?) but what about a trigger in a distributed db? have to check more into that . . .

thx
bill

-----Original Message-----
Sent: Wednesday, July 31, 2002 4:04 PM
To: ORACLE-L_at_fatcity.com
Cc: Bill.Magaliff_at_lendware.com

Bill,

There are other approaches as well.

A table for each type of address. This allows you to have foreign keys. You can use use a view to pull all the tables together, and just include the discriminating column in the view.

It's an option, but not one I personally care for.

Another is similar to your second one, but you could partition the table on the discriminating column, provided it's a large table.

With your second approach, you can use a trigger to enforce referential integrity.

This method is quite a bit more flexible than the first one as well. If you use
a lookup table to enforce the values that go into the TABLENAME column, the
enforcement becomes data driven.

Don't leave data integrity to developers, most of them place too much trust
in their users. :)

In your first method ( using multiple Id columns ), you can use a check constraint to
ensure that only one of the columns is populated at one time. This becomes
unwieldy with more than 3 columns, in which case a trigger is easier to manage.

HTH Jared

"Magaliff, Bill" <Bill.Magaliff_at_lendware.com> Sent by: root_at_fatcity.com
07/31/2002 01:43 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        data modeling question - child table with multiple

parents

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
--------------------------------------------------------------------