Working with tree data in relational databases

Introduction

Working with tree domain models is quite common in software development. As it’s not only common but also interesting I decided do some research on this topic to gain some new experience and know-how. The goal of this article is to present a short overview of essential base knowledge about storing/querying hierarchical data in relational databases. After the theoretical part a web application is introduced that handles tree data gained from the database and displays it on client side.

Storing hierarchical data in a relational database

First of all there are some different techniques available to store that kind of data in a relational database. Most articles I found during my research covered the following techniques:

  • Adjacent List Model
  • Nested Set Model
  • Nested Intervals Model

While writing this article a list with good references is available here.

In the following I will not cover the pros and cons of these techniques. However be aware that each one has its strengths and weaknesses. Therefore you should know your use case in advance to be able to make the best decision. Some techniques may have strengths in moving nodes; some techniques may have strengths in finding nodes, etc.

Database support for hierarchical data

Knowing about different techniques to store this kind of hierarchical data is one thing. Another one is to know your database and how the product supports hierarchical queries:

Hierarchical tree data an example web application

To gain first development experience on this topic I implemented an example web application using the following technologies:

My project is available on GitHub: https://github.com/Eden33/tree-data
As of commit #6106c17 the web application displays the tree data gained from database like this:

Tree data

Summary

The goal of this article was to present some base knowledge needed to start working with hierarchical data in relational databases. Furthermore to increase the base knowledge with help of a practical example a web application was introduced and made publicly available on GitHub.

Comments are closed.