mysql - 创建一个属于多于 2 个模型的关系

Question

我这里有一个独特的情况，我有 3 个模型:Degree、Student 和 Consultant。

学生和顾问之间存在多对多关系。
学位和学生之间也存在多对多关系。

对于上述关系中的每一个，我可以有 2 个数据透视表，
但这里重要的是:特定学位的学生可以有 1 个顾问。
换句话说，创建 2 个单独的数据透视表，并没有给我关于特定用户的哪个顾问的信息。

我可以在这里想到 2 个解决方案:

第一个是创建一个包含 3 个外键的数据透视表，例如:

student_id  |  degree_id  |  consultant_id

这样我就可以知道谁是学生特定学位的顾问。

但是，这种方式是构建数据库的标准方式吗？
另外，我应该为此创建一个 Pivot 模型吗？
代码片段会是什么样子？

我能想到的另一种方法是创建 2 个单独的数据透视表，并在另一个数据透视表中引用一个数据透视表的 id。
所以这看起来像这样:
degree_student: student_id | degree_id
consultant_student: student_id | consultant_id | **degree_student_id**

同样，这是标准方法吗？
我应该为此创建一个 Pivot 模型吗？

对于最后的讨论，我会接受任何其他更好(/标准)的解决方案建议。

注意:我检查了here和 here但不能满足我的要求。

Answer 1

a student in a specific degree can have 1 consultant

您的第一个设计并没有强制执行这一点。 student_id 可能不同于 degree_student 的 student_id。 degree_student_id 可能有多个 consultant_id。此外，consultant_id 必须可以为 null。

类似于您的第一个设计:

--     student student_id is in degree degree_id witout a consultant  
-- AND consultant_id IS NULL  
-- OR  student student_id is in degree degree_id with consultant consultant_id  
consultant_degree_student: degree_id  |  student_id  |  consultant_id  
(composite) PK (primary key) (degree_id, student_id)  
nullable consultant_id  

类似于您的第二个设计(具有隐式 degree_student_id 字段):

-- degree_student_id identifies student student_id being in degree  degree_id  
degree_student: degree_student_id  |  degree_id  |  student_id  

--     degree_student_id identifies student student_id being in degree degree_id  
-- AND student student_id is in degree degree_id with consultant consultant_id  
degree_student_consultant: degree_student_id  |  consultant_id  

类似于没有额外 id 且没有可空字段的那些:

-- student student_id is in degree degree_id  
degree_student: degree_id  |  student_id  

-- student student_id is in degree degree_id with consultant consultant_id
consultant_degree_student: degree_id  |  student_id  |  consultant_id  
PK (primary key) (degree_id, student_id)  

复合 PK 意味着给定的 (degree_id, student_id) 对只能有一个记录，因此 consultant_id 每对是单值的。归一化恰好告诉我们这些表没有它可以解决的问题。

您必须在逻辑上权衡表的数量、ID 的数量、空值和表含义的简单性/复杂性(谓词)，以及其他物理性能。典型的 SQL 设计将使用第一个。您需要查找并遵循已发布的信息建模和关系数据库设计引用。 (PS 根本不是“独特的情况”。)