多对多关系：关联表与单个外键？
给定：一个病人有多个医生，医生有多个病人。
以下两个模式有什么区别？
- 选项 1：关联表
- 患者[id, data1]
- 医生[id, data2]
- Patient_Doctor[患者 ID、医生 ID]
- 选项 2：单个外键
- 患者[id, data1, doctor_id]
- 医生[id, data2]
我唯一能想到的是，选项 2 要求您多次复制 data1，如果 data1 很大，性能将会受到影响。这是正确的吗？
Given: A patient has many doctors and doctors have multiple patients.
What is the difference between the following two schemas?
- Option 1: Association Table
- Patient[id, data1]
- Doctor[id, data2]
- Patient_Doctor[patient_id, doctor_id]
- Option 2: A single foreign key
- Patient[id, data1, doctor_id]
- Doctor[id, data2]
The only thing I can think of is that option 2 requires you to duplicate data1 multiple times and if data1 is large performance will suffer. Is that correct?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不，那是不正确的。 “选项 2”（其中病人.id 可能是主键）会阻止您为每位患者插入多于一行。所以每个病人只能有一位医生。这在一般情况下不起作用：初级保健医生可能会将患者转介给过敏症专科医生、胃肠病学家、肿瘤学家等。
为了好玩，考虑一下医生自己也有医生的事实。
No, that's not correct. "Option 2", in which patient.id is presumably the primary key, prevents you from inserting more than one row for each patient. So each patient can have one and only one doctor. That doesn't work in the general case: a primary care doctor might refer a patient to an allergist, a gastroenterologist, an oncologist, and so on.
For fun, consider the fact that doctors themselves have doctors.
确切地说，选项 2 是一对多关系。因此，每个患者都必须重复多次，这不利于数据库规范化。
您的情况是多对多关系，这就是您需要关系表的原因。
选项2不是性能问题，而是设计问题，一个病人可以有很多与他相关但你不想重复的字段（医疗保险、地址、电话等......）
Exactly, option 2 is a 1 to many relationship. So each patient will have to duplicated multiple times which goes against database normalization.
Your case, is many to many relationship and thats why you need the relation table.
Option 2 is not a question of performance, but of design, a patient can have a lot of fields related to him that you dont want to duplicate (medicare, adress, phone, etc...)