A locations model

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
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

A locations model

Post 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.
Last edited by JellyFish on Fri Feb 13, 2009 1:11 am, edited 2 times in total.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: A locations model

Post by josh »

Conceptually, adjacency list or materialized path paradigms. Efficiency and power? Nested set hands down
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: A locations model

Post 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 :)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: A locations model

Post 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.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: A locations model

Post 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. :?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: A locations model

Post by John Cartwright »

I don't see why you can't implement that within my simple relational model.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: A locations model

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: A locations model

Post 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.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: A locations model

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: A locations model

Post 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.
Post Reply