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:
- Oracle supports START WITH
- PostgresSQL supports WITH RECURSIVE
- MS SQL Server 2014 supports WITH
- MySQL doesn’t support hierarchical queries out of the box but it can be emulated
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:
- Adjacent List Model
- Hibernate
- Maven
- MySQL
- Primefaces
- Spring
- Tomcat
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:
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.