Documentation
v4.1 and below
SQL Tools
mySQL Examples
Robust
Hierarchical Database
MySQL is a relational database. However, there are times when we need to manage hierachical data. For examples, menus and table of contents are hierachical structures. Developers struggle with creating hierachical databases using a relational DBMS. This does not have to be the case, and one can create a database to manage many hierachical nodes using a single table. Yes, I said a single table.
I have modifed this example so that you can understand how it is implemented and utilized. Further it comes with a few sql examples to demonstrate how to access the data for the nodes.
Database design
The table consists of a primary key column "entity" and a related column named "entity_id". There is a column named "descendent" which can hold the foreign key back to the entity. If this column is null it means that the row represents the top level in the hierarchy. Given this architecture their is no theoretical limit to the hierarchy.
The "classification" column allows you to group a hierarchical entity. It would be only a matter of adding additional columns to allow you to further deliniate the hierachical entities.
Accessing a Node and all of its subnodes
The data is accessed by doing a self join on this table. You can predetermine in the query how many levels you want in the hierarchy.
SELECT
mainbranch.entity as "rootnode"
,branch2.entity as node2
,branch3.entity as node3
,branch4.entity as node4
,branch5.entity as node5
FROM hierarchy AS mainbranch
LEFT JOIN hierarchy AS branch2 ON branch2.descendent = mainbranch.entity_id
LEFT JOIN hierarchy AS branch3 ON branch3.descendent = branch2.entity_id
LEFT JOIN hierarchy AS branch4 ON branch4.descendent = branch3.entity_id
LEFT JOIN hierarchy AS branch5 ON branch5.descendent = branch4.entity_id
WHERE
mainbranch.entity = 'Vegetables'
AND mainbranch.classification is null;
As you can see by this query we can access a hierarchy that is 5 nodes deep. Also, I have hardcoded the query for ease of explanation. You will want to turn this value into a variable, or better still, turn the whole thing into a stored procedure.
Parting Words and Downloads
This example was demonstrated using the open source MySQL Query Tool that you can download from the MySQL website.
To download the hierachical database click on the link. This download also contains SQL examples for "extracting a list of nodes" and "returning a list of root nodes" as well as other functionality.