sql - RDB 中的子关系

Question

在典型的 RDB 中，我可以描述与外键的关系。但是，我不确定如何有效地描述组内关系。我知道这很难理解，所以让我描述一下这个场景。

我想做一个成绩册服务，教师可以注册和创建类(class)，学生可以注册和注册类(class)。

CREATE TABLE teachers (
   id BIGSERIAL PRIMARY KEY,
   name VARCHAR(255),
   ...
)

CREATE TABLE students (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255),
  ...
)

CREATE TABLE classes (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255),
  teacher_id BIGINT REFERENCES teachers(id)
)

CREATE TABLE enrollments (
  student_id BIGINT REFERENCES students(id),
  class_id BIGINT REFERENCES classes(id),
  PRIMARY KEY (student_id, class_id)
)

在每堂课中，老师可以创建作业，每个学生都会收到作业的分数。

CREATE TABLE assignments (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255),
  class_id BIGINT REFERENCES class(id)
  ...
)

现在，我将描述一个属于学生的分数，以及一个带有外键的作业。

CREATE TABLE scores (
  id BIGSERIAL PRIMARY KEY,
  score INT,
  student_id BIGINT REFERENCES student(id),
  assignment_id BIGINT REFERENCES assignments(id)
)

但是，没有什么可以阻止我为未参加该类(class)的学生创建分数。换句话说，分数必须伴随着相应的注册(相同的学生 ID)。我说这是子关系的原因是因为分数的关系应该在一个类中的局部范围内。

我如何使用 RDB 强制执行此类限制？

更新:

根据@Joel Brown 的建议，这就是我想出的。

CREATE TABLE enrollments (
  class_id BIGINT REFERENCES classes(id),
  student_id BIGINT REFERENCES students(id),
  PRIMARY KEY (class_id, student_id)
);

CREATE TABLE assignments (
  id BIGSERIAL,
  class_id BIGINT REFERENCES classes(id),
  name VARCHAR(255) NOT NULL,
  max_possible_score INT NOT NULL,
  PRIMARY KEY (id, class_id)
);

CREATE TABLE scores (
  class_id BIGINT,
  student_id BIGINT,
  assignment_id BIGINT,
  PRIMARY KEY (assignment_id, class_id, student_id),
  FOREIGN KEY (class_id, student_id) REFERENCES enrollments(class_id, student_id),
  FOREIGN KEY (assignment_id, class_id) REFERENCES assignments(id, class_id)
);

Answer 1

如果您想确保学生无法在他们未注册的类(class)的作业中获得 SCORE，您有两种选择:

1. 使用应用程序逻辑
2. 使用声明性参照完整性

有些人可能会争辩说您现在拥有的数据模型工作得很好，但它需要一些应用程序逻辑来对 SCORE 的创建执行健全性检查，以确保学生应该拥​​有那个分数。

如果您想确保在不使用应用程序逻辑的情况下不会发生这种情况，那么您必须更改您的数据模型。您当前的数据模型如下所示:

您可以将数据模型更改为如下所示:

注意以下几点:

• Assignment 由其指向 CLASS 的外键部分标识。这会将类 ID 下拉到分配中，以便 SCORE 在其主键中依次引用它。
• SCORE 与ENROLLMENT 直接相关，而不是直接与STUDENT 相关。这是有道理的，因为注册是属于某个类(class)的学生的记录 - 因此它更接近地模拟您所关心的实现的业务规则。
• SCORE 的主键由 ENROLLMENT(包括学号)和 ASSIGNMENT(包括类(class) ID)的外键组成).
• 这意味着SCORE 的主键将是类(class)、作业和学生的组合。

如果您确保在 SCORE 表中只保留一份类(class) ID，那么学生将不可能在作业中获得分数，除非该学生也是就读于分配该作业的类(class)。