AnsweredAssumed Answered

Join table connected to more than two other tables (TOs)

Question asked by ThomasM_1 on Aug 18, 2013
Latest reply on Aug 21, 2013 by philmodjunk

Title

Join table connected to more than two other tables (TOs)

Post

     Hi, my head is exploding trying to figure out the best relationship graph for the following situation:

     I have these entities:

     - Clients
     - Instructors
     - Offers
     - Appointments

     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,
     Thomas

Outcomes