How to best represent addresses in a database

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.