mysql - 将第三个表链接到多对多关联中的桥接表

Question

设计这个数据库的正确方法是什么？

这是我设置表格的方式:

我在一个名为 teachers 的表和一个名为 instruments 的表之间建立了多对多关系。然后我有一个连接两者的桥接表。我想将另一个表与 BRIDGE 表相关联。意思是乐器/老师的组合。该表将有 3 行指定教师可以教授的演奏水平(即初级、中级、高级)。似乎我应该将桥接表设置为具有 teacher_id、instrument_id 和 level_id，但我不知道这是否是常规方法。

我正在使用 mysql 和 cakephp，但我没有在文档中找到任何关于 HABTM 关联的关于在桥接表中有一个额外字段的内容。只是想确保我做的正确。

Answer 1

+1 代表马特·芬威克。我想补充一点，你要小心你的外键约束。您基本上有两个选项，这两个选项最终看起来可能非常相似，具体取决于您选择的主键。

选项一是:忘记 TEACHER 和 INSTRUMENT 之间的简单交集，将其替换为包含 teacher_id< 的复杂交集、instrument_id 和 level_id。所有这三列都是该交集表的(复合)主键。在此选项中，您在 teacher_id 和 instrument_id(以及 level_id 上定义了外键约束，如果这实际上是 的外键LEVEL 表，而不仅仅是整数或字符串代码)。

选项二是:保留 TEACHER 和 INSTRUMENT 之间的简单交集(我们称它为 TEACHER_INSTRUMENT，即使这是缺乏想象力的)并添加一个子表来定义可以教授的级别。这个子表(我们称它为 SKILL)有一个 level_id 和一个指向 TEACHER_INSTRUMENT 的外键。如果 TEACHER_INSTRUMENT 的主键是 teacher_id 和 instrument_id 的组合，则 SKILL 表将具有与选项一中相同的三列。这个选项有什么不同？ 外键约束来自 SKILL 必须是交集表，而不是 TEACHER 和 仪器。

为什么这很重要？如果您选择选项一，您可能需要一些额外的查询逻辑来获得完全填充的技能网格，因为没有参照完整性约束，您可以定义它将确保为每个教师/仪器组合填充所有技能水平。

另一方面，如果您选择选项二，则您的优势在于可以分离谁可以使用什么以及他们可以教授多少。

您要避免的是让一个表只包含教师/仪器关系，然后再有一个表(独立地)重复该关系但添加技能水平详细信息。如果您这样做，那么您将面临这两件事不同步的风险。