best database design for city zip & state tables
- by ryan a
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.
single table solution (cant do relationships)
[locations]
locationID
locationParent (FK for locationID - 0 for state entries)
locationName (city, state)
locationZIP
two tables (with relationships, FK constraints, ref integrity)
[state]
stateID
stateName
[city]
cityID
stateID (FK for state.stateID)
cityName
zipCode
three tables
[state]
stateID
stateName
[city]
cityID
stateID (FK for state.stateID)
cityName
[zip]
zipID
cityID (FK for city.cityID)
zipName
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.
Does anybody know of a good (not perfect) solution that takes this into consideration to minimize discrepencies as the database grows?
