CODEWITHSUNDEEP
mysqldatabasedatabase-designdata-structures
Ben
Ben

Reputation: 25807

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

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

Upvotes: 0

Views: 726

Answers (3)

nvogel
nvogel

Reputation: 25526

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.

Upvotes: 1

Mchl
Mchl

Reputation: 62369

make 2 fields primary key

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

Upvotes: 3

Guido Hendriks
Guido Hendriks

Reputation: 5776

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.

Upvotes: 1

Related Questions