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!
City / State / Country
Moderator: General Moderators
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: City / State / Country
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.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
?
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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.
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.