sql - 为 1 对 N 映射执行连接

Question

假设我有这些模型:

Student:
    string name
    list<Subject> enrolledCourses

Subject:
    string name
    integer credits

我可以将其存储在数据库中的方式是:

CREATE TABLE student(
    id serial PRIMARY KEY,
    student_subject_mapping int NOT NULL,
    name character varying
);

CREATE TABLE student_subject_mapping(
    id serial PRIMARY KEY,
    student_id int,
    subject_id int
);

CREATE TABLE subject(
    id serial PRIMARY KEY,
    name character varying,
    credits int
);

要获取学生(伪代码)，我可以这样做:

var student = SELECT * FROM student WHERE id = ?id;
var subjects = SELECT * FROM subject WHERE id IN (SELECT * FROM student_subject_mapping WHERE student_id = ?student['id']);

make_student(student, subjects)

或者，我可以这样做:

var student = SELECT * FROM student INNER JOIN student_subject_mapping
                  ON student.id = student_subject_mapping.student_id INNER JOIN subject
                  ON subject.id = student_subject_mapping.subject_id;

以上将返回以下类型的行(为简洁起见省略了一些列):

+--------------+-------------------+
| student.name | subject.name      |
+--------------+-------------------+
| Rohan        | Physics           |
| Rohan        | Biology           |
| Rohan        | Computer Science  |
+--------------+-------------------+

在这种情况下，我可以从任何行中获取 student 部分的信息，并通过遍历其他行来获取主题。这听起来可能有点复杂，但对我来说真的很简单，因为我使用 jooq:

Result<Record> rows = query.execute();
if (rows.size() > 0) {
    StudentRecord student = rows.get(0).into(Tables.STUDENT);
}

List<SubjectRecord> subjects = rows.stream()
                                   .map(x -> x.into(Tables.SUBJECT))
                                   .collect(Collectors.toList());

但我不确定这样做是否正确。事情变得更加复杂，如果 subject 是这样的:

Subject:
    string name
    list<Department> offeringDepartments

我知道这是一个不好的例子，因为 subject 可能不应该存储有关提供部门的信息，但请一起玩，因为我相信您可以看到模型可能具有多层嵌套的位置.在那种情况下，不仅我的查询变得复杂，而且我有以下类型的行:

+--------------+-------------------+------------------+
| student.name | subject.name      | departments.name |
+--------------+-------------------+------------------+
| Rohan        | Physics           | Science          |
| Rohan        | Biology           | Science          |
| Rohan        | Computer Science  | EECS             |
| Rohan        | Computer Science  | IT               |
+--------------+-------------------+------------------+

在前面的例子中，我可以从第一行本身获取有关 student 的信息，但我无法知道获取有关主题行的信息的边界是 [0, 1] ; [1, 2] 和 [2, 4](除了一些额外的 SQL 魔法)。

那么，您会推荐我采用哪种方法？还是有第三种更好的替代方案？

Answer 1

我认为在单个查询中同时检索学生和主题是完全合适的。一些框架(ActiveRecord)确实会让这样的工作变得非常容易。用例通常是您从一个表和一堆“父”表中进行选择的地方，但是对于单个子关系这样做没有错。

它会变得棘手的是多个子关系，例如，如果您要检索产品及其关联的子订单和库存水平，其中 100 个订单和 10 个库存水平等于 1,000 行，其中包含大量冗余数据(在有些圈出“鸿沟陷阱”)。在这种情况下，您可能希望使用两个或三个查询来返回结果。