title: Database: Foreign keys or denormalized tables for immutable data?
body: <p>Given:</p>
  
  <pre><code>Person[id, name, city, state, country]
  </code></pre>
  
  <p>When am I better off storing city names directly in the main table versus using foreign keys to a separate <code>cities</code> table? For the purpose of this discussion, assume that city names are immutable. Here is what I was thinking:</p>
  
  <p><strong>Option 1</strong>: Store values inline.</p>
  
  <pre><code>Person[id, name, city, state, country]
  </code></pre>
  
  <ul>
  <li>Pro: Easy inserts. Fast queries.</li>
  <li>Cons: Increased diskspace/memory usage</li>
  <li>Ideal for small values, infrequent duplicates.</li>
  </ul>
  
  <p><strong>Option 2</strong>: Foreign keys to a separate table</p>
  
  <pre><code>Person[id, name, city_id, state_id, country_id]
  Cities[id, name]
  States[id, name]
  Countries[id, name]
  </code></pre>
  
  <ul>
  <li>Pro: Decreased diskspace/memory usage. Potentially easier to cache frequently-used values.</li>
  <li>Cons: Inserts are more complicated (does a city already exist or should it be inserted?)</li>
  <li>Ideal for large values, frequent duplicates.</li>
  </ul>
  
  <p>To quote <a href="http://en.wikipedia.org/wiki/Database_normalization" rel="nofollow">http://en.wikipedia.org/wiki/Database_normalization</a>: "The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships." It seems to me that these benefits fly out the window when dealing with immutable data since it never needs to be updated. As for insertion and deletion anomalies, we can use nullable columns (if needed).</p>
  
  <p>What is the best-practice for this case?</p>