What is the best way to set this data in the database?

I have a table of cites and zip codes. As it happens one city can have multiple zip codes so how should i set the db that i may be able to pull the correct zips when i enter a city name or get the correct city when i enter the zip.

Should this be one table with all the data, or three where one has cities, one has zips and third has t开发者_StackOverflowhe proper associations?

I would use only one table with zip codes as primary key and an index on the cities (to speed up searching). The three-table solution may me useful if you have a many-to-many relationship (one zip code can have multiple cities), but otherwise I think it's only unnecessary complexity.

Having lived in Kissimmee and worked for UPS (memorizing zip codes), the cities of Kissimee and Celebration in Florida have the same zip code 34747. I personally use multiple tables: countries, regions (states), and localities (cities). The countries table has country name and other relevant data. The regions table is basically the same but has a foreign key to the country name. The localities table has foreign key to the region name, plus the postal code and other relavant data. I create a primary key referencing multiple columns in the localities table (name, region, postal_code), regions table (name, country), and countries table (name) to help with performance. My localities table can therefore handle (Kissimmee, FL, 34747) and (Celebration, FL, 34747) as well as (Orlando, FL, 32801) and (Orlando, FL, 32802). In any case, I handle returning multiple results if I search by zip code or city. However, depending on how your source data is a acquired, you may want to make a Boolean "metropolis" column in your localties table to indicate (in my example) that Kissimmee is the metrolopitan area for Celebration and only allow one metropolis per zip code.