1

I have the following tables: school, teacher, school_teacher and student.

school_teacher is a many-to-many junction table using an autogenerated PK and the FKs of school and teacher.

A student belongs to a teacher of a particular school and a teacher of a school has many students.

My question is whether I should create the foreign key in the student table to point to school_teacher table PK or to the two tables PKs (school, teacher) individually?

I believe it would be best for referential integrity to reference school_teacher to ensure that transaction records are only inserted if there is a relationship between the school and the teacher. In terms of best practices, what is the best option or the best way to address this problem? Is there any other possibility better than the ones I mention?

In general, is there a problem in which the primary key of an association table is a foreign key in another table?

This is simply an example. In a real case a student could have more than one teacher. It is to simplify the example.

Mr. Mars
  • 762
  • 1
  • 9
  • 39

1 Answers1

0

Student * ------ 1 School *------*Teacher

Will you check for student belongs to a school or many schools?

INDIAN2025
  • 191
  • 2
  • 6
  • Yes. In addition, a student must be associated with one or several teachers. Hence it is necessary to associate a student with a teacher. – Mr. Mars Feb 02 '20 at 11:29