# Nested sets: another data model for record hierarchies **URL:** \ **Category:** Bobino's\ **Tags:** performance, data-model\ **Created:** [March 14, 2021, 9:14pm UTC](https://the.fmsoup.org/t/nested-sets-another-data-model-for-record-hierarchies/1897 "2021-03-14T21:14:41Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Bobino](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/bobino/32/194_2.png) [@Bobino](https://the.fmsoup.org/u/Bobino)\ **Post date:** [March 14, 2021, 9:14pm UTC](https://the.fmsoup.org/t/nested-sets-another-data-model-for-record-hierarchies/1897/1 "2021-03-14T21:14:41Z")
Continuing the discussion from [Performance core principles: 13. Miscellaneous](https://the.fmsoup.org/t/performance-core-principles-13-miscellaneous/1874/14): > [@Performance core principles: 13. Miscellaneous](https://the.fmsoup.org/t/performance-core-principles-13-miscellaneous/1874/14): > > Unfortunately, I haven't found a tree structure yet that was fast enough when opening or closing nodes when dealing with more than - let's say - a few hundred records. I guess that was because you were relying on some data that could not be indexed. It is one of the drawbacks of the adjecency list model (the traditional approach to hierarchies). Nested set relies on indexed data to query the structure, so you can expect to have great performance gains right there. Are your users going to be browsing the structure via a portal or a list view? Do you expect them to explode many nodes from different branches at the same time or a single one at a time (closer to accordion behavior, opening one closes the other ones.) I retrieved my [sample file from 2015](https://the.fmsoup.org/uploads/short-url/i1u8Fx8a2tl87dEQnivjnoeYhjN.fmp12) (1.7 MB) . I tried to make some of it more English, but it was built for a French audience. This sample file does not implement transactions, so it is simply for showcasing the basic concepts. **The category list & detail layouts contain the things that are relevant to nested sets.** Here are some elements that are worth outlining: - Each child can only have one parent - Each extremity has right tag = left tag +1 - The left tag of a node is always included between the left and right tags of each of its parents - Getting a parent children count is easy: (right - left - 1)/2 - queries that used to rely on recursion (expensive performance wise, degrading based on the amount of data) no longer rely on recursion (performance gains) Some drawbacks: - Not as easy to find direct parent or child (only one level above or under) - Sorting the data can be challenging. - Updates usually require to update many records from the whole structure, so that requires more work (performance wise, updating multiple records can only be more expensive than updating a single record.) Recommended reading: > **[Managing Hierarchical Data in MySQL — Mike Hillyer's Personal Webspace](https://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/)** > > Introduction Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database... > **[Nested sets: Performant attribute calculation on collections - Dico Duba](https://dico.duba.dev/nested-sets-performant-attribute-calculation-on-collections/)** > > We all know what a tree is. Computer science taught us that the root is in the top, that it is occasionally red and black and that leaves look exactly like the trunk of the tree. It teaches you that if you want to store a tree, you have various... > **[Nested set model practical examples, part I.](https://www.werc.cz/blog/2015/07/19/nested-set-model-practical-examples-part-i)** > > Every developer will sooner or later solve a situation how to store hierarchical data (parent – child relation) in relational database. As there is a lot ... [https://fuelphp.com/docs/packages/orm/model/nestedset.html](https://fuelphp.com/docs/packages/orm/model/nestedset.html) ---
**Author:** ![mipiano](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/mipiano/32/1074_2.png) [@mipiano](https://the.fmsoup.org/u/mipiano)\ **Post date:** [March 14, 2021, 9:52pm UTC](https://the.fmsoup.org/t/nested-sets-another-data-model-for-record-hierarchies/1897/2 "2021-03-14T21:52:46Z")
@Bobino Thank you for your time and effort in helping me understand the nested set model. Also for the fact that you translated some things in your example file into English. Whereby: Je comprends le français dans une certaine mesure. Je l'ai appris à l'école - il y a environ 35 ans. Malheureusement, j'avais rarement l'occasion de parler français, mais au moins je lisais quelque chose de temps en temps. I will understand the file - hopefully 😉 The topic is surely interesting for others, too. I will have a look in the file tomorrow how you have approached this. And then I will experiment with my material and see how well the model works for it. Of course, I'll also try to make it transactional. Once again - thank you very very much! 👍 This is really great! And I will of course have a look at the websites you linked. ---
**Author:** ![mipiano](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/mipiano/32/1074_2.png) [@mipiano](https://the.fmsoup.org/u/mipiano)\ **Post date:** [March 14, 2021, 10:05pm UTC](https://the.fmsoup.org/t/nested-sets-another-data-model-for-record-hierarchies/1897/3 "2021-03-14T22:05:46Z")
As soon as I have worked my way through it, I will report here about my experiences with it. Whether the performance is as I hope and whether the whole thing works in my scenario. This will take a few days, but: I'll be back! 🕶