Join table connected to more than two other tables (TOs)
Hi, my head is exploding trying to figure out the best relationship graph for the following situation:
I have these entities:
with these relationships:
- Any client can have multiple appointments.
- Any appointment can include multiple clients ("class").
- Any instructor can have multiple appointments.
- Any appointment can include multiple instructors (up to 3).
- On any appointment one offer will be executed.
- Any offer can spread offer multiple appointments.
Therefore there are the following many-to-many relationships which need to be resolved:
- Clients >---< Appointments
- Instructors >---< Appointments
I thought of solving these with an Enrollment join table like this:
Offers ---< Appointments >--- Enrollment ---< Clients
and: Enrollment ---< Instructors
However, the Enrollment table would include the keys of Appointments, Clients and Instructors with each AppointmentID being able to have more than one ClientID and InstructorID. Is this correct? When adding 2 clients and 1 instructor to an appointment, is it correct that two records would be created in the Enrollment table:
- AppointmentID1 ClientID1 InstructorID1
- AppointmentID1 ClientID2 InstructorID1
Thanks for any suggestions,