Oracle FAQ Your Portal to the Oracle Knowledge Grid

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: Jacques Kilchoer <>
Date: Wed, 31 Jul 2002 14:08:38 -0800
Message-ID: <>

> From: Igor Neyman []
> here is an argument against this solution:
> in current economy, some day you'll have a lay-off, and you'd want to
> "delete" an employee (you will be laying off employees - not
> addresses,
> right?), then you'll have to take "additional" care not to
> leave "orphan"
> addresses, and all this headache, only because of the wrong
> design in the
> first place.

But if the software / users were sophisticated enough (and if the situation were possible in this case), you could share an address amongst several different employees/suppliers:
i.e. if an employee and a supplier had the same address, you would only need one row in the address table, with both employee_address_id and supplier_address_id pointing to the same record.

Past experience: in one of my previous companies we designed a similar layout for physician offices: often many physicians share the same office (and also the same physician can work at multiple offices.)

The layout was:
physician table (physician_id pk, name, etc.) office table (office_id pk and physical address) physican_office table (physican_id & office_id pk)

Then in theory you would enter the office once even though many physicians work in that office; all physicians in that office would have the same office_id.

In practice though we found it difficult to convince the users to search on address to prevent multiple entries in the office table. :(

Please see the official ORACLE-L FAQ:
Author: Jacques Kilchoer

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: (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 - 17:08:38 CDT

Original text of this message