Member-only story
Managing deep hierarchies with RDBMS
Trees and graphs
One time or the other you might already have or you will come across a scenario where you have to store deep hierarchies in a relational database.
Some such scenarios are product categories, threaded comments, folder structures which might dynamically increase or decrease.
Products -> Electronics -> Phones And Tablets -> Brands-> Moodels
These hierarchies may change over time, the depth may increase or decrease.
For eg, the products hierarchy may change over time as new categories are introduced.
The above example shows a threaded comment from hacker news. You will notice the hierarchy which keeps on increasing endlessly.
How will you store all these comments in database and again fetch them in the same manner?
How do we manage such deep dynamic hierarchies in a relational database?
There are a few ways to go about it, we will discuss the most popular solutions out there:
- Adjacency List
- Nested Sets
- Materialised Path
- Closure Tables
Adjacency List (parent-child relationship)
In this method a simple parent child relationship is used to represent hierarchy.
You will notice every node has a parent id. This helps us to maintain the hierarchy.
Adding a new record is fairly simple. You just need to know the parent id to insert a new record.
