mysql - 数据库设计——多对多表——如何最好地表示

Question

我需要表示教师与他们教授的科目之间的多对多关系。

对于实现，我想到了几个策略:

 teacher_name | subject_names
 bill           math, english, science, french
 sally        | chemistry, english, arts & crafts

我拒绝了这种策略，因为用逗号分隔值查询字段似乎效率不高，尤其是当我将它们用于搜索引擎、迭代等时……尽管我当然乐于听到对这种策略的辩护

 teacher_name | subject_name
 bill           math
 bill           english
 bill           science
 ...            ...
 sally          chemistry
 sally          english
 ...            ...

我最初认为这是一个更好的主意，但是当我查询有关老师的信息时，我得到的数据很难报告。也就是说，鲍勃的主题我有 5 行很好，但我还发现他住在 123 main st. 就不好了。和 123 大街和 123 条主要街道……我仍然认为总体上这是一个更好的主意，但也许存在更好的主意。

顺便说一句，我并没有真正使用 teacher_names 和 subject_names 来索引，我使用数字，但为了清楚起见，我这样画了

Answer 1

多对多关系的最佳解决方法是使用映射关系的称为连接表的第三个表。这是一个 good guide这解释得更详细一些，但基本上......

新表表将包含两列外键；一列中教师的唯一 ID(教师表的主键)，另一列中的主题唯一 ID(主题表的主键)(...当然还有联结表的列它有自己的唯一 ID)。

说这是你的教师表:

----------------------------
| ID   | Name  | Last_Name |
----------------------------
| 0001 | JOHN  | STEPHENS  |
----------------------------
| 0002 | BRUCE | WAYNE     |
----------------------------

这是您的主题表

-----------------------
| ID   | Subject_name |
-----------------------
| 0101 | MATH         |
-----------------------
| 0202 | BIOLOGY      |
-----------------------
| 0303 | ENGLISH      |
-----------------------

那么你需要一个这样的联结表:

TeacherSubject_JunctionTable:

--------------------------------
| ID | Teacher_ID | Subject_ID |
--------------------------------
| 01 | 0001       | 0101       |
--------------------------------
| 02 | 0001       | 0202       |
--------------------------------
| 03 | 0002       | 0101       |
--------------------------------
| 04 | 0002       | 0303       |
--------------------------------

现在，每当您需要获取教数学的教师列表(subject_id 0101)时，您可以简单地查询 junction 表；像

SELECT Teacher_ID FROM TeacherSubject_JunctionTable
WHERE Subject_ID = 0101;

或者反过来，如果您想获得老师教授的科目。