How best to design a hierarchy in mysql

Andrea Edwards
Andrea Edwards used Ask the Experts™
on
Hello

I have seen many complex articles about how best to represent a hierarchy in a database. For example I saw this post which is beyond my level of myself

http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database

I also saw this article where you have a very simple database design for the hierarchy and then filter using php. http://stackoverflow.com/questions/41291683/obtaining-mysql-to-get-hierarchical-data-structure?rq=1

The database deign in this last link was how I planned to store the data:

e.g. call table Heirarchy
Fields
id
descrition
parentid (FK which links to back to another row in the same table)

In general when I have asked questions I am advised the database was designed to handle the data you want and its retrieval, not the language you are looking for

As I said the first link in the question was too in depth for my needs. Being as this must be such a common requirement I imagined there must be a database design that lets you have an unlimited number of rows in your hierarchy and then do queries like

1) get all parents in the hierarchy (e.g. select * from hierarchy where parentId is null)
2) get second row in hierarchy (e.g. select * from hierarchy as a inner join heirarchy as b on a.parent id = b.id where b.parentID is null

It then started to get more complex where I was thinking how to get third level items in the hierarchy.


e.g. select a.*  from hierarchy as a inner join ( select c.id from hierarchy as c inner join hierarchy as d where d.parentID is null) as e where a.parentid = c.id

It naturally gets more convoluted the number of levels in a hierarchy there is.

I was hoping there may be a simpler database solution for this where I do not need to understand all the features mentioned here (
http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database)

The hierarchy will not be very big and unlikely to have more than 4 levels. I wasn't sure whether my simple database design would work and I could create some stored procedures to get the rows I want. I have learned from previous experiences it is quicker to have simple queries and temporary tables than have complex join statements. In one question I asked on here, the stored procedure with temp tables took less than a second but the sql query with lots of joins in it took 5 seconds (this was a big database with millions of entries)

The database in this example if not very big and I cannot imagine the hierarchy having more than 4 levels but naturally I want it to be extensible. There are  about 7 top levels and 5 second levels and 2 third level entries. Due to the small amount of data and its simplicity I hoped to avoid having to investigate all the options here
http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database)

An obvious cheat would be to have a field in the hierarchy table like 'hierarchy level' but this would required editing the tables if the hierarchy changed, if the hierarchy changed I would have to edit the tables anyway but I shouldn't need to alter the tables if a new hierarchy is added. I always like to do the best solution but sometimes time outweighs the input needed.

Many thanks for your help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
I am afraid that if you want to easily retrieve hierarchical data then you have to switch engine and move to MS SQL..
MS SQL has the CTE which allow with ease to perform a recursive query and get all the data in the hierarchy...
Take a look in this article to get you started...
Also don't forget that SQL Express allows up to 10Gb database and its completely free..
Most Valuable Expert 2011
Top Expert 2016

Commented:
a simpler database solution for this where I do not need to understand all the features...
As Euclid said to Ptolemy, "Non est regia ad Geometriam via."  This is an advanced topic in computer science, and you might be better off hiring a professional to do the design, rather than asking questions in an online forum.  In order to really understand the design you need to grasp two key concepts: doubly-linked lists and recursion.  The structure for something like this is a tree.  Databases are, by nature, relational, whereas JSON or XML may be better suited to expressing a hierarchy.  But if you really want to use a database, the multiple lineage design might be the easiest path forward.

Commented:
1. You don't need MS SQL.

2. "parentid (FK which links to back to another row in the same table)" is almost always your best bet for hierarchies of unknown depth, and it usually scales very well and is handled by just about every programming language that can connect to a database like MySQL.

3. If you have a situation where you have items in a product catalog (or similar situation), then you would normally want 2 tables. One table controls that folders and the folder hierarchy. The second table holds items that have a folder ID that links back to the first table.

In this approach, you have a slim folder structure that can be queried very quickly in order to build out a menu system (and if the hierarchy is small enough, it can be more efficient to pull the whole thing down, process it into the desired hierarchy via code, and cache the processed results in a JSON or serialized file so you can near-instantly get the processed results again and again without hitting the database each time.

Meanwhile, the database can simply deal with querying the items from the desired level without having to deal with any joins at all. And if you want items from multiple levels at once, the code can process the in-memory hierarchy pretty quickly to get the parent IDs in your hierarchy, and then you simply pass all of those IDs to the query that returns your items so you're getting them all at once.

Author

Commented:
Hello

Thank you to everyone who has replied.

To ray, I cannot hire a professional as this is an online project I am doing on a voluntary basis as I think it is desperately needed. I do like to do things properly but doing this on a voluntary basis means I need to take short cuts. If someone sees my voluntary project and wants to help me fund it then I will take your route for sure. I remember the days of linked lists in C with pointers. It seems a million miles away now.

To gr8gonzo, I am sticking with mysql as I am familiar with it and I just generally like it so I am considering your answer. My hierarchy doesn't have the complexity of products in a catalgoue. It is a health website. An example might be a section on mental illness,. The top levels would be anxiety, depression, ocd and self harm etc. Considering self harms there are second level sub categories such as  physical self harm, substance abuse. Substance abuse then has many third level children e.g. alcohol, drugs. I'm sure there are many more but I am no expert; this is just an example.

I like your example and the cached json data. I don't understand what you mean that the database wouldn't have to deal with joins at all
Would you mind giving me an example of how I would retrieve all items in, say, level 3 of the hierarchy (using this example that would be alcoholism, drug abuse). Do you mean getting this from a json file or an sql query? I don't expect you show me how to query the json file but if that is the method  you are referring to, please can you confirm? Were you thinking of this approach (http://stackoverflow.com/questions/41291683/obtaining-mysql-to-get-hierarchical-data-structure?rq=1). If you are talking about a json file I presume you can create this is and process it before the website even goes live?

 If you are considering this at the sql level I would greatly appreciate an example query.

Kind regards

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial