title: Database Design - Foreign Key and Primary Key relationships across 3 tables
body: <p>As a personal project to myself, I am trying to redesign one of our existing Access database tools at work into VB.net. This is including a database redesign from scratch as the current one is an absolute mess.</p>
  
  <p>Here is the database as it stands at the moment with my current re-design in SQL Server:</p>
  
  <p><img src="https://i.stack.imgur.com/1LXZT.png" alt="enter image description here"></p>
  
  <p>Now to make the relationships clear:</p>
  
  <p>On the <code>Client</code> table, <code>Client_ID</code> is the primary key. This has a relationship to <code>Contracts.Client_ID</code> as its foreign key. This also has a relationship to <code>Sites.Client_ID</code> as its foreign key.</p>
  
  <p>On the <code>Sites</code> table, <code>Site_ID</code> is the primary key. This has a relationship to <code>Contracts.Site_ID</code> as its foreign key.</p>
  
  <p>Each primary key in every table auto-increments by one on each record creation.</p>
  
  <p>The idea here is a simple Client/Site/Contract structure. For example, Client: Microsoft, Site: Reading Head Office, Contracts (any type of contract that could apply to either the company as whole or an individual site).</p>
  
  <p>You can't have a site without a client. You should be able to link a contract to a client or a site. At present I have allowed Nulls for both <code>Site_ID</code> and <code>Client_ID</code> in contracts to facilitate this as I can't find any way to ensure at least one is filled in.</p>
  
  <p>Does this design look reasonable and follow best practice? I've tried to follow best practice as per a number of different suggestions found across the web, namingly separating tables for different types of data. Any input would be gratefully received</p>