sql - 联结表的必要性是什么？

Question

我已经实现了以下存储关系拓扑的方法:
1.一般联结关系表:

Table: Relation

Columns: id parent_type parent_id parent_prop child_type child_id child_prop

大多数 sql 引擎通常无法在哪些连接上执行。
2.关系特定的联结表

Table: Class2Student

Columns: id parent_id parent_prop child_id child_prop

可以在哪些连接上执行。
3.将相关对象的列表/字符串映射存储在 text中两个双向对象上的字段。

Class: Class

Class properties: id name students

Table columns: id name students_keys

Rows: 1 "history" [{type:Basic_student,id:1},{type:Advanced_student,id:3}]

要通过 sql 引擎启用连接，可以编写一个自定义模块，如果 students_keys 的内容只是 [1,3]，这将变得更加容易。 ，即与显式 Student 的关系类型。
在以下情况下，问题如下:
我看不出连接表的意义是什么。例如，我看不到连接表声称可以缓解的以下参数实际存在的任何问题:

无法在逻辑上正确保存双向关系(例如
双向关系中没有数据孤儿或任何
与 keys 的关系字段，因为一个递归保存，一个可以强制执行
其他操作(删除、更新)很容易)

无法有效加入

我不会就您对最佳实践的个人意见或任何类似邪教的关于正常化的声明征求意见。
明确的问题如下:

在哪些情况下需要查询未通过查询拥有对象的 keys 提供的联结表？ field ？

在 sql 引擎提供的计算上下文中，连接表更可取的逻辑实现问题是什么？

关于 junction table 的唯一实现差异与 keys 相比字段如下:

在搜索以下性质的查询时，您需要匹配 keys具有自定义索引实现或其他一些合理实现的字段:

class_dao.search({students:advanced_student_3,name:"history"});

search for Classes that have a particular student and name "history"

与搜索联结表的索引列然后选择适当的类相反。
我一直无法确定为什么连接表在逻辑上出于任何原因在逻辑上更可取的答案。 我并不是说情况就是这样，或者我是否有某种宗教偏好，正如我实现了多种实现这一目标的方式所证明的那样。 我的问题是我不知道它们是什么。

Answer 1

在我看来，你有几个实体

CREATE TABLE StudentType
(
    Id Int PRIMARY KEY,
    Name NVarChar(50) 
);

INSERT StudentType VALUES
(
    (1, 'Basic'),
    (2, 'Advanced'),
    (3, 'SomeOtherCategory')
);

CREATE TABLE Student
(
    Id Int PRIMARY KEY,
    Name NVarChar(200),
    OtherAttributeCommonToAllStudents Int,
    Type Int,
    CONSTRAINT FK_Student_StudentType
        FOREIGN KEY (Type) REFERENCES StudentType(Id)
)

CREATE TABLE StudentAdvanced
(
    Id Int PRIMARY KEY,
    AdvancedOnlyAttribute Int,
    CONSTRIANT FK_StudentAdvanced_Student
        FOREIGN KEY (Id) REFERENCES Student(Id)
)

CREATE TABLE StudentSomeOtherCategory
(
    Id Int PRIMARY KEY,
    SomeOtherCategoryOnlyAttribute Int,
    CONSTRIANT FK_StudentSomeOtherCategory_Student
        FOREIGN KEY (Id) REFERENCES Student(Id)
)

所有学生共有的任何属性在 Student 上都有列。 table 。

具有额外属性的学生类型被添加到 StudentType table 。

每个额外的学生类型都会获得一个 Student<TypeName>表来存储它的特定属性。这些表与 Student 具有可选的一对一关系。 .

我认为您的“稻草人”连接表是 EAV 反模式的部分实现，唯一合理的情况是当您不知道需要建模哪些属性时，即您的数据将完全是非结构化的.当这是一个真正的要求时，关系数据库开始看起来不太理想。在这些情况下，可以考虑使用 NOSQL/Document 数据库替代方案。

联结表在以下场景中很有用。

假设我们向模型添加了一个 Class 实体。

CREATE TABLE Class
(
    Id Int PRIMARY KEY,
    ...
)

可以想象，我们希望存储学生和类(class)之间的多对多关系。

CREATE TABLE Registration
(
    Id Int PRIMARY KEY,
    StudentId Int,
    ClassId Int,
    CONSTRAINT FK_Registration_Student
        FOREIGN KEY (StudentId) REFERENCES Student(Id),
    CONSTRAINT FK_Registration_Class
        FOREIGN KEY (ClassId) REFERENCES Class(Id)
)

该实体将是存储与学生注册类(class)特别相关的属性的正确位置，例如可能是完成标志。其他数据自然会与这个路口相关，比如类(class)特定的出勤记录或成绩历史。

如果你不联系 Class和 Student这样，你将如何选择一个类(class)的所有学生和一个学生阅读的所有类(class)。性能方面，这很容易通过关键列上的索引进行优化。

当存在没有任何属性的多对多关系时，我在逻辑上同意，联结表不需要存在。但是，在关系数据库中，联结表仍然是一种有用的物理实现，也许像这样，

CREATE TABLE StudentClass
(
    StudentId Int,
    ClassId Int,
    CONSTRAINT PK_StudentClass PRIMARY KEY (ClassId, StudentId),
    CONSTRAINT FK_Registration_Student
        FOREIGN KEY (StudentId) REFERENCES Student(Id),
    CONSTRAINT FK_Registration_Class
        FOREIGN KEY (ClassId) REFERENCES Class(Id)
)

这允许简单的查询，如

// students in a class?
SELECT StudentId
FROM StudentClass
WHERE ClassId = @classId

// classes read by a student?
SELECT ClassId
FROM StudentClass
WHERE StudentId = @studentId

此外，这提供了一种从任一方面部分或完全管理关系的简单方法，这对于关系数据库开发人员来说是熟悉的，并且可以被查询优化器发现。