How to Represent and Query Hierarchical Data in SQL

A tutorial on how to build and query a hierarchical table using a relational database.

Given its flat nature, a relational database is not suitable to represent hierarchical data. However, thanks to some tricks, you can transform a relational database into good storage for hierarchical data.

In this article, I cover the following topics:

definition of hierarchical data

how to convert hierarchical data into a relational table

how to query a hierarchical table

1 Definition of Hierarchical Data

Hierarchical Data is a data structure where items are related to each other in a parent/child relationship. The following figure shows an example of hierarchical data:

At the first level, there is a parent class, called Animal, which is the father (or root) of all the other classes. Then, at the second level, there are three children, Mammal, Bird, and Fish, all at the same level. Finally, there is a third level, with all the single species (e.g. Cat, Dog, and Lion for Mammal).

Looking at the previous figure, we can say that Mammal, Bird, and Fish are brothers, while Cat, Pheasant, and Shark are cousins.

Hierarchical Data is well represented by a tree, thus a graph database could be the best solution to represent it. Alternative solutions, such as NoSQL databases also could fit, as described in this Stackoverflow thread.

Anyway, a relational database also could be exploited to store hierarchical data. In the remainder of the article, I describe how to do it.

2 Converting Hierarchical Data into a Relational Table

To convert a hierarchical data structure into a relational table, different strategies could be adopted. In this article, I describe two methodologies:

Adjacency List Model

Nested Set Model

2.1 Adjacency List Model

In the Adjacency List Model each item stores a pointer to its parent. Practically, a new attribute, describing the level in the hierarchy, should be added to the classical data properties. Let us see an example, to understand what I mean.

I suppose to have the previous Animal structure, with three levels of hierarchy. I also suppose that for each animal, the following attributes are available:

name

description

Thus, I can build the following schema for the table Animals:
Animals(animal_id,name,description,parent_id)

where parent represents the previous level in the hierarchy. As a result, I could produce the following table:

Continue Reading on Medium