title: best database design for city zip & state tables
body: <p>My application will need to reference addresses. Street info will be stored with my main objects but the rest needs to be stored seperately to reduce redundancy. How should I store/retrieve ZIPs, cities and states? Here are some of my ideas.</p>
  
  <p>single table solution (cant do relationships)</p>
  
  <p>[locations]
  locationID
  locationParent (FK for locationID - 0 for state entries)
  locationName (city, state)
  locationZIP</p>
  
  <hr>
  
  <p>two tables (with relationships, FK constraints, ref integrity)</p>
  
  <p>[state]
  stateID
  stateName</p>
  
  <p>[city]
  cityID
  stateID (FK for state.stateID)
  cityName
  zipCode</p>
  
  <hr>
  
  <p>three tables</p>
  
  <p>[state]
  stateID
  stateName</p>
  
  <p>[city]
  cityID
  stateID (FK for state.stateID)
  cityName</p>
  
  <p>[zip]
  zipID
  cityID (FK for city.cityID)
  zipName</p>
  
  <hr>
  
  <p>Then I read into ZIP codes amd how they are assigned. They aren't specifically related to cities. Some cities have more than one ZIP (ok will still work) but some ZIPs are in more than one city (oh snap) and some other ZIPs (very few) are in more than one state! Also some ZIPs are not even in the same state as the address they belong to at all. Seems ZIPs are made for carrier route identification and some remote places are best served by post offices in neighboring cities or states.</p>
  
  <p>Does anybody know of a good (not perfect) solution that takes this into consideration to minimize discrepencies as the database grows?</p>