Modeling variable depth inheritance in MySQL

Question

I am working on a project where I need to have data inherit at variable depths, and then be able to return any descendants of a particular piece of data. I am working with geographical regions, so I was thinking about having a "Region" table, and have everything from "North America" to individual neighborhoods such as "Bronx". So, if someone types in "North America", they should also receive results for "Bronx" because "Bronx" is a descendant of "North America" by means of the relationship North America->United States->New York(State)->New York(City)->Bronx

In my DB I was planning on having the Region table link back to itself with a table that specifies a parent/child relationship between Regions. Here is an example of what I would propose: enter image description here

Any help would be greatly appreciated!

I like the two-tables approach. But you don't need the `In_Region.ID` column. The Primary Key can be set as `(ParentRegionID, ChildRegionID)`. — ypercubeᵀᴹ, Jan 25 '12 at 21:18
Your model is called Adjacency List. There are also other models for storing hierarchical data. See this question, too: [Which Hierarchical model should I use? Adjacency, Nested, or Enumerated?](http://stackoverflow.com/questions/4831154/which-hierarchical-model-should-i-use-adjacency-nested-or-enumerated) — ypercubeᵀᴹ, Jan 25 '12 at 21:22
And this article: [Managing hierarchical data in MySQL](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) — ypercubeᵀᴹ, Jan 25 '12 at 21:23

dani herrera · Accepted Answer · 2012-01-25T20:38:16.567

Do you dont need a new table. A foreign key in major table is enough.

This is my approach:

First problem, design data schema: I keep hierarchis with a foreign key to parent row. It is simply. You have an example with hiererchy regions here:

WOE_ID  ISO Name    Language    PlaceType   Parent_ID
20069843    "NA"    "Oshana"    ENG State   23424987
55921113    "NA"    "Ondangwa"  ENG County  20069843
...

Second problem, retrieve ascendants/descendants: As you explain, problems comes with select: select some region and all descendants os ascendants. To solve this you should to create a new tree table. This table contains pairs: al combination to a person with all they ancestors (and itself):

region( id, name, id_parent)
region_tree( id, id_ancestor, distance )

Noticie that with this structure is easy to query hierarchies. Sample: all descendants of some region:

select region.*, distance
from 
  region p
    inner join 
  region_tree t 
    on ( p.id = t.id)
where
  id_ancesor = **someregion.id **

You can play with distance to get only sub-regions, sub-sub-regions, ...

Last problem, keep tree: tree must be all time up to data. You should automatize this: a trigger over region or a store procedure for CRUD operations,

Modeling variable depth inheritance in MySQL

1 Answers1