A Guide For Dealing With Hierarchical, Parent-Child And Tree Form Data Operations

Posted by Ahmed Tarek Hasan on 1/30/2015 10:27:00 PM with No comments

Lately I have been working on more than one project dealing with hierarchical data structures. This encouraged me to try to sum up my experience on this type of structures and corresponding operations.

From time to time I will revisit this post to update it with whatever new I found related to this topic. Please find the assembled posts below and let me know if you have any comments.

How To Apply Recursive SQL Selections On Hierarchical Data
Sometimes we work on systems where a hierarchical data structure exists on some entities like employees and their managers. Both employees and managers can be called employees but there is a self join relation between them as each employee must have a manager. We all faced the situation when we need to get the info about each employee and his/her direct manager. At this point we used to join between the employees (child) table and itself (parent) on the condition that the parent id of the child is equal to the id of the parent. This is good. But, what about if we need to get the hierarchical tree of managers of a certain employee not just his direct manager. It seems as we just need to the same join but more than one time till we are up all the way to the head manager. This is somehow logical but how can we do this number of joins and we don't know the number of levels up to the head manager?!!! If you want to know more, you can read this article.

How To Transform Unsorted Flat Hierarchical Data Structures Into Nested Parent-Child Or Tree Form Objects
Sometimes you have hierarchical data structure presented into an SQL database table where there is a parent-child relation between rows. You may need to transform this flat hierarchical data structure into a parent-child or tree form entity so that you can use it in more advanced or complex business like binding to a tree control or whatever. If you want to know how to do this transformation process, you can read this article.

How To Copy SQL Hierarchical Data At Run-time While Keeping Valid Internal References And Self Joins
Sometimes when you deal with hierarchical data structures you may need to perform internal copy operations. These copy operations should be handled in a smart way as just using INSERT-SELECT statements will mess up the internal references and self joins of the newly inserted records. If you want to know more about this, you can read this article.

For SQL Hierarchical Data, How To Show Only Tree Branches Including Certain Type Of Entities And Discard Others
If you have a tree of entities represented into a SQL database into parent-child relation, you may need to be able to view only the tree branches which include a certain type of entities and discard the branches which don't include this type. If you ever faced such situation or even curious to know how to manage such situation, you can read this article.

Knockout Advanced Tree Library & Control
This post shows you how to fully implement a tree control using knockout. This tree control has great features like; 01.Flat input data, 02.Dynamic node object properties, 03.Sorting by node object properties, 04.Searching by node object properties, 05.Searching by like patterns (case sensitive/insensitive) or whole words, 06.Searching by regular expressions, 07.Expanding to matching nodes, 08.Highlighting matching nodes, 09.Expand/Collapse, 10.Adding nodes, 11.Extensibility. It was taken into consideration while writing the code to separate the business-related code from the core implementation as far as possible without adding too much complexity to the whole solution. You can take this library as a base which you can modify to be adapted to your needs. Feel free to do it and for sure if you have any comments or ideas I am all ears.

That's it. Hope this will help you someday.