Date: 12/15/06 (MySQL Communtiy)    Keywords: software

My lead and I've spent the last month bouncing idea's against each other on how to store the entire structure of the United States (states->counties->cities & zips) as well as Canada, Mexico, England, Germany, and Japan.

So far the idea is 2 tables: locations & location_rules.
the location tables would be like so
id, name, abbr, parent, rule_id

so for the USA, a quick example would be.
1, United States of America, U.S.A., 0, 1
2, Colorado, CO, 1, 2
3, Denver County, Denver, 2, 3
4, Denver, DEN, 3, 4


and location_rules is still up in the air, but something like this
id, head, type
1, 0, Country
2, 0, State
3, 0, County
4, 0, City

Right of the bat the problem is dealing with US ZIP codes which often are 1 to 1 with cities, but some cities have multiple ZIP codes and a few counties share zip codes.

The second problem is the current schema used for the USA sucks, but it's been an effective guide to figure out what were dealing with. USA alone has 3360~ counties, 76,000~ cities, and I don't want to think about ZIP codes but it's more or greater then cities in count. So that's a table with 140K~ entries. Doing dirty math, I figured Canada is about 2/3 the size of the USA for something like 238K records total. My experience so far is that life gets interesting when you get near the quarter of a million records edge and this is definately going to go well past that point.

So yeah, that's what I got... anyone know a better way besides making sets of tables for each country, thereby increasing the complexity of the software that uses it.

Source: http://community.livejournal.com/mysql/107817.html