many to many table - 1 seperate field primary key or 2 existed fileds primary key (Example inside)

Question

Hi,

I have 2 tables with many to many relation:

student 
(
id int(11) NOT NULL, 
name varchar(255), 
primary key(id)
);

teacher
(
id int(11) NOT NULL, 
name varchar(255), 
primary key(id)
);

and I should do 3 table - student_has_teacher

1. option add id separate field primary key

   student_has_teacher ( id int(11) NOT NULL, teacher_id int(11), student_id int(11) primary key(id) );

2. option make 2 fields primary key

   student_has_teacher ( teacher_id int(11), student_id int(11), primary key(teacher_id,student_id), foreign key(teacher_id) references teacher(id), foreign key(student_id) references student(id) );

What is better option and why?

Thanks

Answer 1

That depends. If you'll need to relate something to a row in student_has_teacher (weird table-name imo, I'd suggest student_teacher) an id field would be nice. If you're not, the two fields will do fine to.

Answer 2

make 2 fields primary key

Because they fulfill the definition of what a primary key is. They allow to unambiguously indicate the row.

Answer 3

The two options are not equivalent. In option 1 there can be multiple pairings of each teacher and student. In option 2 only 1 row is permitted for each combination of teacher and student.

There is another difference as well. In 1 the student and teacher are nullable. In 2 they aren't.

On the information given I don't see why a teacher would need to be paired with the same student more than once. So as a guess I'd say that 2 was more appropriate, but it all depends on the business requirements and you haven't really given much information to say either way.