Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    11

    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.

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by alpinegroove View Post
    Would it help to have one Instructional Staff table instead of a separate
    table for instructors, TAs, and readers?
    absolutely, yes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    yup
    one table to hold all instructional staff
    an intersection table to associate members of staff to a course (and in that course intersectin tbael optionally store the role that member of staff has. Im assumign a member of staff cannot have more than one role is any one course.

    the wrinkle is if an organisation may run courses, but allocate staff as required to specific instances of a course.

    eg say the course runs monthly, and in months 1...6 it run by Fred Bloggs, month 7 he's off to be replaced by B.B.Named.... do you need to know which member of staff actuall taught on a specifric instance of a course
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2011
    Posts
    11
    Quote Originally Posted by healdem View Post
    yup
    Im assumign a member of staff cannot have more than one role is any one course.
    This is part of my problem. It gets complicated (for me, at least) because an instructor of a course can also serve as the reader for that same course and/or as the TA as well, so I don't even know what to call that kind of relationship.

    1. So the instructor/ta/reader are sometimes the same person and other times three different people.

    Also, an instructor from one course can be the TA in another course, which is taught by a different instructor.

    2. How do I create the relationships with the interaction table?
    Wouldn't I have to make the PK from tblnstructionalStaff the FK in multiple places in the intersection table (Instructor1, Instructor2, TA1, TA2, Reader1, Reader2)?

    I suppose that is my main challenge.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •