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:Comments Off