Introduction to Hierarchal Data in Databases, Part Four

Are we there yet? Well, if you were looking for an amazingly awesome way to store Hierarchal Data in Databases, then we may have already passed what you were looking for there, there, or even there! The fact is, like most database structures, there often isn’t one size fits all solutions. Each schema has advantages and disadvantages, and thus you should choose wisely. We’ll continue our journey today by combining our structures from parts two and three.

Let’s take a look:

So now we have combined the ability to have a one-to-many relationship with the ability to easily drill down descendant nodes. Let’s take a look at our database structure:

>SELECT * FROM family;
+----+------------------+-------+-------+
| id | name             | open  | close |
+----+------------------+-------+-------+
| 0  | Russell          | 1     | NULL  |
+----+------------------+-------+-------+
| 1  | Anna             | NULL  | 14    |
+----+------------------+-------+-------+
| 2  | Dr. H. Huxtable  | 2     | NULL  |
+----+------------------+-------+-------+
| 3  | C. Huxtable, ESQ | NULL  | 13    |
+----+------------------+-------+-------+
| 4  | Sondra           | 3     | 4     |
+----+------------------+-------+-------+
| 5  | Denise           | 5     | 6     |
+----+------------------+-------+-------+
| 6  | Theo             | 7     | 8     |
+----+------------------+-------+-------+
| 7  | Vanessa          | 9     | 10    |
+----+------------------+-------+-------+
| 8  | Rudy             | 11    | 12    |
+----+------------------+-------+-------+

So, with our new structure, we could find out who all of Dr. H. Huxtable and C. Huxatble, ESQ’s children are:

>SELECT * FROM family WHERE open > 2 AND close < 13;

Also, when Theo gets in trouble, we can easily find out who his parents are:

>SELECT * FROM relationships WHERE child_id = 6;

Well, each of these structures have ended up with a shortcoming. Can you spot this one’s? Data integrity. There are going to be a lot of checks and rules to maintain proper data integrity in this structure. However, implemented properly with checks in place, this structure can be very powerful.

The end goal, however, is to accomplish hierarchal data in a way that is fast and easy to maintain. We’ve explored a variety of ways to represent this data in standard SQL schemas. However, as you have seen, they all come up short in one way or another. Thus, the need for a new form of databases have arrived on the scene: graph databases. These database are typically in the NoSQL flavor, and we’ll cover them in more detail, including examples from some of the leading platforms, over the next series of articles.

Published
Categorized as Database

By Michael Marr

Michael Marr is a staff writer for WebProNews

Leave a comment