RE: data modeling question - child table with multiple parents

> From: Igor Neyman [mailto:ineyman_at_perceptron.com]
>
> 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: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.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).