数据库设计/规范化结构需要包含 AND、OR、可选元素及其关系

Question

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

基本上，一门课程可以有必修部分、一组可选模块、一个选项部分，并且每个部分中都可以有包含模块之间 AND 或 OR 的选择。

---

简单示例：
一门 60 学分的课程有一些必修模块，总共 40 学分。剩下 20 个学分可供从可选模块组中选择。 （模块本身可以持有不同数量的学分）。有效地; ('强制模块 1' AND '强制模块 2'... AND'强制模块 N') AND (来自 '可选模块' 的 40 个学分),

ANDs & OR：
当我在上面说模块时，它可以是单个模块，也可以是“模块 x 或模块 Y”，即在强制部分中。 （这些模块显然必须具有相同的信用权重）。 或者在可选部分中可能有单个模块，甚至其中一个选项可能类似于“模块 x AND 模块 y”。

选项：
学生可能必须选修必修模块以及n个选项之一，其中可能包含也可能不包含 AND、OR 以及必修的 & 选项。可选部分；即“选项”具有整个课程模块选择的所有属性。选项部分将与其他部分（如强制或可选）进行 AND 或 OR 运算；即强制模块“加上以下选项之一”。实际上，选项部分只是“选项 1”或“选项 2”...或“选项 N”。

---

问题是当操作数可能是另一个 AND/OR 运算或单个模块时，如何存储所有 AND 和 OR 关系，并跟踪每个选择允许的积分数量；例如“来自以下内容的 20 学分：”（可选模块组）。

Answer 1

一个非常简单的第一种方法是仅使用 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)
) ;

Answer 2

该设计相当简单，您只需要一个带有约束的递归“组”表。

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  

因此，如果您有一门课程和模块，您可以从 Course_Group 表中获取课程的所有组，并从 Group_Module 表中获取模块所属的组。一旦您将模块放入组中，您就可以沿着 Group.GroupID 父系链检查组的约束 AtLeastNSelections 和 AtLeastNCredits，直到到达 Group.GroupID = NULL。

Answer 3

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

• 然后可以通过查询此表中具有“空”父项的所有选项来识别“主”选项。

• “与或”关系可以通过单独的“选项集”表来实现，其中主键是“选项”。具有空自引用的选项集表是定义课程选项的“根”点。从那时起，您将选择父=根的选项集记录。这将是选项的第一个“级别”。有些是强制性的，有些则不是。为了表达这一点，您必须在选项集表上有一个布尔属性作为标志。因此，每个选项集都是根据更小的选项集来定义的。当然，最终，一旦你深入了解，你的选项集将在某个时候定义一个实际的类。

我建议可以更有效地用 JSON 或 XML 进行建模，因为这些数据结构以更具表现力的方式支持层次结构。

Answer 4

您可能可以这样做：

TABLE course_definition (
    ID int,
    num_mandatory_sections int,
    mandatory_hours int,
    num_optional_modules int,
    optional_hours int,
);

TABLE modules (
    ID int,
    Description varchar(max),
    hours int,
    ....
);

TABLE course (
    Course_ID int FOREIGN KEY (course_definition.id),
    module_id int FOREIGN KEY (modules.id)
);

TABLE course_module_relationship (
     Course_ID int FOREIGN KEY (course_definition.id),
     module_ID int foreign key (modules.id),
     Requirement_flag ENUM ("MANDATORY", "OPTIONAL")
);

TABLE Student_to_course (
     Student_ID int,
     Course_ID int foreign key (course_definition.id)
);

TABLE Student_to_module (
     Student_ID int,
     Module_ID int FOREIGN KEY (module.id)
);

如果您确实需要能够创建组模块，即从多个其他模块创建的单个模块，那么表 module 将需要有一个标志字段：

group_module boolean

以及下表应该添加：

TABLE module_groupings (
    group_module_ID int foreign key (module.id)
    dependant_module_id int foreign key (module.id)
);

这更多是伪代码，但你明白了。表course和course_module_relationship将没有键并存储您的关系，因为据我了解这个问题，它们可以是多对多的。因此，基本上，读取处理选择的代码必须检查这是否符合course_definition的标准。

如果必修部分与课程是一对一的关系，您可以将必修部分分成单独的表，但您必须更彻底地分析数据。

Answer 5

使用和/或（如您的系统）的生产质量系统可以免费查看，它是 entlib 5.0 安全块。 http://entlib.codeplex.com/

每个规则均按名称检索到获得完整表达。图案和样式实践团队为表达式创建了自己的简短DSL，以避免使 xml 结构/db 结构复杂化。

这是实验练习 ex02 app.config 中的内容。要将规则存储在数据库中，您需要实现自定义的 AuthorizationRuleProvider。

R：=角色名； U:= username

  <securityConfiguration defaultAuthorizationInstance="RuleProvider"
defaultSecurityCacheInstance="">
<authorizationProviders>
  <add type="Microsoft.Practices.EnterpriseLibrary.Security.AuthorizationRuleProvider, Microsoft.Practices.EnterpriseLibrary.Security"
    name="RuleProvider">
    <rules>
      <add expression="R:Employee OR R:Developer OR R:Manager" name="Raise Bug" />
      <add expression="R:Manager" name="Assign Bug" />
      <add expression="R:Developer OR R:Manager" name="Resolve Bug" />
    </rules>
  </add>
</authorizationProviders>

dev 用法

    public static AssignBug Create()
    {
        // TODO: Check Authorization
        if (!SecurityHelper.Authorized(AuthRule.Assign))
        {
            throw new SecurityException();
        }

        return new AssignBug();
    }

虽然不是立即答案，但我认为这提供了一个很好的示例，说明如何实现基于规则表达式的系统。