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