Many-to-many relationships: association table versus a single foreign key?

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]
  • Pat开发者_运维百科ient_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?

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?

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.

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...)