Introduction to Hierarchical Data in Databases, Part Two

In the previous installment of Introduction to Hierarchical Data in Databases, we were introduced to a simple parent-child relationship hierarchy. We could easily represent this in one SQL table. However, as time passed, our children began to have children, and now we have a multi-leveled parent-child relationship. This could still be represented in a single SQL table, but gathering information like "Who are all my grand-children?", or "Who are all my descendants?" becomes very cumbersome. Let’s take a look at a slightly improved structure for representing this.

Our goals for improving upon our existing table structure for part 1 is to make the above tree easier to work with. One of the primary shortcomings of the above tree in our current table structure is that finding descendants (other than immediate children) of a particular node is very cumbersome, involving either recursive functions or multiple SQL joins. Here’s how we can visually improve this tree:

Here’s what we did: Russell is our starting node, so we number him 1. His only child is Dr. H. Huxtable, so we number him 2. Dr. Huxtable has many children, so we start with his oldest, Sondra, at 3. Sondra has no children, so we close her node with 4. Denise, the next child of Dr. Huxtable, starts with 5. Denise has a child, so her child, Olivia, gets 6 to open her node. Olivia has no children, so she then gets closed by 7, and her mother subsequently closed by 8 (because Olivia has no siblings). Follow this pattern down the line and back up, closing Russell with number 16.

To represent this in a SQL table:

+-----------------+-----------------+-------+-------+
|  parent         | name            | open  | close |
+-----------------+-----------------+-------+-------+
|                 | Russell         | 1     | 16    |
+-----------------+-----------------+-------+-------+
|  Russell        | Dr. H. Huxtable | 2     | 15    |
+-----------------+-----------------+-------+-------+
| Dr.  H. Huxtable| Sondra          | 3     | 4     |
+-----------------+-----------------+-------+-------+
| Dr.  H. Huxtable| Denise          | 5     | 8     |
+-----------------+-----------------+-------+-------+
|  Denise         | Olivia          | 6     | 7     |
+-----------------+-----------------+-------+-------+
|  Dr. H. Huxtable| Theo            | 9     | 10    |
+-----------------+-----------------+-------+-------+
| Dr.  H. Huxtable| Vanessa         | 11    | 12    |
+-----------------+-----------------+-------+-------+
| Dr.  H. Huxtable| Rudy            | 13    | 14    |
+-----------------+-----------------+-------+-------+
  

Now, let’s say we want to find all of Russell’s descendants:

>SELECT  name FROM family WHERE open > 1 and close < 16;

With this enhanced database structure, we have many more options on how to manipulate and access our hierarchical data. Stay tuned for further exploration of hierarchical data in databases.

Published
Categorized as Database

By Michael Marr

Michael Marr is a staff writer for WebProNews

Leave a comment