Hierarchical Database Structures in RDBMS
Akash Mishra (~akash7) |
What do google drive, threaded comments system, e-commerce categories and an employee management system have in common ? Their data has recursive relationships. It can be organised in a treelike or hierarchical structure. In such a data model, each row is a node. A node may have many children and one parent. The top node which has no parent is called root. Your query patterns may involve querying individual nodes, all children of a node, siblings of a node etc.
Lets take the example of an organization chart, each employee has a manager, who represents the employee’s parent in a tree structure. The manager is also an employee.
Another example would be for threaded comments, a tree structure may be used for the chain of comments in reply to other comments. All comments in reply to other comments are replies and a comment with no parent is the root comment.
In this talk, I will take you through different approaches to organising your hierarchical data. I seek to outline advantages and disadvantages of each with respect to CRUD operations on an employee management system for an organization.
- Trees and Hierarchies
- Approaches to modelling trees in RDBMS
- Adjacency List
- Path Enumeration
- Nested Sets
- Closure Table
I will also point to the appropriate implementation for each of the approaches with respect to the django web framework. However, the concepts discussed can be implemented for any framework and RDBMS of your choice.
- Data modelling in RDBMS's
- Understanding of Tree data structure
Akash Mishra works as a Senior Backend Engineer at Fueled, Noida. He has 3+ years of backend development experience with Python/Django and has worked on implementing a lot of complex data relationships on multiple projects. His interest lies in the domain of functional programming and distributed systems.