RE: data modeling question - child table with multiple parents

igor:

I agree with what you say here, as well as your previous comment about deleting a laid-off employee and then the extra step of finding the orphan addresses. Our developers have imbedded this inverse logic throughout the application. and now I'm left to try to figure out how to validate data and make sure the logic is accurately represented by the data model.

I think I will ultimately end up with multiple address tables to support distributed data, proper enforcement of parent-child relationships via foreign keys (as opposed to triggers - don't like using them for r/i issues) as well as the concept of not mixing different conceptual data elements within the same table. Works for this case.

-bill

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

seems to me like a case of "inverse" logic. is it "employee" (or "supplier", or whatever) entity, which has "address" attribute, or is it "address" entity, which has "employee" (or "supplier") as an attribute?
for me, it's the first: I'm not interested in any address, if it does not belong to "employee", or "supplier", or whoever... info stored in "address" table is just "common" set of attributes split from "employee", or "supplier" table.
and, if they'd stay in those tables, employee_id (or supplier_id) would be PK - not address_id.

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 5:04 PM

> Since the ADDRESS table is just a look-up table, why not let it have a
> primary key for each address and then let the EMPLOYEE and SUPPLIER tables
> reference it with a foreign key? That does not prevent the EMPLOYEE and
> SUPPLIER tables from having their own unique primary keys.
>
> -----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: Clark, Tommy R
> INET: TOMMY.R.CLARK_at_saic.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: 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).