Results 1 to 6 of 6
  1. #1
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114

    Help with Many to Many Relationships

    Hello,
    I am a novice and am trying to develop a database for managing course and instructor information.

    I have courses, instructors, TAs, and readers.

    Each course can have more than one instructor, TA, or reader (for example,
    1 instructor, 2 TAs, 1 reader or 2 instructors, 1 TA, and 2 readers or one instructor without TAs or readers).

    All courses have instructors, but some of them don't have a TA or a reader
    or have only one of them.

    Each instructor can teach more than one course. So I think this creates a
    many to many relationship.

    It gets complicated (for me, at least) because an instructor of a course can also serve
    as the reader for that course and/or as the TA as well, so I don't even
    know what to call that kind of relationship.

    Additionally, an instructor in one course can serve as a TA in a different course that has a different instructor.

    I tried to model this but got stuck creating the relationships.
    I first created four tables: Courses, Instructors, TAs, and Readers.
    Then I created a junction/link table to accommodate the many to many situation, but I couldn't figure out how to handle the relationships between each of my staff tables and the Courses tables.



    How do I make the PK from the staff table the FK in multiple places in the junction table (Instructor1, Instructor2, TA1, TA2, Reader1, Reader2)?

    Would it help to have one Instructional Staff table instead of a separate
    table for instructors, TAs, and readers?

    Do I even need the junction/link table? Do I need more junction tables?

    Thanks!
    Last edited by alpinegroove; 03-24-2011 at 07:07 AM.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Would it help to have one Instructional Staff table instead of a separate
    table for instructors, TAs, and readers?
    Absolutely, like data should be in 1 table, so all people should be in 1 table.

    Now since a person can play many roles (instructor, TA, reader), you have another one-to-many relationship. Additionally, a role can apply to many people, so another one-to-many which gives you a many-to-many relationship.

    tblPeople
    -pkPeopleID primary key, autonumber
    -txtFName
    -txtLName

    tblRoles
    -pkRoleID primary key, autonumber
    -txtRole

    tblPeopleRoles
    -pkPeopleRoleID primary key, autonumber
    -fkPeopleID foreign key to tblPeople
    -fkRoleID foreign key to tblRoles

    Now since a course can have many people/role combinations you need another junction table

    tblCourses
    -pkCourseID primary key, autonumber
    -txtCourseName

    tblCoursePeopleRoles
    -pkCoursePeopleRolesID primary key, autonumber
    -fkCourseID foreign key to tblCourses
    -fkPeopleRoleID foreign key to tblPeopleRoles

    Out of curiosity, I assume that a course can be offered many times (multiple times within a term, one term to another etc.) and the people assigned to the course may be different from one time to the next, so I think you many have to junction the courses with the times the course can be offered and then relate the various people to the course/time combination.

  3. #3
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    You're absolutely right: many-to-many is exactly the situation you've got, and a junction or cross-reference table is what you need to sort things out.

    First, however, you should combine your Instructors, TAs, and Readers into one table, not 3. You'll assign their class roles in the junction table.

    Based on the information you've supplied, something like the following table design ought to work:

    ClassID (Foreign Key to the Classes table)
    InstructorID (Foreign Key to the Instructors table)
    InstructorRole

    The last column/field is where you identify the role the instructor has in conducting the class. This could be (would be if I were designing this) a code which refers to a lookup table which has a human-readable list of the available roles as well as the codes. The code would be added to the table on a form using a combo box which looks up the code & description in the lookup table and puts the code in the junction table.

    What you'll end up with is one or more rows for each class, something like this:

    ClassID | InstructorID | Role
    1 | 1 | 1
    2 | 2 | 1
    2 | 1 | 2
    ....

    And so on. Note that in the example above, Instructor 1 has been assigned both Role 1 for Class 1 and Role 2 for Class 2.

    Does this make sense?

    Steve

  4. #4
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114
    Thank you, SteveF and jzwp11.
    I had a feeling I needed more junction tables, but I wasn't sure how to think it through. This helps a lot and gives me a clear direction.

    Is this what you are talking about?

    tblPeople
    1, Johnson, Mike
    2, Davids, Mary

    tblRoles
    1, Instructor
    2, TA
    3, Reader

    tblPeopleRoles
    1, 1, 1 (MJ=Instructor)
    2, 1, 2 (MJ=TA)
    3, 1, 3 (MJ=Reader)
    4, 2, 1 (MD=Instructor)
    5, 2, 2 (MD=TA)
    6, 2, 3 (MD=Reader)

    Do I have to create this table manually every time I add a new instructional staff member? I should have mentioned that some of my instrcutors are also TAs and Readers but most of the TAs or Readers are only TAs or Readers.

    SteveF, it looks like your model doesn't create all the role possibilities for each staff member. Is it necessary?

    tblCourses
    1, Baking 101
    2, Carpentry 200
    3, Marketing Basics 1

    tblCoursePeopleRoles
    1, 1, 4 (Baking 101, Instructor: Davids, Mary)
    2, 1, 6 (Baking 101, Reader: Davids, Mary)
    3, 3, 1 (Marketing Basics 1, Instructor: Johnson, Mike)
    4, 3, 5 (Marketing Basics 1, TA: Davids, Mary)

    What kind of forms should I create to easily interact with this model?

    Thanks again. This is great help.

  5. #5
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    SteveF, it looks like your model doesn't create all the role possibilities for each staff member. Is it necessary?

    tblPeople
    1, Johnson, Mike
    2, Davids, Mary

    tblRoles
    1, Instructor
    2, TA
    3, Reader

    tblCourses
    1, Baking 101
    2, Carpentry 200
    3, Marketing Basics 1

    IMO, you don't need a PeopleRole junction table -- this is taken care of in tblCoursePeopleRoles. Given the values in the other tables, here's what tblCoursePeopleRoles would look like for the descriptions you supplied:

    tblCoursePeopleRoles (Field Order: Course, Person, Role)
    1, 2, 1 (Baking 101; Davids, Mary; Instructor)
    1, 2, 3 (Baking 101; Davids, Mary; Reader)
    3, 1, 1 (Marketing Basics 1; Johnson, Mike; Instructor)
    3, 2, 2 (Marketing Basics 1; Davids, Mary; TA)

    Please note that this structure does NOT show any RowID/RecordID/Primary key field and the values in it -- It just has the fields listed in the Field Order list. Strictly speaking, an artificial Primary Key is not necessary for a junction table.

    What kind of forms should I create to easily interact with this model?
    I'd recommend a form/subform setup for data entry. One way to go about this would be to have the master section of the form show the different classes, with a continuous-form subform listing the people and their roles. Link the subform to the junction table, then use combo boxes in the subform row to select the people and roles. Join the subform to the master form using the Course ID.

    Steve

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I also thought about using Steve's approach to the roles (as part of the junction table) but decided to establish the relationship between the people and their roles upfront.

    Strictly speaking, an artificial Primary Key is not necessary for a junction table.
    I don't necessarily agree with Steve's statement especially if the junction table has a one-to-many relationship with another table. If that is necessary, then you would have to use a 3-field composite key (course/people/role) in order to join to the related table. That would get kind of messy to handle in my opinion, but I am biased in that I don't like composite keys.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Relationships
    By bopsgtir in forum Database Design
    Replies: 1
    Last Post: 01-10-2011, 12:44 PM
  2. Relationships again
    By BarbT in forum Access
    Replies: 2
    Last Post: 11-09-2010, 04:12 AM
  3. One to many relationships
    By CoachBarker in forum Database Design
    Replies: 1
    Last Post: 09-13-2010, 09:01 PM
  4. Too Many Relationships
    By MikeT in forum Database Design
    Replies: 4
    Last Post: 08-25-2010, 07:23 PM
  5. Relationships?
    By rosh41 in forum Database Design
    Replies: 2
    Last Post: 06-23-2010, 01:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums