mysql handling nested data for a known number of levels

Question

Now I have a hierarchical structure that looks like this:

.stage
...grade
.....semester
.......subject
.........unit
...........lesson

At first they were all stored inside the same table with an id parent relationship; however, each level of those 6 levels has its own data. For example, a lesson has featured_video_id and a subject has a language_id.

Now I'm thinking of creating 6 tables: one for each level and connect them with foreign keys. I'm thinking of all the possibilities and thought that a professional opinion will help. Will that division be of any good or it will be something that I will regret in the future?

Quite the contrary you will be relieved in the future; multiple tables help you normalise your data and allows for more flexibility, it's also faster when you have a large number of rows. Take a look at this previous question: http://stackoverflow.com/questions/9774715/mysql-multiple-tables-or-one-table-with-many-columns with emphisis on normalisation. :) — Edward, Sep 24 '13 at 19:52

unique2 · Answer 1 · 2013-09-24T20:14:30.270

1

I t makes a lot of sense to have separate tables for stages, grades, semesters, ...

You have already mentioned the best reason for this, you can add individual data for each of these levels. You can name the foreign keys in a sensible way (i.e. stage_id in the table for the grades). And I doubt you will ever need a list of subjects mixed in with lessons or semesters.

edited Sep 24 '13 at 20:14

answered Sep 24 '13 at 19:54

unique2

2,162
2
18
23

score 0 · Answer 2 · answered Sep 24 '13 at 20:00

I'd do a bit of both:

Have a master table that contains all of the items at all levels with a common primary key. Include only the attributes that are common across all levels of the hierarchy. Link them with a parent key here.
Have separate tables for only the attributes that are unique to a particular level of the hierarchy. Use the primary key in the master table as a foreign key in these "detail tables."

This lets you separate your concerns. The structure of the hierarchy is in the master table. The details of each level are in the detail tables.

mysql handling nested data for a known number of levels

2 Answers2