Meta-relationships (Relationships between relationships) versus tuples I am designing a database which needs to store some fairly complex relationships among many different types of entities.

In the simplest example, let us say I have three types of entities - "Student", "Tutor", and "Course", each with their own tables (student, tutor, course).  First of all, I need to represent simple many-to-many relationships between each possible pair of entity types:


student_course - Each student can be enrolled in zero or more courses, and each course can have zero or more students.  (ex: "Alex is enrolled in Biology").
student_tutor - Each student can be working with zero or more tutors, and each tutor can be assigned to zero or more students. (ex: "Emma is assigned to Alex").
course_tutor - Each tutor can be approved to teach zero or more courses, and each course can have zero or more approved tutors. (ex: "Emma knows how to tutor Physics").


Easy enough.  However, I also need to represent some more advanced relationships, like:


"Tutor John is working with students Alex and Maria on course Chemistry"
"Tutor John is working with student Alex on course Algebra"


Notice that these are not captured by the binary many-to-many relationships I described earlier - John can be tutoring Maria, and Maria can be enrolled in History, but that doesn't mean that John is tutoring Maria in History.

The way I see it, I have two options:

Option 1 - A single table containing triplets

Table student_course_tutor:

| student_id | course_id | tutor_id |
|------------|-----------|----------|
| 1          | 1         | 1        |
| 1          | 1         | 2        |
| 1          | 2         | 5        |


This is nice because it explicitly lays out the full relationship in a single row.  However, I now need to maintain this table in addition to my simple relationship tables.

Option 2 - Meta-relationships

Table student_course:

| id | student_id | course_id |
|----|------------|-----------|
| 1  | 1          | 1         |
| 2  | 1          | 2         |


Table student_course__tutor:

| student_course_id | tutor_id  |
|-------------------|-----------|
| 1                 | 1         |
| 1                 | 2         |
| 2                 | 5         |


Since I was going to have a student_course table anyway, adding this "meta-relationship" table is less redundant than representing the full triplet.  However, it now becomes a lot more abstract and difficult to understand.

Which way is more correct?
I like option 1 better. My first thought is always maintaining data integrity so my question is, "Could it be possible to relate a student to a tutor for a course the tutor is not approved for?" With table student_course_tutor, the field student_id would, as you say the student needn't be attending the course, be a FK to the Students table and the fields (course_id, tutor_id) would be a FK to the course_tutor table. If the requirement that the student must be enrolled in the course as well as the tutor must be approved for the course, that would require only that the fields (student_id, course_id) be a FK to the student_course table.

So you cover the existing specifications and you have an easy solution for a not unreasonable future requirement.