alpinegroove
Technical User
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)?
Do I even need the junction/link table? Do I need more junction tables?
Thanks!
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)?
Do I even need the junction/link table? Do I need more junction tables?
Thanks!