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
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
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 (
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
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