title: Common one-to-many table for multiple entities
body: <p>Suppose I have two tables, Customer and Vendor.  I want to have a common address table for customer and vendor addresses.  Customers and Vendors can both have one to many addresses.</p>
  
  <h3>Option 1</h3>
  
  <p>Add columns for the AddressID to the <code>Customer</code> and <code>Vendor</code> tables.  This just doesn't seem like a clean solution to me.</p>
  
  <pre><code>Customer     Vendor         Address
  --------     ---------      ---------
  CustomerID   VendorID       AddressID
  AddressID1   AddressID1     Street
  AddressID2   AddressID2     City...
  </code></pre>
  
  <h3>Option 2</h3>
  
  <p>Move the foreign key to the <code>Address</code> table.  For a Customer, <code>Address.CustomerID</code> will be populated.  For a Vendor, <code>Address.VendorID</code> will be populated.  I don't like this either - I shouldn't need to modify the address table every time I want to use it for another entity.</p>
  
  <pre><code>Customer     Vendor         Address
  --------     ---------      ---------
  CustomerID   VendorID       AddressID
                              CustomerID
                              VendorID
  </code></pre>
  
  <h3>Option 3</h3>
  
  <p>I've also seen this - only 1 foreign key column on the Address table with another column to identify which foreign key table the address belongs to.  I don't like this one because it requires all the foreign key tables to have the same type of ID.  It also seems messy once you start coding against it.</p>
  
  <pre><code>Customer     Vendor         Address     
  --------     ---------      ---------
  CustomerID   VendorID       AddressID
                              FKTable
                              FKID
  </code></pre>
  
  <p>So, am I just too picky, or is there something I haven't thought of?</p>