在SQL中表示"X of Y“

Question

在我的数据库中，我有很多必修课。有些是选修课。但是，还有第三种课程:您必须从中选择X门课程的列表。每个学习项目的列表(和数字X)是不同的。你将如何在关系上表现这种关系？

Answer 1

我发现有趣的是，被接受的答案是，“没有办法在关系上表示‘Y的X’”，而这实际上就是问题所要问的。在我看来，“X of Y”确实可以使用SQL建模(并在很大程度上强制执行)，下面是一种建议的方法：

示例场景:选修“法语”课程的学生必须从总共三个可能的组件(y)中选择两个组件(x)。

CREATE TABLE Components
(
 component_name VARCHAR(100) NOT NULL, 
 UNIQUE (component_name)
);

INSERT INTO Components (component_name) VALUES 
('Oral'), 
('Writing'), 
('Vocab'), 
('Databases');

显然，“数据库”不属于法语课程，所以我们需要一个供课程设计者建模的表[这些表有许多相关的候选关键字，所以为了清楚起见，我将在CREATE TABLE语句的“底部”定义它们)：

CREATE TABLE XofYCourses
(
 course_name VARCHAR(100) NOT NULL, 
 x_components_choice_tally INTEGER NOT NULL 
    CHECK (x_components_choice_tally > 0), 
 y_components_tally INTEGER NOT NULL
    CHECK (y_components_tally > 0), 
 CHECK (x_components_choice_tally < y_components_tally),
 UNIQUE (course_name), 
 UNIQUE (course_name, y_components_tally), 
 UNIQUE (course_name, x_components_choice_tally)
);


INSERT INTO XofYCourses (course_name, y_components_tally, 
x_components_choice_tally) VALUES 
('French', 2, 3);

上面的内容允许我们对法语课程的“2/3”属性进行建模。现在我们需要一个表来模拟该课程的三个可能的组成部分：

CREATE TABLE XofYCourseComponents
(
 course_name VARCHAR(100) NOT NULL, 
 y_components_tally INTEGER NOT NULL, 
 FOREIGN KEY (course_name, y_components_tally)
    REFERENCES XofYCourses (course_name, y_components_tally), 
 component_sequence INTEGER NOT NULL
    CHECK (component_sequence > 0), 
 component_name VARCHAR(100) NOT NULL 
    REFERENCES Components (component_name), 
 CHECK (component_sequence <= y_components_tally), 
 UNIQUE (course_name, component_sequence), 
 UNIQUE (course_name, component_name) 
);

INSERT INTO XofYCourseComponents (course_name, 
component_sequence, y_components_tally, component_name) 
VALUES 
('French', 1, 3, 'Oral'), 
('French', 2, 3, 'Writing'), 
('French', 3, 3, 'Vocab');

现在开始注册。比利想上法语课。

CREATE TABLE Students
(
 student_name VARCHAR(20) NOT NULL, 
 UNIQUE (student_name)
);

INSERT INTO Students (student_name) VALUES ('Billy');

...and选择“口语”和“词汇”：

CREATE TABLE XofYCourseComponentChoices
(
 student_name VARCHAR(20) NOT NULL
    REFERENCES Students (student_name), 
 course_name VARCHAR(100) NOT NULL, 
 x_components_choice_tally INTEGER NOT NULL, 
 FOREIGN KEY (course_name, x_components_choice_tally)
    REFERENCES XofYCourses (course_name, x_components_choice_tally), 
 component_name VARCHAR(100) NOT NULL, 
 FOREIGN KEY (course_name, component_name)
    REFERENCES XofYCourseComponents (course_name, component_name), 
 x_component_sequence INTEGER NOT NULL
    CHECK (x_component_sequence > 0), 
 CHECK (x_component_sequence <= x_components_choice_tally), 
 UNIQUE (student_name, course_name, component_name), 
 UNIQUE (student_name, course_name, x_component_sequence)
);

INSERT INTO XofYCourseComponentChoices (student_name, course_name, 
component_name, x_component_sequence, x_components_choice_tally)
VALUES
('Billy', 'French', 'Oral', 1, 2), 
('Billy', 'French', 'Vocab', 2, 2);

上面的结构很好地实施了最大值，即法语课程不超过三个组成部分，每个学生不超过两个选择。

然而，它不能做的是确保精确的数量，例如，比利不会只选择一个组件。标准的SQL有这个问题的解决方案，例如支持子查询的CHECK约束(例如，计算Billy总共有两行...)和DEFERRABLE约束(...but将计数延迟到提交事务时为止)。有一个“多任务”功能会更好。但是，大多数SQL产品都没有这些特性。

缺乏对完整解决方案的支持是否意味着我们什么都不做，只相信应用程序不会写入无效数据？当然不是!

一个好的过渡方法是从基表中撤销特权，并提供助手存储过程，例如，一个注册学生的过程，它将他们选择的课程组件作为参数:计数在INSERT之后完成，如果它违反了数据规则(例如，法语不到两个)，那么事务将被回滚并返回错误。

Answer 2

这里你需要3张表: StudyPrograms，课程和组件。组件表示组成每个StudyProgram的课程，是课程和StudyPrograms之间的连接表。

每个组件记录可以包含一个字段，指示该课程是否是StudyProgram的必修部分。还可以包括一个字段，以指示课程是否为可选列表之一。

没有办法在关系上表示“X of Y”，您将需要在存储过程中使用一些逻辑来确保此业务规则得到遵守(或者可能在数据访问代码层，这取决于您希望如何组织应用程序)。

Answer 3

您有两个选择:您可以对更接近现实的数据进行建模，其中一些是单课程需求，而其他是Y课程需求中的X，或者您可以将所有需求建模为Y中的X，其中单课程需求是"1中的1“需求。

我会推荐这样的东西：

Course
---------------
CourseID
Description
...

Program
---------------
ProgramID
Description
...

CourseGroup
---------------
CourseGroupID
CourseID

ProgramCourseGroup
---------------
ProgramID
CourseGroupID
RequiredCourses

Course和Program是两个顶层表。它们分别定义了所有课程和课程的简单列表，两者之间没有任何关系。

CourseGroup定义了一组课程。这与Course相关，但与其他表无关。

ProgramCourseGroup将课程组与方案相关联。程序指示需要特定的课程组，然后RequiredCourses指示必须从该组中选择多少课程才能满足要求。

例如，假设你有一个名为“篮子编织”的程序，它需要：

• 入门介绍
• Basic weaving

以及以下四门课程中的两门：

复活节baskets

• Handbaskets

• Picnic baskets

• SCUBA Diving

您的数据将如下所示：

Course
------------------------------------
CourseID    Description
1           Intro to baskets
2           Basic weaving techniques
3           Easter baskets
4           Handbaskets
5           Picnic baskets
6           SCUBA Diving

Program
--------------------------
ProgramID   Description
1           Basket Weaving

CourseGroup
--------------------------
CourseGroupID  CourseID
1              1
2              2
3              3
3              4
3              5
3              6

ProgramCourseGroup
-----------------------------------------
ProgramID  CourseGroupID  RequiredCourses
1          1              1
1          2              1
1          3              2