title: Why is using a common-lookup table to restrict the status of entity wrong?
body: <p>According to <a href="http://www.simple-talk.com/sql/database-administration/five-simple--database-design-errors-you-should-avoid/" rel="noreferrer">Five Simple Database Design Errors You Should Avoid</a> by Anith Sen, using a <strong>common-lookup table</strong> to store the possible statuses for an entity is a common mistake.</p>
  
  <p><strong>Edit + Answer:</strong> The figures in Anith's article aren't well labelled - I thought both <strong>Figure 1</strong> and <strong>Figure 2</strong> are examples of <em>bad design</em>, whereas Figure 2 is <em>good design</em>. <em>Phew</em>, got worried there for a moment.</p>
  
  <h2>In summary:</h2>
  
  <ul>
  <li>Lookup tables: <strong>good</strong>.</li>
  <li>Common-lookup tables: <strong>bad</strong>.</li>
  </ul>
  
  <p>I'll keep my question below for reference.</p>
  
  <hr>
  
  <h2>The following reasons are given:</h2>
  
  <blockquote>
    <ol>
    <li><p>"You lose the means to ensure accurate
    data; constraints. By combining
    different entities into a single
    table, you have no declarative means
    to restrain values of a certain
    category."<br>
    <em>How is constraining values losing accuracy?</em></p></li>
    <li><p>"You are forced to represent
    every data type as a string with this
    type of  generic lookup table."<br>
    <em>If I want to represent another data type, I can add a column for it to my lookup table.</em></p></li>
    <li><p>"You commit yourself to
    rigidity and subsequent complexity."<br>
    <em>How?</em> </p></li>
    <li><p>Fourthly and finally, you are faced
    with the physical implementation
    issues.<br>
    <em>I don't see why.</em></p></li>
    </ol>
  </blockquote>
  
  <p>I disagree with most of the reasons given and would like some objective critique on my incorrect? logic.</p>
  
  <h2>My examples:</h2>
  
  <p>Citing the example of jobs at a repair service with many possible statuses that generally have a natural flow, let's take a <code>JobStatus</code> table:</p>
  
  <ol>
  <li>Booked In</li>
  <li>Assigned to Technician</li>
  <li>Diagnosing problem</li>
  <li>Waiting for Client Confirmation</li>
  <li>Repaired &amp; Ready for Pickup</li>
  <li>Repaired &amp; Couriered</li>
  <li>Irreparable &amp; Ready for Pickup</li>
  <li>Quote Rejected</li>
  </ol>
  
  <p>Arguably, some of these statuses can be normalised to tables like <code>Couriered Items</code>, <code>Completed Jobs</code> and <code>Quotes</code> (with Pending/Accepted/Rejected statuses), but that feels like unnecessary schema complication.</p>
  
  <p>Another common example would be an <code>OrderStatus</code> table to restrict the status of an order:</p>
  
  <ol>
  <li>Pending</li>
  <li>Completed</li>
  <li>Shipped</li>
  <li>Cancelled</li>
  <li>Refunded</li>
  </ol>
  
  <p>The status titles and descriptions are in one place for editing and are easy to scaffold as a drop-down with a foreign key for dynamic data applications. This has worked well for me in the past. If the business rules dictate the creation of a new order status, I can just add it to <code>OrderStatus</code> table, without rebuilding my code.</p>
  
  <p>Why is this a bad practice?</p>
  
  <hr>
  
  <p><strong>Edits:</strong> I added Anith's reason to my question and tried to remain objective.</p>
  
  <p>--</p>