Introduction to Hierarchical Data in Databases

When it comes to structuring data, hierarchical systems are now the norm. Being able to quickly relate a given data element to a parent or child element is very common in content and document management systems, forums, and a majority of other common applications. However, going beyond the basic parent-child relationship opens up more advanced data relationships and functions.

The standard parent-child relationship looks like this:

+-----------------+-----------------+
| parent          | name            |
+-----------------+-----------------+
|                 | Dr. H. Huxtable |
+-----------------+-----------------+
| Dr. H. Huxtable | Sondra          |
+-----------------+-----------------+
| Dr. H. Huxtable | Denise          |
+-----------------+-----------------+
| Dr. H. Huxtable | Theo            |
+-----------------+-----------------+
| Dr. H. Huxtable | Vanessa         |
+-----------------+-----------------+
| Dr. H. Huxtable | Rudy            |
+-----------------+-----------------+

With this structure, we can easily identify one relationship: parent to child.

Find all children:

>SELECT name FROM family WHERE parent = 'Dr. H. Huxtable';

Find parent:

>SELECT parent FROM family WHERE name = 'Theo';

Why limit ourselves to this most simplistic representation of our data relationships? It would certainly work for the data set above, but what happens when a similar dataset grows just slightly?

Now we have multiple levels of parent-child relationships. What if we wanted to find all of Russell’s descendants, i.e. children, grand-children, and great-grandchildren. There is no way to do this with the parent-child structure without multiple joins or sub-queries. Even then, it would prove to be very difficult, inefficient, or even impossible to implement based on our database management system. In these cases, we end up bringing down multiple datasets through multiple database calls to our application. This requires additional coding and fails to take full advantage of the speed and efficiency of our DBMS.

In our next article, we’ll examine how we can take the example above and structure it in a more hierarchical friendly fashion.

Published
Categorized as Database

By Michael Marr

Michael Marr is a staff writer for WebProNews

Leave a comment