Not sure how to store multi layered table data Watch

Maccees
Badges: 15
Rep:
?
#1
Report Thread starter 5 years ago
#1
This might be very confusing.

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 :lol:).

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.
0
reply
Planto
Badges: 16
Rep:
?
#2
Report 5 years ago
#2
It sounds like you are trying to build nested accordions.

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.
0
reply
INTit
Badges: 17
Rep:
?
#3
Report 5 years ago
#3
From the database side: You create a self referencing table:

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.
http://technet.microsoft.com/en-us/l...ql.105%29.aspx

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
0
reply
X

Quick Reply

Attached files
Write a reply...
Reply
new posts
Back
to top
Latest
My Feed

See more of what you like on
The Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.

Personalise

Has your university offer been reduced?

Yes (50)
30.86%
No (84)
51.85%
Don't know (28)
17.28%

Watched Threads

View All
Latest
My Feed

See more of what you like on
The Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.

Personalise