Not sure how to store multi layered table data Watch
I'm teaching myself web at the moment. In one of my projects on the front end I've made a table that has three 'layers', which drop down to show each underlying layers data (to try and explain what I mean, imagine having three spoilers tags, each with their own set of inner spoiler tags, and each of those having their own set of inner spoiler tags).
Now I'm not sure how to actually store the data that will be going into the table. A friend of mine suggested having a single table, with a parent_id key and a child_id key. The top layer of data would have a parent_id of null. I'd loop through the table and get all the records with a parent_id of null. Then for each one of those, I'd loop through the table and find all records whose parent_id matched the child_id of the currently selected top level record. Then, for each one of those, I'd loop through again to find all the final layer data whose parent_id again matched the child_id of the currently selected middle layer of data. (Sorry, I did say it might be confusing ).
If you've followed, is this the best way of doing it? Are there better ways?
To put into context what it is I'm making this for, it's like an invoice. Each 'top level' is a category which drops down when clicked to show sub-categories. When one of those is clicked, it drops down again to show individual items.
A couple of comments:
- Are you actually talking about tables with tabular data (e.g. a timetable) or are you using HTML tables for the purposes of creating your layout? If the latter, stop it now. This isn't 1999. Learn to use HTML5 and CSS and you will find this much easier.
- It is very difficult (if not impossible) to help based on descriptions of what you're doing. You need to post your actual markup, actual styles and - if applicable - actual code so people can see what you are actually doing and figure out where you're going wrong.
Id parentid Name
1 null Bert
2 1 Bert junior
3 2 Bert the younger
Etc can go as deep as you like.The entire family tree can be stored in that table.
The fun part is querying that table for the data
You can use a CTE to write a recursive query.
Recursion can be quite a difficult concept at first but if your using hierarchical data then you need to be comfortable with it and the solutions are often short and elegant. If you catch yourself writing nested for loops then stop. I'm not saying you can't do it with nested loops just that recursion is better