title: Representing a lookup table in SQL Server and C#
body: <p>We are sending updates on the status of an avatar to an external service via their API.</p>
  
  <p>The Avatar:</p>
  
  <pre><code>ID  
  Name
  Age
  Nationality
  Status
  </code></pre>
  
  <p>There are 4 statuses we can send:</p>
  
  <pre><code>(A) Alive
  (D) Dead
  (C) Injured
  (U) Unknown
  </code></pre>
  
  <p>The characters in brackets are how the statuses are stored in the external system - and we must send them ID &amp; Status in that format.</p>
  
  <p>We need to store these in SQL Server, so we'd have the primary key of the status table in the avatar table.</p>
  
  <p>We also need to represent the status in C# code. It's unlikely the status will ever change - so an enum or dictionary would (I think) be an appropriate way to represent the statuses to make them clear in C# code.</p>
  
  <p>We can add an int primary key to the status table, then have a single letter representation (as sent to the external system), and the full name.</p>
  
  <p>Or we could set the primary key to a char(1) as the letter - and store that in the Status column of the Avatar table.</p>
  
  <p>The former could be an enum - the latter a dictionary in C#.</p>
  
  <p>Are there any normalisation problems with either approach. Are there any issues with either approach in object oriented terms. </p>
  
  <p>What is the best practice in both cases?</p>