Database: best way to *flag a specific link* within a many-to-many relationship?

41 Views Asked by At

Take for example a classical student and teacher many-to-many relationship:

Table "student": id, name
Table "teacher": id, name
Table "student_teacher": id, student_id, teacher_id, relation_quality

I need to add the concept that a student has a "referent teacher": in other words, to "flag" one relation with teachers as specific, for every student.

I see three possible methods:

  1. add a one-to-many relation between teacher and students
  2. add one-to-one relationship between student and student_teacher
  3. add field "is_referent" to "student_teacher", setting it to True when appropriate
  • With option 1, the link table student_teacher is "bypassed", which in my case would require some 'constraint' against setting a teacher as referent without any relation between student and teacher through the many-to-many path. Generating SQL queries to get the corresponding "relation_quality" does not appear too hard (we would have the two required FKs at hand), but I am not sure that it would not complicate my existing Django templates;
  • With option 2 (which I am currently using), the 'constraint' to ensure is again that a student cannot connect to a 'referent' through an entry of the link table to which she/he is not related (i.e. a link used for another student). Maybe more importantly, it "looks more complex" within the relations diagram than the other options... By contrast, getting from student to either referent.relation_quality or the list of all its teacher is straightforward.
  • With option 3, the logical integrity could also be compromised by setting more than one "is_referent" to True for a student... and as with option 1, I suspect that my very simple existing Django templates would need some new code to process this.

My impression is that both 3 approaches would work. But is one of these more advisable / standard, especially to avoid confusing people that might have to deal with my database in the future?

0

There are 0 best solutions below