0

The Problem

I am creating a database of Countries, Regions, Cities and Postal Codes. Here's an example to give an idea of the database structure as it is now:

enter image description here

Now, let's take Tuskegee University as an example to illustrate my problem. Here is Tuskegee University's address:

1200 W Montgomery Rd
Tuskegee, AL 36088

For the city database, I grabbed postal code data from Geonames, and I cross referenced this data with USPS. In both dumps, for '36088' I get Tuskegee Institute. So, when I do the following:

SELECT a1.name,a1.address,b1.name,b1.postal_code 
   FROM university a1 
   JOIN city b1 ON a1.city_id = b1.city_id
   WHERE a1.name = 'Tuskegee University';

I get back:

"Tuskegee University";"Kresge Center, 3rd Floor";"Tuskegee Institute";"36088"

Thus, making my address:

Kresge Center, 3rd Floor
Tuskegee Institute, AL 36088

The Question

There are many problems here (e.g., the address: I downloaded university data from IPEDS and obviously their address does not match Google Places). However, what I want to focus on is the issue with my city table. I feel like the schema here may not be the best approach to the problem, but I'm not really sure what the best approach might be. Do I create separate city and postal code tables? But, in the end, how do I associate 36088 with Tuskegee, AL? Is there a better source here that I have just not found?

A Possible Solution (with problems)

Say I switched up the DB schema, such that it looked like this:

enter image description here

Then, I could have the postal code and city be independent of each other. Thus, the university table points to Tuskegee for city_id, and the 36088 for postal code. However, I now have an issue. If someone types in a city, I cannot tell them cities within a certain radius. I could get the lat/long for cities and create a geog column in the city table as well, but that seems a bad solution. I could associate cities with postal codes (i.e., city_id column in postal_codes table), but then I am faced with the same problem of mapping 36088 to Tuskegee, or having 'Tuskegee Institute' in my city table (which I obviously don't want).

An Interesting Side Note

CityGrid seems to have completely ignored the problem I am facing, going with Tuskegee Inst for the city.

As well, a USPS lookup for this zip code tells me the following:

The preferred city in 36088 is...

TUSKEGEE INSTITUTE AL
Other acceptable cities for 36088 are

TUSKEGEE INST AL

However, the mailing address according to Tuskegee University's website says the following:

ATTN: (Recipient's name)
Tuskegee University (Dept. or Office name)
(Building name, Room #)
1200 W. Montgomery Rd.
Tuskegee, AL 36088

So, it seems that Tuskegee should also be an acceptable name for USPS, but it's not listed as such.

  • If you get a postal manual you will likely be surprised at all the exceptions and special cases. That is just how it is. For another example, ZIP Codes may span different communities and even states. – RLF Oct 25 '14 at 2:09
  • Is there a manageable way to deal with this type of information then? I mean, I'm having this much trouble just tackling the US, I can't imagine what it's like to deal with world data... – Franz Kafka Oct 25 '14 at 2:18
  • I did not mention more than just a sample. The "manageable way" is to accept that the only goal of this information is to get the mail to a location. Although it often maps fairly well to physical locations, that is not required. – RLF Oct 25 '14 at 2:38
  • Why are you trying to map postal codes to respective cities? What are you trying to accomplish? – Walter Mitty Oct 25 '14 at 10:11
  • Cities can have vanity names as you've discovered. Hollywood is a famous one, it's just Los Angeles I cover a bit of this over on SuperUser. The next challenge you might run into is these postal codes can change. The +4 delivery route more freq than the 5 but it happens. Also, a zip on the MO/IA border covers both. The source of the postal code data is the USPS. – billinkc Oct 28 '14 at 14:54
1

Your question is not very clear.

You say you are trying to create a database of cities and zip codes, but then you say you are using Geonames, which is a database of cities and zip codes. Why are you trying to re-create it?

Places are not hierarchical. You need to think about the institution responsible for the place's boundaries. State boundaries are determined by the US Constitution. City/county boundaries are determined by city and/or state governments. Zip boundaries are determined by USPS.

Some towns contain zips. Some zips contain towns. Some zips cross state boundaries.

A mailing address needs a zip AND some other info. Usually it is a city OR a "surveyed area" such as a street address (which in turn may belong to a city).

A university also likely has more than one address ;)

I would use a little table inheritance and give country, state/prov, city and surveyed area all the same supertype, that way you can point a single foreign key at any of them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.