- United States
- California
- San Diego
- San Francisco
- Los Angeles
- ...
- New York
- New York
- ...
- Texas
- ...
- California
- China
- Hong Kong
- Bejing (Or however you spell that)
- ...
- England
- London
- Manchester
- ...
- etc...
- Fast access to deeply nested child elements. For example, retrieving all the cities within a particular state or the US.
- The ability for other tables to relate to the locations table so that the data contained in that other table has a location. In other words data can refer to any record within the locations table; California, China, California > San Diego, etc.
- Queries to the locations table will only need a locations string like "United States/New York" or "United States>New York" (whatever the separator might be) to access child location records.
- [EDIT]Also the locations table should not have a limited number of child nodes for each node. (E.g. USA/Texas/Dallas/some street/some coke can/some kind of bug/this/could/go/on/virtually/forever)
My first idea goes something like this:
| locationID | name |
| 1003 | United States/California |
| 1004 | United States/California/Los Angeles |
With this I could have other tables refer to either the `locationID` or `name` field ('cause both are suppose to be unique). And if I'd like to select all the child elements of "United States" I'd just need to write a query like:
Code: Select all
SELECT `name` FROM `locations` WHERE `name` REGEXP '^United States';| locationID | name | parent |
| 1002 | United States | NULL |
| 1003 | California | 1002 |
| 1004 | Los Angeles | 1003 |
With this each record has a name (which doesn't have to be unique) and a parent it should fall under. This model feels more like it should, but makes things more cumbersome. Now in order to meet my third requirement I need to write a long query with nested subqueries like this:
Code: Select all
SELECT `name` FROM `locations` WHERE `parent` = (SELECT `locationID` FROM `locations` WHERE `name` = 'California' AND `parent` = (SELECT `locationID` FROM `locations` WHERE `name` = 'United States' AND `parent` = NULL));Code: Select all
SELECT `name` FROM `locations` WHERE `parent` = 1003;So out of these two models for a locations table do you suggest I use, or would you suggest another model?
PS: Sorry for the long post.