Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010

    Question Unanswered: DB Design for ZipCodes Table?

    Hi everyone. I am looking for a solution to my database Zip Code problem and thought someone might be able to help me out. Seems easy enough, but I have to be missing something!

    I have an event system. Each event has a location (PK = LocationID). Each location has a single Zip Code. Now, we know that each Zip Code could have multiple cities (possibly even states). Here is where the issue comes in. Right now, I have a ZipCodes table that has a city, state, zip in there. This does not work (multiple zips for a city issue again), so I added a ZipCodeID field to the ZipCodes table. In theory that solves the issue and I can just store the ZipCodeID in the Locations table (instead of ZipCode) and key off that, but that would be MY field that I created. The next time I reload the data, the keys are all going to be off since the zip data can change from the provider with USPS updates.

    Is the ONLY way to do this by keying off of a double primary key of CityName and ZipCode? Seems a bit hokey to me and I must be missing something. Any help would be GREAT!

    Thanks in advance.


  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    It is actually worse than you think... There is (or recently was) at lease one case where one zip code is shared by two cities with the same name and different states.

    Zip codes are meant for postal delivery. They can be used as "rough indicators" of location and political boundaries, but they are AT BEST rough indicators. I'd be willing to bet that there is at least one of "special" zipcodes that will break every rule that you could conceive.

    I'd live with having a zip code that would map to a metro locale (a city in many cases) and allow for the possibility of more than one locale per zip code.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2010
    Hi Pat, thanks for the reply. I am fine with zip mapping to just a city (or multiple cities). I just need to develop my database such that it can handle the multiple zip codes and keep data integrity with subsequent reloads on the zip data. It seems that CityName and ZipCode as a dual PK is really the only way to do this. I also just noticed that this bombs out all of my function calls since there are subqueries returning more than one record now (I can't add a DISTINCT, it just takes too long to execute).


    Would love to know how others have similar situations architectured!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts