设计相当直观,您只需要一个带有约束条件的递归“组”表。
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表中获取模块所属的组。一旦您将模块放入它们的组中,您可以检查组的约束条件AtLeastNSelections和AtLeastNCredits,沿着Group.GroupID的父级链向上走,直到您到达Group.GroupID = NULL。