locked
Help/Review Designing a Many to Many to Many Relationship RRS feed

  • Question

  • Hello,

    I am needing to design a relationship(s) that are somewhat complicated for me so I would appreciate the gurus here critiquing me.

    My scenario is thus for an academic scenario:

    There are Classes (Such as English 101)

    There are Humans (Bob, Jane, Harry)

    There are Roles (Primary Instructor, Co-Instructor, Shadow, Student)

    I can envision one entity to another but the three interacting is where I want to make sure I am getting it right.

    So starting with Roles and Classes. Every class can POTENTIALLY have a human in every role but many times will not have all the roles fulfilled.

    I think it best to tell you my vision before hashing out my pathetic technical implementation.

    Visually I am thinking to modify the class record form with a tab dedicated to roles. This roles tab would have five sub-grids...each sub-grid being one of the five potential roles minus the student role. (I know there are issues already peaking at me which is why I am asking for your help)

    So the admin type user could add people to each of the sub-grids. To restate with a scenario:

    For English 101 an admin type could go to the record and add 2 people (these would be users not contacts) to the "Instructors" role sub-grid. Let's say Jane, and Bob.

    I now have a relationship that I'm not sure how to express technically. A class can have many roles and many users.

    So two 1:N relationships. (class(1) to roles(N)) and (class(1) to users(N))

    Now turning to the users when an admin type opens a user I would want to a have similar capability.

    I would have a tab on the user form for "skills" with the same 5 roles...but the sub-grid this time would show the classes. So I could add Math 101 under the instructor role to Jane's user record. (English 101 would already be there).

    So two 1:N relationships. (user(1) to roles(N)) and (user(1) to classes(N))

    Now considering these two alone I  see class(1 to user(N) and user(1) to classes(N) so...that would translate to a many to many (N:N) correct.  user >> User_Classes << classes.

    Now I also see (conceptually) that there has to be a relationship N:N between user and roles. users >> User_Roles << roles.

    There is also a (conceptually) a N:N between classes and roles. class >> Class_Roles << roles.

    So this is one big happy circle?

    user >> classes >> rolls >> to users and round we go? Do I have this correct or am I missing something?

    Thank You very much for your patience reading this and your input.

    Tuesday, March 18, 2014 3:26 PM