Page 1 of 1

A locations model

Posted: Thu Feb 12, 2009 9:06 pm
by JellyFish
What would be the best way to represent a hierarchal model of locations in a MySQL table? Such as something like:
  • United States
    • California
      • San Diego
      • San Francisco
      • Los Angeles
      • ...
    • New York
      • New York
      • ...
    • Texas
    • ...
  • China
    • Hong Kong
    • Bejing (Or however you spell that)
    • ...
  • England
    • London
    • Manchester
    • ...
  • etc...
I'll need a table that could contain this kind of data, but will also have the following attributes:
  1. Fast access to deeply nested child elements. For example, retrieving all the cities within a particular state or the US.
  2. 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.
  3. 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.
  4. [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)
These are just some of the things that come to mind that are really necessary. So some of the ideas I have for a design for this kind of table have some flaws it seems.

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';
This model allows me to meet all four of my requirements, but doesn't feel like it should. So I thought more relational with this kind of table:

| 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));
This query will return a result set of all the locations under "United States/California". I know I could just do:

Code: Select all

SELECT `name` FROM `locations` WHERE `parent` = 1003;
But then, how would I know what the parent ID is suppose to be for California?

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.

Re: A locations model

Posted: Thu Feb 12, 2009 10:12 pm
by josh
Conceptually, adjacency list or materialized path paradigms. Efficiency and power? Nested set hands down

Re: A locations model

Posted: Thu Feb 12, 2009 10:29 pm
by John Cartwright
Assuming you have 3 tables, locations, countries, cities

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));
Can relationally be expressed as

Code: Select all

SELECT * 
FROM locations AS l
INNER JOIN cities AS ci ON l.city_id = c.id
INNER JOIN countries AS co ON ci.country_id = co.id
WHERE co.name = 'United States'
Doesn't seem cumbersome at the least :)

Re: A locations model

Posted: Thu Feb 12, 2009 10:54 pm
by josh
You unless you're writing a CMS model them as tables, if its geographic regions, that makes the most sense. I overlooked the fact this was geographic locations. In the event you need the hierarchy to be more flexible is when you'd pull out the hierarchical stuff. This is just basic relations / joins.

Re: A locations model

Posted: Fri Feb 13, 2009 12:18 am
by JellyFish
Thanks John, but the problem with your solution is, I guess, it'll be less flexible. That is, I'll be restricted to 3 levels of a locations tree, unless I'm not understanding you fully.

I failed to mention that I'll be needing the ability to specify as many levels as needed. This way I could have three levels for the US (country, state, city) and only two levels for other locations (china for example). Also, if I wanted I could put regions within cities or whatever I want.

And as for Josh's posts, I'm not quite sure what he's trying to say. :?

Re: A locations model

Posted: Fri Feb 13, 2009 8:47 am
by John Cartwright
I don't see why you can't implement that within my simple relational model.

Re: A locations model

Posted: Fri Feb 13, 2009 3:58 pm
by JellyFish
John Cartwright wrote:I don't see why you can't implement that within my simple relational model.
I don't see how I could. Maybe I'm misunderstanding you.

Are you suggesting three tables, one for counties, one for cities, and one that binds them all together called locations?

countries
| ID | name |
| 1111 | United States |
...

cities
| ID | name |
| 2323 | San Diego |
...

locations
| ID | country_id | city_id |
| 555 | 1111 | 2323 |
...

Did I get this right? How could these have more dimensions then three?

Re: A locations model

Posted: Fri Feb 13, 2009 4:56 pm
by josh
You don't need the redundant country_id since if you need to move a city to a different country now you have 2 places to update it which introduces the possibility to corrupt your own database.

Re: A locations model

Posted: Fri Feb 13, 2009 5:42 pm
by JellyFish
Well I'm not so excited about the idea of separating my locations table into three related tables. Why is it better to do this?

Re: A locations model

Posted: Fri Feb 13, 2009 7:13 pm
by josh
Because they are different entities with different information. Since the way we structure geography isn't going to change why would you need anything else? itd be overkill.