locked
Help with Many to Many Relationships RRS feed

  • Question

  • 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!
    Thursday, March 24, 2011 4:02 AM

Answers

  • So you have a table something like that

    create table junction

    (

     courseid int not null foreign key referenced courses(courseid),

    instructorid int not null foreign key referenced instructors (instructorid),

    ta_id int nu..........


    )

    You can make courseid,instructorid  as pk to enforce uniquness


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, March 24, 2011 6:50 AM

All replies

  • So you have a table something like that

    create table junction

    (

     courseid int not null foreign key referenced courses(courseid),

    instructorid int not null foreign key referenced instructors (instructorid),

    ta_id int nu..........


    )

    You can make courseid,instructorid  as pk to enforce uniquness


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, March 24, 2011 6:50 AM
  • Any progress?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, March 30, 2011 6:09 AM