Trying to map postal codes to their respective cities, but running into some issues

Question

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:

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:

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.

Answer 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.