title: Linking an address table to multiple other tables
body: <p>I have been asked to add a new address book table to our database (SQL Server 2012).</p>
  
  <p>To simplify the related part of the database, there are three tables each linked to each other in a one to many fashion: Company (has many) Products (has many) Projects and the idea is that one or many addresses will be able to exist at any one of these levels. The thinking is that in the front-end system, a user will be able to view and select specific addresses for the project they specify and more generic addresses relating to its parent product and company.
  The issue now if how best to model this in the database.</p>
  
  <p>I have thought of two possible ideas so far so wonder if anyone has had a similar type of relationship to model themselves and how they implemented it?</p>
  
  <p><strong>Idea one:</strong>
  The new address table will additionally contain three fields: companyID, productID and projectID. These fields will be related to the relevant tables and be nullable to represent company and product level addresses. e.g. companyID 2, productID 1, projectID NULL is a product level address.
  My issue with this is that I am storing the relationship information in the table so if a project is ever changed to be related to a different product, the data in this table will be incorrect. I could potentially NULL all but the level I am interested in but this will make getting parent addresses a little harder to get</p>
  
  <p><strong>Idea two:</strong>
  On the address table have a typeID and a genericID. genericID could contain the IDs from the Company, Product and Project tables with the typeID determining which table it came from. I am a little stuck how to set up the necessary constraints to do this though and wonder if this is going to get tricky to deal with in the future</p>
  
  <p>Many thanks,</p>