Re: Separate tables?

>> I have several tables all of which model concepts that require one
or more addresses i.e. Customer, Supplier etc. If I create a single address table I have to have a foreign key column for every adressee, but if instead I have a separate address table for CustomersAddresses, SupplierAddresses, etc.

The usual choice is to put in all the address columns in each table that uses them, and duplicate those columns when the table has multiple addresses (i.e. the Invoices table has both a ship-to and bill-to address.

Since (I assume) all addresses are from the same domain, then putting them into table like CustomersAddresses, SupplierAddresses, etc. would be wrong. But if you had US addresses and foreign addresses, you could have two tables because they are different kinds of things -- different postage rates and rules, different formats, etc.

The single address table model is easy to maintain and cuts down the size of the other tables. However, you might even want to consider having other tables that relate zip codes to cities and states. And you can look up the SAN (Stanadrd Address Number) codes used in the book industry and find out if your industry has something like this.

--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jul 04 2000 - 00:00:00 CEST