title: what is the best way to design a city, state, country table?
body: <p>I need help designing my country, city, state tables. I will provide sample data from my table so that you can help me better on my problem.</p>
  
  <p>This is my country table:</p>
  
  <pre><code>Country
  ______
  code   name
  US     United States
  SG     Singapore
  GB     United Kingdom
  </code></pre>
  
  <p>This is my city table:</p>
  
  <pre><code>City
  _____
  id   country   city        state
  1    US        Birmingham  Alabama
  2    US        Auburn      Alabama
  .
  .
  29   GB        Cambridge   NULL
  30   GB        Devon       NULL
  </code></pre>
  
  <p>My problem is that the only country that has the state field is the US. All other cities have a null value.</p>
  
  <p>My temporary solution for this is to just create a special city table for the United States, then all other countries have another city table that doesn't have the state field.</p>
  
  <p>I think this will just complicate the matter, because I have two tables for cities.</p>
  
  <p>How can I improve this design?</p>