> From: Igor Neyman [mailto:[EMAIL PROTECTED]]
>
> 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. :(
