数据库设计/规范化结构需要包含ANDs、ORs、可选元素及其关系[英] Database design / normalization structure needs to contain ANDs, ORs, optional elements and their relationships

本文是小编为大家收集整理的关于数据库设计/规范化结构需要包含ANDs、ORs、可选元素及其关系的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我想将大学课程的详细信息存储在(MySQL)数据库中,但我不确定如何维护模块和选择之间的关系.

基本上,一门课程可以具有强制性部分,一组可选模块,一个选项部分,每个部分都可以选择包含模块之间的and或or.

.


简单示例:
60个学分课程有一些强制性模块,构成了40个学分.从可选模块组中选择20个学分. (模块本身可以持有不同数量的学分).有效; ('Mandatory module 1' AND 'Mandatory module 2'... AND'Mandatory module N') AND (40 credits from 'optional modules'),

ands&ors :
当我说上面的模块时,它可能是一个模块,也可以是"模块x或模块y",即在强制性部分中. (这些模块显然必须具有相同的信用额). 或在可选部分中,可能有单个模块,甚至其中一个选择可能是"module x AND module y".

选项:
学生可能必须采用强制性模块以及可能包含或不包含或可能不包含ORS以及强制性和可选部分的 n 选项之一;即"选项"具有整体课程模块选择的所有属性.选项部分将为或或与其他部分(例如强制性或可选)进行或或或"或";即强制性模块"加上以下选项之一".实际上,选项部分只是'Option 1' OR 'Option 2'... OR 'Option N'.


问题是,当操作数可能是另一个和/或操作或单个模块时,我如何存储所有和或或关系,并跟踪每个选择允许的信用量;例如"以下20个学分:"(一组可选模块).

推荐答案

设计相当简单,您只需要一个带有约束的递归"组"表.

Course
- ID
- Title
- Credits

Course_Group
- CourseID
- GroupID

Group
- ID
- GroupID
- Description
- AtLeastNSelections
- AtLeastNCredits

Group_Module
- GroupID
- ModuleID

Module
- ID
- Title
- Credits

示例结构将是

Course: 1, "Math Major", 60
Group: 1, NULL, "Core Modules", 2, 40
Course_Group: 1, 1
    Group: 2, 1, "Required (5) Core Modules", 5, 25
    Course_Group: 1, 1
    Group_Module: (1, 1), (1, 2), (1, 3), (1, 4), (1, 5)
        Module: 1, "Calculus I", 5
        Module: 2, "Calculus II", 5
        Module: 3, "Calculus III", 5
        Module: 4, "Stats I", 5
        Module: 5, "Stats II", 5
    Group: 3, 1, "Required (3) Of (N) Modules", 3, 15
    Course_Group: 1, 3
    Group_Module: (3, 6), (3, 7), (3, 8), (3, 9), (3, 10)
        Module: 6, "Number Theory", 5
        Module: 7, "Bridge Adv. Math", 5
        Module: 8, "Calculus IV", 5
        Module: 9, "Stats III", 5
        Module: 10, "Finite Math", 5
Group: 4, NULL, "Secondary Modules", 1, 20
Course_Group: 1, 4
    Group: 5, 4, "Comp. Sci.", 2, 0
    Course_Group: 1, 5
    Group_Module: (5, 11), (5, 12), (5, 13), (5, 14), (5, 15), (5, 16)
        Module: 11, "Math in Hardware", 4
        Module: 12, "Math in Software", 4
        Module: 13, "Programming 101", 4
        Module: 14, "Algorithms 101", 4
        Module: 15, "Programming I", 5
        Module: 16, "Programming II", 5
    Group: 6, 4, "Physics", 0, 8
    Course_Group: 1, 6
    Group_Module: (6, 17), (6, 18), (6, 19), (6, 20)
        Module: 17, "Physics Mechanics", 4
        Module: 18, "Physics Thermodynamics", 4
        Module: 19, "Physics Magnetism", 5
        Module: 20, "Physics Theoretical", 5
    Group: 7, 4, "Gen. Ed.", 0, 0
    Course_Group: 1, 7
    Group_Module: (7, 21), (7, 22), (7, 23), (7, 24)
        Module: 21, "Business Writing", 3
        Module: 22, "Ethics", 3
        Module: 23, "Aesthetics", 3
        Module: 24, "Graphic Design", 3

快速步行...课程"数学专业"在其中有两个组"核心模块"和"次级模块". "核心模块"至少需要2个孩子和至少40个学分. "次级模块"至少需要1个孩子和20个学分.

您可以看到"核心模块"下组的约束比"次级模块"下的组的约束更具限制性.

输出上面的示例结构将是.

SELECT c.Title, g.Description, m.Title FROM Course c
 INNER JOIN Course_Group cg ON c.ID = cg.CourseID
 INNER JOIN Group g ON cg.GroupID = g.ID
 INNER JOIN Group_Module gm ON g.ID = gm.GroupID
 INNER JOIN Module m ON gm.ModuleID = m.ID
WHERE c.ID = 1
ORDER BY g.GroupID, g.ID, m.Title  

因此,如果您有课程和模块,则可以从课程_group表中获取课程的所有组,并获取模块属于group_module表的组.一旦您的组中有模块,您就可以检查小组的约束atleastnselections和Atleastncredits沿着小组行驶.组parentage链,直到您到达group.groupid = null.

.

其他推荐答案

一种非常简单的第一种方法将仅使用4个表:

TABLE Course 
( CourseId 
, Title 
, TotalCredits 
, ... other stuff
, PRIMARY KEY (CourseId)
) ;

TABLE Module 
( ModuleId 
, Description 
, Hours
, Credits
, ... other stuff
, PRIMARY KEY (ModuleId)
) ;

和通过这2:

允许的组合
TABLE Course_Module 
( CourseID                 --- for this course
, ModuleID                 --- this module is allowed (optional or mandatory)
, PRIMARY KEY (CourseID, ModuleId)
, FOREIGN KEY (CourseId) 
    REFERENCES Course (CourseId)
, FOREIGN KEY (ModuleId)
    REFERENCES Module (ModuleId)
) ;

TABLE Course_MandatoryModule 
( CourseID                  --- for this course
, ModuleID                  --- this module is mandatory
, PRIMARY KEY (CourseID, ModuleId)
, FOREIGN KEY (CourseID, ModuleId)
    REFERENCES Course_Module (CourseID, ModuleId)
) ;

现在,如果您的描述暗示,如果您允许的模块和课程组合更为复杂,而不是Course_Module和Course_MandatoryModule表,则可以定义一个复杂的层次结构模型:

课程:

TABLE Course                        --- same as previous model
( CourseId 
, Title 
, TotalCredits 
, ... other stuff
, PRIMARY KEY (CourseId)
) ;

模块和(模块)组:

TABLE ModuleEntity                  --- the supertype for both
( ModuleEntityId                    --- modules and group of modules
, PRIMARY KEY (ModuleEntityId)
) ;

TABLE Module                        --- subtype
( ModuleId 
, Description 
, Hours
, Credits
, ... other stuff
, PRIMARY KEY (ModuleId)
, FOREIGN KEY (ModuleId) 
    REFERENCES ModuleEntity (ModuleEntityId)
) ;

TABLE ModuleGroup                  --- group of modules
( ModuleGroupId                    --- subtype of the supertype (entity)
, GroupDescription        
, PRIMARY KEY (ModuleGroupId)
, FOREIGN KEY (ModuleGroupId) 
    REFERENCES ModuleEntity (ModuleEntityId)
) ;

和关系(模块属于组):

TABLE Module_in_Group  
( ModuleEntityId               --- this module or group
, ModuleGroupId                --- is in this group
, PRIMARY KEY (ModuleEntityId, ModuleGroupID)
, FOREIGN KEY (ModuleEntityId)
    REFERENCES ModuleEntity (ModuleEntityId)
, FOREIGN KEY (ModuleGroupId)
    REFERENCES ModuleGroup (ModuleGroupId)
) ;

和(最后)课程可以具有一组模块:

TABLE Course_ModuleGroup
( CourseId                 --- for this course
, ModuleGroupId            --- this module group is allowed
, PRIMARY KEY (CourseID, ModuleGroupId)
, FOREIGN KEY (CourseId) 
    REFERENCES Course (CourseId)
, FOREIGN KEY (ModuleGroupId)
    REFERENCES ModuleGroup (ModuleGroupId)
) ;

其他推荐答案

您可以在此处创建递归表结构,其中选项引用其父选项.

  • 然后,可以通过向所有选项查询使用" null"父母的所有选项来识别"主要"选项.

  • 可以通过单独的"选项集"表实现"和或"关系,其中主键是"选项".带有零引用的选件集表是定义课程选项的"根"点.从那时起,您将选择带有parent = root的选项集记录.这将是选项的第一个"级别".有些将是强制性的,有些不会.为了表达这一点,您必须在选项表上作为标志上的布尔属性.因此,每个选项集都是根据较小的选项集定义的.当然,最终,一旦您到达底部,您的选项集将在某个时候定义实际类.

我建议这可以更有效地在JSON或XML中建模,因为这些数据结构以更具表现力的方式支持层次结构.

本文地址:https://www.itbaoku.cn/post/597757.html

问题描述

I want to store the details of college courses in a (MySql) database but I'm not sure how to maintain the relationship between modules and selections.

Basically, a course can have mandatory section, group of optional modules, an options section and within each there can be selections which contain ANDs or ORs between modules.


Simple example:
A 60 credit course has a few mandatory modules which make up 40 credits. That leaves 20 credits to be selected from the group of optional modules. (Modules themselves can hold different amount of credits). Effectively; ('Mandatory module 1' AND 'Mandatory module 2'... AND'Mandatory module N') AND (40 credits from 'optional modules'),

ANDs & ORs:
When I say modules above, it could be a single module or it could be "Module x OR Module Y" i.e. in the mandatory section. (those modules would obviously have to have the same credit weight). Or in the optional section there might be single modules or even one of the choices could be something like "module x AND module y".

Options:
The students may have to take the mandatory modules plus one of n options which may or may not contain ANDs, ORs, and mandatory & optional sections; i.e. An 'Option' has all the attributes of the overall course modules selection. The Options section would be AND'd or OR'd with other sections like mandatory or optional; i.e. mandatory modules "plus one of the following options". Effectively the options section is just 'Option 1' OR 'Option 2'... OR 'Option N'.


The problem is how do I store all of the AND and OR relationships when the operand may be another AND/OR operation or a single module, and keep track of the amount of credits allowed for each selection; e.g. "20 credits from the following:" (group of optional modules).

推荐答案

The design is fairly straight-forward you just need a recursive "group" table with constraints.

Course
- ID
- Title
- Credits

Course_Group
- CourseID
- GroupID

Group
- ID
- GroupID
- Description
- AtLeastNSelections
- AtLeastNCredits

Group_Module
- GroupID
- ModuleID

Module
- ID
- Title
- Credits

An example structure would be

Course: 1, "Math Major", 60
Group: 1, NULL, "Core Modules", 2, 40
Course_Group: 1, 1
    Group: 2, 1, "Required (5) Core Modules", 5, 25
    Course_Group: 1, 1
    Group_Module: (1, 1), (1, 2), (1, 3), (1, 4), (1, 5)
        Module: 1, "Calculus I", 5
        Module: 2, "Calculus II", 5
        Module: 3, "Calculus III", 5
        Module: 4, "Stats I", 5
        Module: 5, "Stats II", 5
    Group: 3, 1, "Required (3) Of (N) Modules", 3, 15
    Course_Group: 1, 3
    Group_Module: (3, 6), (3, 7), (3, 8), (3, 9), (3, 10)
        Module: 6, "Number Theory", 5
        Module: 7, "Bridge Adv. Math", 5
        Module: 8, "Calculus IV", 5
        Module: 9, "Stats III", 5
        Module: 10, "Finite Math", 5
Group: 4, NULL, "Secondary Modules", 1, 20
Course_Group: 1, 4
    Group: 5, 4, "Comp. Sci.", 2, 0
    Course_Group: 1, 5
    Group_Module: (5, 11), (5, 12), (5, 13), (5, 14), (5, 15), (5, 16)
        Module: 11, "Math in Hardware", 4
        Module: 12, "Math in Software", 4
        Module: 13, "Programming 101", 4
        Module: 14, "Algorithms 101", 4
        Module: 15, "Programming I", 5
        Module: 16, "Programming II", 5
    Group: 6, 4, "Physics", 0, 8
    Course_Group: 1, 6
    Group_Module: (6, 17), (6, 18), (6, 19), (6, 20)
        Module: 17, "Physics Mechanics", 4
        Module: 18, "Physics Thermodynamics", 4
        Module: 19, "Physics Magnetism", 5
        Module: 20, "Physics Theoretical", 5
    Group: 7, 4, "Gen. Ed.", 0, 0
    Course_Group: 1, 7
    Group_Module: (7, 21), (7, 22), (7, 23), (7, 24)
        Module: 21, "Business Writing", 3
        Module: 22, "Ethics", 3
        Module: 23, "Aesthetics", 3
        Module: 24, "Graphic Design", 3

A quick walk through... the course "Math Major" has two groups under it "Core Modules" and "Secondary Modules". "Core Modules" requires AT LEAST 2 children AND AT LEAST 40 credits. "Secondary Modules" requires AT LEAST 1 child AND AT LEAST 20 credits.

You can see that the constraints of the groups under "Core Modules" are more restrictive than the constraints of the groups under "Secondary Modules".

To output the example structure above would be something like.

SELECT c.Title, g.Description, m.Title FROM Course c
 INNER JOIN Course_Group cg ON c.ID = cg.CourseID
 INNER JOIN Group g ON cg.GroupID = g.ID
 INNER JOIN Group_Module gm ON g.ID = gm.GroupID
 INNER JOIN Module m ON gm.ModuleID = m.ID
WHERE c.ID = 1
ORDER BY g.GroupID, g.ID, m.Title  

So if you have a course and modules you can get all the groups for the course from the Course_Group table and get which group the modules belong to from the Group_Module table. Once you have the modules in their group(s) you can check the group's constraints AtLeastNSelections AND AtLeastNCredits walking up the Group.GroupID parentage chain until you get to Group.GroupID = NULL.

其他推荐答案

A very simple, first approach would be using just 4 tables:

TABLE Course 
( CourseId 
, Title 
, TotalCredits 
, ... other stuff
, PRIMARY KEY (CourseId)
) ;

TABLE Module 
( ModuleId 
, Description 
, Hours
, Credits
, ... other stuff
, PRIMARY KEY (ModuleId)
) ;

and the combinations allowed through these 2:

TABLE Course_Module 
( CourseID                 --- for this course
, ModuleID                 --- this module is allowed (optional or mandatory)
, PRIMARY KEY (CourseID, ModuleId)
, FOREIGN KEY (CourseId) 
    REFERENCES Course (CourseId)
, FOREIGN KEY (ModuleId)
    REFERENCES Module (ModuleId)
) ;

TABLE Course_MandatoryModule 
( CourseID                  --- for this course
, ModuleID                  --- this module is mandatory
, PRIMARY KEY (CourseID, ModuleId)
, FOREIGN KEY (CourseID, ModuleId)
    REFERENCES Course_Module (CourseID, ModuleId)
) ;

Now, if your allowed combinations of modules and courses is more complicated, as your description suggests, instead of the Course_Module and the Course_MandatoryModule tables you could define a complex hierarchical model:

Courses:

TABLE Course                        --- same as previous model
( CourseId 
, Title 
, TotalCredits 
, ... other stuff
, PRIMARY KEY (CourseId)
) ;

Modules and groups of (modules):

TABLE ModuleEntity                  --- the supertype for both
( ModuleEntityId                    --- modules and group of modules
, PRIMARY KEY (ModuleEntityId)
) ;

TABLE Module                        --- subtype
( ModuleId 
, Description 
, Hours
, Credits
, ... other stuff
, PRIMARY KEY (ModuleId)
, FOREIGN KEY (ModuleId) 
    REFERENCES ModuleEntity (ModuleEntityId)
) ;

TABLE ModuleGroup                  --- group of modules
( ModuleGroupId                    --- subtype of the supertype (entity)
, GroupDescription        
, PRIMARY KEY (ModuleGroupId)
, FOREIGN KEY (ModuleGroupId) 
    REFERENCES ModuleEntity (ModuleEntityId)
) ;

and relationship (module belongs to group):

TABLE Module_in_Group  
( ModuleEntityId               --- this module or group
, ModuleGroupId                --- is in this group
, PRIMARY KEY (ModuleEntityId, ModuleGroupID)
, FOREIGN KEY (ModuleEntityId)
    REFERENCES ModuleEntity (ModuleEntityId)
, FOREIGN KEY (ModuleGroupId)
    REFERENCES ModuleGroup (ModuleGroupId)
) ;

and (finally) course can have group of modules:

TABLE Course_ModuleGroup
( CourseId                 --- for this course
, ModuleGroupId            --- this module group is allowed
, PRIMARY KEY (CourseID, ModuleGroupId)
, FOREIGN KEY (CourseId) 
    REFERENCES Course (CourseId)
, FOREIGN KEY (ModuleGroupId)
    REFERENCES ModuleGroup (ModuleGroupId)
) ;

其他推荐答案

You can create a recursive table structure here, wherein Options reference their parent options.

  • The "main" options can then be identified by querying this table for all options with "null" parents.

  • The "and-or" relationships can be implemented by a separate "option-set" table, where the primary key is to an "option". The option-set table's with null self-references are the "root" point for defining a course's options. From that point, you will select option-set records with parent = root. This will be the first "level" of options. Some will be mandatory, some won't. To express that, you will have to have a boolean attribute on the option-set table as a flag. Thus each option-set is defined in terms of smaller option-sets. Of course, ultimately, once you get to the bottom, your option-set's will define an actual class at some point.

I would suggest that this can much more effectively be modelled in JSON or XML, since those data structures support hierarchies in a much more expressive manner.