City / State / Country

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

City / State / Country

Post by psurrena »

I have a database with multiple projects in different locations. Is the best method for storing the locations to have:
- three tables, one with all the cities one with all the states and one with the countries
- in the cities table have a field for state_id
- in the state table have a field for country_id
?

Thanks!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

The two character state and country codes are going to be smaller than an integer key, I would just use them in a CHAR(2) field. Besides not requiring a lookup, they are meaningful when you view the data.
(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: City / State / Country

Post by califdon »

psurrena wrote:I have a database with multiple projects in different locations. Is the best method for storing the locations to have:
- three tables, one with all the cities one with all the states and one with the countries
- in the cities table have a field for state_id
- in the state table have a field for country_id
?
Are you talking about a comprehensive list of cities, states and countries, which users will be able to choose from? What I have seen done is have a single table with 3 fields, country, state/province, city. The user is asked to choose a country, which launches a query that selects states that are valid in that country, and when the user chooses a state, it launches a query that selects cities that are valid in that state/province. Sometimes that is done by showing a world map, allowing the user to click on a country or region, which then changes the map, etc. I don't think I'd want more than one table, because it would allow a user to choose something like Los Angeles, Minnesota, India.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

A lot will depend on what other information you are storing with the city/state/country tables. If they are all the same I would make them one table. If the information is different for each then it makes sense to separate them. For a single table you may want to look at this link Storing Hierarchical Data in a Database It contains two methods of storing tree level structures. The Adjacency List Model and Modified Preorder Tree Traversal. The second one may be useful to you.

Another alternative to the ones already suggested (and one I would probably go with for maximum flexibility) is to have a single table using the Modified Preorder Tree Traversal method containing the fields (tree_id, abbreviation, longname, left, right) an 'attribute' table (att_id, name) and an attribute2tree table (tree_id, att_id, content). This would provide the most flexibility as it would allow you to place additional attributes into any of your tables if required at a later date. Example new requirement for 'population'. No modification to the table structure would be required, only adding the attribute and linking in the values.
Post Reply