Hierarchical Database Structures in RDBMS

Akash Mishra (~akash7)


2

Votes

Description:

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.

Talk Outline

  • Trees and Hierarchies
  • Approaches to modelling trees in RDBMS
    1. Adjacency List
    2. Path Enumeration
    3. Nested Sets
    4. Closure Table
  • Summary
  • References

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.

References

  1. https://www.slideshare.net/billkarwin/models-for-hierarchical-data
  2. https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database

Prerequisites:

  • Data modelling in RDBMS's
  • Understanding of Tree data structure

Video URL:

https://youtu.be/056K_Wndnhg

Speaker Info:

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.

Speaker Links:

https://github.com/tucosaurus

Section: Web development
Type: Talks
Target Audience: Intermediate
Last Updated: