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

问题描述

我想在（MySql）数据库中存储大学课程的详细信息，但我不知道如何保持模块和选择之间的关系。

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.

基本上，一个课程可以有强制性部分，一组可选模块，一个选项部分，每个部分中可以有包含模块之间的AND或OR的选择。

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.

简单示例：

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。

选项：

学生可能必须参加必需的模块加上 n 选项之一，可能不包含ANDs，ORs和mandatory&可选部分;即选项具有所有课程模块选择的所有属性。选项部分将与其他部分进行AND'd或OR'，如强制或可选;即强制性模块加上以下选项之一。有效的选项部分只是'选项1'或'选项2'...或'选项N'。

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'.

问题是当操作数可能是另一个AND / OR操作时，如何存储所有的AND和OR关系或单个模块，并记录每个选择所允许的信用量;例如

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

示例结构为

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个学分。

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  

所以如果你有一个课程和模块，你可以从Course_Group表中获得课程的所有组，并从模块中获取模块所属的组Group_Module表。一旦您在其组中有模块，您可以检查组的约束AtLeastNSelections和AtLeastNCredits走上Group.GroupID父级链，直到您到Group.GroupID = NULL。

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.

这篇关于数据库设计/规范化结构需要包含AND，OR，可选元素及其关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！