best database design for city zip & state tables_问答_开发者

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 a开发者_如何学编程nd 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?

There is actually some database(with a single table) that the USPS puts out every year with ZIP codes and state and counties and state/county codes. I would look into it. I have an (outdated) copy of it. The schema is pretty simple:


ZIPCODE nvarchar(5) not null
CITY nvarchar(50) null
STATE nvarchar(2) null
STATECODE nvarchar(50) null
COUNTY nvarchar(50) null
COUNTYCODE nvarchar(50) null

(see below)

edit: Also, I would allow your users to add a new zipcode(with city and county and such) because zipcodes are being added all the time..

http://www.usps.com/ncsc/addressinfo/addressinfomenu.htm

edit: Actually, I guess I'm wrong. I don't have an official copy of their database.. I downloaded one of their sample files and their schema seems quite complex.

Thanks for all the replies. I wanted to give a review & my solution incase someone was interested. The question was "How should I store/retrieve ZIPs, cities and states?"

Jon Seigel gave me a fairly reassuring answer about using: Country Region (state/province) City with one to many relationships.

My reasons were redundancy and misspelling. Allowing any free-input of cities and states columns stored within the address records opens up a slew of issues with queries. Not having relational integrity could allow incorrect cities to states. I just wanted to store locations in a uniform way for users to be able to lookup.

For anybody interested my solution is this:

[state]; stateID; stateName

[location]; locationID; stateID (FK); cityName; zipID

The [location.stateID] is a foreign key relation with a one-to-many to [state.stateID]. I decided to keep the ZIP with the location table as unique ZIPs are not directly relational to a unique city. Also it seems ZIPs are not a basis for city/state boundary determination, rather they are for USPS purposes and actually indicate a carrier route and postal delivery zone which can span cities or even states. Another location record can be added with the same city name and the additional ZIP. This way ZIP searches can result in all cities & city searches can result in all zips if need be.

I don't know if you're internationalizing your app, but the general construct is this, with a one-to-many relationship with the following item:

Country
Region (state/province)
City

That's usually sufficient to be able to filter your data in a meaningful way. Trust me on this: you don't want to get into the technicalities of geographic land division.

For an address, store the data above plus street address, postal code (international version of zip code), etc. down to the resolution you need. I say resolution because you could split the address field into things like apartment number, street number, street name, street direction, etc. -- but that data may be dependent on the location, so I would avoid doing that if you are going to internationalize your app. Just a street address field is sufficient 99.99% of the time.

It depends on whether data integrity, normalization is more important, or performance.

However, for most applications, what you really want is a household. So, this information should be stored separately from your customer, so you can represent multiple customers living at the same household.

The household must have the street address, apt, city, state/province, country, zip/postal code.

I would not bother to normalize this by including only a reference to the city (which would be a key since there could be multiple cities with the same name), but you should store a separate table with cities, countries, postal codes just for validation and integrity purposes.

I would put these foreign keys as fields in the household.

I've worked with marketing databases, and developed an AI system for constructing customer keys and household keys for householding purposes for a bank, and this was one of the primary issues. For analysis purposes, we needed to aggregate accounts to the customer level, and customers to the household level. So your representation should support this for future analytical purposes.

There's not a single right model for this need - there are dozens. To know which is best for you depends on some additional info, like:

performance & capacity - what's driving concern about redundancy?
functionality - what kind of data analysis will be performed?
historical data - do you have to maintain old data? note that zip codes change, and this invalidates some of the offered solutions
internationalization
language
do you have other kinds of locations? You may want a more abstract solution that can consolidate physical with electronic locations - if for example your user wants to pick preferred contact method, etc.
do you want to allow locations to be shared?
any other physical location info also kept or highly likely to be added? Like county, country, lat & long, etc?