title: Best database schema for country, region, county, town
body: <p>I have country, region, county, town data and I'm currently deciding between 2 schema designs (if there's a better one, do tell).</p>
  
  <p>I first thought</p>
  
  <ul>
  <li><p>Country</p>
  
  <ul>
  <li>Id</li>
  <li>Name</li>
  </ul></li>
  <li><p>Region</p>
  
  <ul>
  <li>Id</li>
  <li>CountryId</li>
  <li>Name</li>
  </ul></li>
  <li><p>County</p>
  
  <ul>
  <li>Id</li>
  <li>RegionId</li>
  <li>Name</li>
  </ul></li>
  <li><p>Town</p>
  
  <ul>
  <li>Id</li>
  <li>CountyId</li>
  <li>Name</li>
  </ul></li>
  </ul>
  
  <p>Does the job however to get all towns in a country you have to 3 inner joins to do the filtering.  I guess this could be ok but potentially expensive?</p>
  
  <p>The other design was:</p>
  
  <ul>
  <li><p>Country</p>
  
  <ul>
  <li>Id</li>
  <li>Name</li>
  </ul></li>
  <li><p>Region</p>
  
  <ul>
  <li>Id</li>
  <li>Name</li>
  </ul></li>
  <li><p>County</p>
  
  <ul>
  <li>Id</li>
  <li>Name</li>
  </ul></li>
  <li><p>Town</p>
  
  <ul>
  <li>Id</li>
  <li>CountryId</li>
  <li>RegionId</li>
  <li>CountyId</li>
  <li>Name</li>
  </ul></li>
  </ul>
  
  <p>This way all hierarchical data so to speak is at the bottom and you can go back up however if you want all regions in a country you're a bit screwed which makes we wonder whether the first design is best.</p>
  
  <p>What do you think is the best schema design?</p>