title: Versioning Lookup Tables
body: <p>I'm creating some enterprise master lookup tables in SQL Server 2008r2.  I'm required to keep a table specific to a domain.  For example Gender would be a table and City could be another table.  In each table I keep Start and End dates to keep history. </p>
  
  <p>Now I'm being asked to version these so you would know something like V1 had these codes and V2 had these updated codes.  Thinking about one lookup table is pretty straight forward.  Using Gender for example you could have a Gender table, a GenderVersion table and a GenderToGenderVersion linking table.  This would require 2 extra tables per lookup.  With just a few tables that is manageable but I’m hoping to get an idea for a better way to do this with many lookup tables</p>
  
  <p>A few requirements.</p>
  
  <ul>
  <li>As stated, lookup tables must be domain specific. I cannot combine
  them.  </li>
  <li>Junior level programmers must be able to build a select
  statement to find all the look values based on a specific version.</li>
  </ul>
  
  <p>Any ideas?  I hate the idea of having 3x the required tables.  </p>
  
  <p><strong>[Edit]
  Additional Details</strong></p>
  
  <p>We need to be able to determine exactly what codes were in a particular version.
  We need codes that are not edited or removed from a previous version to be part of the next version.
  We do maintain start and end dates in the codes.  The version labels are in addition to the start and end dates.</p>
  
  <p>Here is a sample model using Race.  This is with the   </p>
  
  <p><strong>RaceVersion table</strong></p>
  
  <ul>
  <li>RaceVersionID</li>
  <li>VersionLabel</li>
  <li>StartDate</li>
  <li>EndDate</li>
  </ul>
  
  <p><strong>RaceToRaceVersion</strong></p>
  
  <ul>
  <li>RaceID</li>
  <li>RaceVersionID</li>
  </ul>
  
  <p><strong>Race table</strong></p>
  
  <ul>
  <li>RaceID (used just for the master code DB) </li>
  <li>RaceCode (used for all other systems that use these codes)</li>
  <li>RaceDescription</li>
  <li>StartDate</li>
  <li>EndDate</li>
  </ul>
  
  <p>Both the code and the version get date searchable effective dates. 
  You can find an version and then relate to the code</p>
  
  <p>Whenever there is a change to the codes, in this case Race, you can add a new version then find all the rows that are currently effective and populate the linking table.</p>
  
  <p>This works well for a few sets of codes.  I'm hoping for functionality like this that scales better.</p>