How to best represent addresses in a database [duplicate]

Question

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.

Answer 1

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.