Zip code lookup table - design Q

The US / Canada zip code lookup table has 1.1 million rows already. I am planning to add in all world countries zip codes. So I assume the table rows will grow a lot. All this data will be used on site for lookupts (to pull city/state/country info) + some auto suggest also.

So is it better to break the table down by country or leave this as a world zip code table and let it grow into a huge l开发者_StackOverflow社区ookup table?

Let it grow into one big table and optimize/tune the DB as appropriate.

The database constraints should not dictate design except in the very extreme cases.

Depends on what you plan to use it for.

I have a location database, and sometimes it is useful to get all the zip codes in new york for example, or just Canada.

Adding a field for state and country is probably a good idea, even if you don't use them.

Just make sure you have separate indexes for zip, country, and state.

One issue with using a single, monolithic table is that there are some postcodes that are used by different countries for different places (no, I don't know of an example off the top of my head).

Depending how your table is set up, that may or may not be a problem. Hopefully your table isn't PKd on the postcode, or you'll be in trouble.

You'll definitely need to add at least a country field, and probably also a state/province/prefecture/etc field too in order to disambiguate such occurrences.