Now I have a hierarchical structure that looks like this:
现在我有一个如下所示的层次结构:
.stage
...grade
.....semester
.......subject
.........unit
...........lesson
.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.
起初它们都存储在具有id父关系的同一个表中;但是,这6个级别中的每个级别都有自己的数据。例如,课程有features_video_id,主题有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?
现在我正在考虑创建6个表:每个级别一个,并使用外键连接它们。我正在考虑所有的可能性,并认为专业意见会有所帮助。这个部门会有什么好处,或者将来我会后悔的事情会是什么?
2 个解决方案
#1
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.
您已经提到了最佳原因,您可以为每个级别添加单个数据。您可以以合理的方式命名外键(即表中的stage_id作为成绩)。我怀疑你是否需要一系列与课程或学期混合的科目。
#2
0
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
parentkey 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.
这可以让您分开您的顾虑。层次结构的结构位于主表中。每个级别的详细信息都在详细信息表中。
#1
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.
您已经提到了最佳原因,您可以为每个级别添加单个数据。您可以以合理的方式命名外键(即表中的stage_id作为成绩)。我怀疑你是否需要一系列与课程或学期混合的科目。
#2
0
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
parentkey 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.
这可以让您分开您的顾虑。层次结构的结构位于主表中。每个级别的详细信息都在详细信息表中。