以多对多关联将第三个表链接到桥接表

Question

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

下面是我如何设置我的表：

我在一个名为teachers的表和一个名为instruments的表之间存在多对多的关系。然后我有一个桥接表连接两者。我想要将另一个表与桥接表相关联。指的是乐器/教师组合。该表将有3行，指定教师可以教授的游戏级别(即。初学者、中级、高级)。似乎我应该设置桥接表来拥有teacher_id、instrument_id和level_id，但我不知道这是不是传统的方法。

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

Answer 1

如果这就是你的数据所需要的，而且听起来确实如此，那么就去做吧。

基本上，正如您所提到的，您将拥有一个由三部分组成的密钥：

• teacher_id
• instrument_id
• level_id

这意味着一个老师可以教初学者和高级吉他，钢琴在中级和初学者，另一个老师可以教钢琴在所有三个水平和双簧管在初学者…听起来不错。

顺便说一下，桥接表也称为intersect或intersection表。

Answer 2

马特·芬威克+1。我要补充的是，您需要对外键约束稍加小心。您实际上有两个选项，根据您对主键的选择，这两个选项最终可能看起来非常相似。

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

选项二是:保持TEACHER和INSTRUMENT之间的简单交集(让我们称其为TEACHER_INSTRUMENT，尽管这没有想象力)，并添加一个子子表来定义可以教授的级别。这个子子表(让我们称它为SKILL)有一个level_id和一个指向TEACHER_INSTRUMENT的外键。如果TEACHER_INSTRUMENT的主键是teacher_id和instrument_id的组合，那么SKILL表将具有与选项一相同的三列。此选项的不同之处在哪里？来自SKILL的外键约束必须指向交叉表，而不是指向TEACHER和INSTRUMENT。

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

另一方面，如果您选择第二个选项，那么在谁可以使用什么以及他们可以教得多好之间，您具有分离关注的优势。

您要避免的是有一个只包含教师/乐器关系的表，然后有第二个表(独立地)重复该关系，但添加了技能级别的详细信息。如果你这样做，那么你就会冒着这两件事不同步的风险。