Re: Foreign keys

> Matt Browne wrote:

> Basically, we have a fairly complex database, with many tables
> (customers, etc) that need to reference addresses that are contained in
> a generic address table.

> So:
> customer_addresses [table]
> supplier_addresses [table]
> address [table]

I've stumbled late onto this thread so I may have missed something
important. However, I need to ask: are you keeping the same address in two
different tables? That is, are customer_addresses records duplicated in
address, and the same for supplier_addresses?

If so, you've violated a normalization rule in your schema and it's no
wonder that you can't delete all the addresses you want.

How do you synchronize addresses in multiple tables and, much more
importantly, why do you have multiple records?

In every database I've designed, the address is with the name record. If a
customer, supplier or whatever had multiple addresses, then I'd put _all_
addresses in a single table and reference each one to the name record in the
appropriate table.

What have I missed here?

Rich

Dr. Richard B. Shepard, President

Applied Ecosystem Services, Inc. (TM)
2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
http://www.appl-ecosys.com/