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

Question

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

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

id is the worst name for an id field. Please learn to use studentid, teacherid, etc. It makes a huge difference when you get to complex queries and reporting. — HLGEM, Aug 17 '10 at 15:20
Duplicate of http://stackoverflow.com/questions/963809/should-i-use-composite-primary-keys-or-not, http://stackoverflow.com/questions/159087/composite-primary-keys-versus-unique-object-id-field, http://stackoverflow.com/questions/1383062/composite-primary-key-to-use-or-not-to-use ? — Abe Miessler, Aug 17 '10 at 17:01

score 3 · Answer 1 · answered Aug 17 '10 at 14:16

3

make 2 fields primary key

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

answered Aug 17 '10 at 14:16

Mchl

61,444
9
118
120

score 1 · Answer 2 · answered Aug 17 '10 at 14:15

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.

answered Aug 17 '10 at 14:15

Guido Hendriks

5,706
3
27
37

score 1 · Accepted Answer · answered Aug 17 '10 at 14:48

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.

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

3 Answers3