Introduction to Hierarchical Data in Databases, Part Three

If you’ve been following along like a good little boy or girl, then you should now be familiar with basic parent-child relationship and tree hierarchy structures in relational databases. We’ve been using our favorite family, the Huxtables, as a guideline to represent these structures. However, we’ve been missing the most important person of this family tree: Mommy! Don’t make the mistake of sarcastically posting on your mother’s Facebook wall that you weren’t going to call her on her birthday, then not actually call her. Trust me, you’ll feel bad about it. Instead, let’s honor our moms and include them on this article’s family tree:

What makes this structure different from our previous iterations is that nodes in our tree can have more than one parent. Previously, we only allowed one to many relationships, but there are certainly cases where we want to hold a hierarchy with the ability to have many to many relationships. We accomplish this by normalizing our relationship. Here’s how it looks in a SQL table:

>SELECT * FROM family;
+----+------------------+
| id | name             |
+----+------------------+
| 0  | Russell          |
+----+------------------+
| 1  | Anna             |
+----+------------------+
| 2  | Dr. H. Huxtable  |
+----+------------------+
| 3  | C. Huxtable, ESQ |
+----+------------------+
| 4  | Sondra           |
+----+------------------+
| 5  | Denise           |
+----+------------------+
| 6  | Theo             |
+----+------------------+
| 7  | Vanessa          |
+----+------------------+
| 8  | Rudy             |
+----+------------------+


>SELECT * FROM relationships WHERE child_id = 2; +----+-----------+----------+ | id | parent_id | child_id | +----+-----------+----------+ | 0 | 0 | 2 | +----+-----------+----------+ | 1 | 1 | 2 | +----+-----------+----------+

With this structure, we can now denote multiple parent nodes. With one simple JOIN, we can find out Theo’s parents:

>SELECT name FROM relationships LEFT JOIN family ON (parent_id = family.id) WHERE child_id = 6;

Like our previous structures, this hierarchy representation also has its shortcomings. Accessing grand-children nodes is equally difficult in this structure as our initial structure in Part 1. Tune in next time to see what the Huxtables are up to next!

Published
Categorized as Database

By Michael Marr

Michael Marr is a staff writer for WebProNews

Leave a comment