Trees and Hierarchical Data in mySQL using the nested sets model

A more frequently used way of dealing with hierarchical data and trees in mySQL is by adding a ‘parent’ column which references the primary key in the same table. This model is, however limited by the number of self joins you have when you read from this table.

There is a solution which is quite elegant and does not imply a variable number of self joins – called the nested set model.

If this made you curious, I recommend reading this article http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/. The examples in the article are based on Joe Celko’s ‘Trees and Hierarchies in SQL for Smarties’ which is very valuable resource in any developer’s book collection.

At the time of writing this post, I could find the following shopping links for it:

http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/1558609202

 http://books.google.de/books/about/Joe_Celko_s_Trees_and_Hierarchies_in_SQL.html?id=uw2lq2o4VbUC&redir_esc=y

Posted Tuesday, August 21st, 2012 under mysql, nested set.

Comments are closed.