13

Possible Duplicates:
Is there common street addresses database design for all addresses of the world?
What is the “best” way to store international addresses in a database?
Best practices for consistent and comprehensive address storage in a database

I currently have four tables, Customers, Contacts, Facilities and Clients.

Each of these tables has the following fields: AddressLine1, AddressLine2, City, StateOrProvince, PostalCode.

I would like to move the addresses out to a separate table and be able to also designate the type of address (billing, shipping, main, etc.).

My solution is as follows:

  1. Remove AddressLine1, AddressLine2, City, StateOrProvince, PostalCode from Customers, contacts, Facilities and Clients.
  2. Create Addresses table with fields AddressID(PK), AddressLine1, AddressLine2, City, StateOrProvince, PostalCode , LastUpdateUser, LastUpdateTime.
  3. Create AddressTypes table with fields AddressTypeID, AddressTypeName, AddressTypeDescription, AddressTypeActive, LastUpdateUser, LastUpdateTime
  4. Create CustomerAddresses table with fields CustomerID, AddressID, AddressTypeID, CustomerAddressActive, LastUpdateUser, LastUpdateTime
  5. Create ClientAddresses table with fields ClientID, AddressID, AddressTypeID, ClientAddressActive,LastUpdateUser, LastUpdateTime
  6. Create ContactAddresses table with fields ContactID, AddressID, AddressTypeID, ContactAddressActive, LastUpdateUser, LastUpdateTime
  7. Create FacilityAddresses table with fields FacilityID, AddressID, AddressTypeID, FacilityAddressActive, LastUpdateUser, LastUpdateTime

I am looking for guidance to determine if there is a better solution than the one I devised. Why does everyone think?

EDIT: I am not concerned with anything outside the US at this point and not concerned with how to store the street address, ie street number vs the whole street address. I am concerned from a database design and table structure stand point.

Community
  • 1
  • 1
Michael Wheeler
  • 2,459
  • 3
  • 19
  • 26
  • 1
    See: http://stackoverflow.com/questions/24481/ http://stackoverflow.com/questions/126207/ http://stackoverflow.com/questions/929684/ http://stackoverflow.com/questions/310540/ etc – Welbog Sep 18 '09 at 16:02
  • What is wrong with having more than one address table? – NoChance Jun 17 '17 at 00:39

4 Answers4

13

A DBA where I used to work told me this gem, and it's worked great for us (the first two steps are the same as in your solution):

  1. Remove AddressLine1, AddressLine2, City, StateOrProvince, PostalCode from Customers, Contacts, Facilities and Clients.
  2. Create AddressTypes table with fields AddressTypeID, AddressTypeName, AddressTypeDescription, AddressTypeActive, LastUpdateUser, LastUpdateTime
  3. Create Addresses table with fields AddressID(PK), AddressTypeID(FK), AddressLine1, AddressLine2, City, StateOrProvince, PostalCode , LastUpdateUser, LastUpdateTime, CustomerID(FK), ClientID(FK), ContactID(FK), FacilityID(FK)
  4. On the addresses table, set up a constraint so that only one of the CustomerID, ClientID, ContactID, or FacilityID foreign keys may be non-NULL at a time.

This way you've got all your addresses in one table, they can reference any record you need, your referential integrity is intact, and you don't have in intermediate table that you have to traverse.

The downside is that if you want to add addresses to a new class of object (e.g. an Employee table), you have to add a EmployeeID column to the Addresses table, but that's pretty easy.

bernie
  • 9,820
  • 5
  • 62
  • 92
Edward Robertson
  • 399
  • 1
  • 6
  • What is the point of adding so many primary keys where the reverse can make the life easier and allow one to may relations where customers (just a sample) can have more than one address. – Cem Kalyoncu Sep 18 '09 at 17:10
  • 2
    The Customer *can* have more than one address. The Addresses table can have multiple rows that reference the same CustomerID. You just can't have the same address reference both a Customer and a Contact. – Edward Robertson Sep 18 '09 at 17:40
  • 2
    what if an address reference two different customer because they living on the same house? – nonsensecreativity Apr 21 '17 at 10:38
  • @nonsensecreativity, you can have two addresses with the same AddressLine1, City, StateOProvince, PostalCode – danilo Apr 23 '18 at 13:47
2

One additional thing that we have in our database that you might want to consider is to havea correspondence flag onthe address table with a trigger to enforce that only one address per person can bet as correspondence. We send out a lot of mail to people in our database and knowing which of the three addresses for that person is the one we need to use when sending mail is invaluable. It also makes its easier when querying to grab only one address per person to avoid getting multiple records per person for some reports.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

I also want to add one more thing, for the sake of simplicity, create views that expands address info to your tables or you may hate yourself for designing db this way.

Cem Kalyoncu
  • 14,120
  • 4
  • 40
  • 62
0

I'd consider a single AddressLink (name?) table with 

LinkTypeID (Customer,Client,Contact,Facility) -- needs additional TypeID table
ID (CustomerID,ClientID...)
AddressID
AddressTypeID
AddressActive
LastUpdateUser
LastUpdateTime

Adding a new address link type means adding a new LinkTypeID w/o a new [TypeID]Addresses table, no queries need to be modified, and if you're looking for all uses of an Address (for deletes etc) there's only one place to look.

This is pretty similar to how we do it, anyway.

Oh, and we have an AddressLine3 in our Addresses (equivalent) table for some odd outlier situations.

DaveE
  • 3,579
  • 28
  • 31
  • 1
    How do you enforce a foreign key constraint on ID with this structure? Or do you not bother? – APC Sep 18 '09 at 16:52
  • Insert/modify triggers validate that the ID column is valid relative to the LinkTypeID. FKs on the LinkTypeID, AddressID, and AddressTypeID columns, and "delete restrict" in place on the parent tables. I *think* that's done declaratively but could be triggers also. (MS SQL Server 2005) – DaveE Sep 18 '09 at 17:19