title: Is this a good way to model address information in a relational database?
body: <p>I'm wondering if this is a good design.  I have a number of tables that require address information (e.g. street, post code/zip, country, fax, email).  Sometimes the same address will be repeated multiple times.  For example, an address may be stored against a supplier, and then on each purchase order sent to them.  The supplier may then change their address and any subsequent purchase orders should have the new address.  It's more complicated than this, but that's an example requirement.</p>
  
  <p>Option 1
  Put all the address columns as attributes on the various tables.  Copy the details down from the supplier to the PO as it is created.  Potentially store multiple copies of the </p>
  
  <p>Option 2
  Create a separate address table.  Have a foreign key from the supplier and purchase order tables to the address table.  Only allow insert and delete on the address table as updates could change more than you intend.  Then I would have some scheduled task that deletes any rows from the address table that are no longer referenced by anything so unused rows were not left about.  Perhaps also have a unique constraint on all the non-pk columns in the address table to stop duplicates as well.</p>
  
  <p>I'm leaning towards option 2.  Is there a better way?</p>
  
  <p>EDIT: I must keep the address on the purchase order as it was when sent.  Also, it's a bit more complicated that I suggested as there may be a delivery address and a billing address (there's also a bunch of other tables that have address information).</p>
  
  <p>After a while, I will delete old purchase orders en-masse based on their date.  It is after this that I was intending on garbage collecting any address records that are not referenced anymore by anything (otherwise it feels like I'm creating a leak).</p>