Can you please help me think though how I should design this base? I want to list classes and instructors, but I’m having trouble figuring out how to group by instructor since I’m using the same table for Instructor 1 and Instructor 2
Instructors: Name, Email, etc.
Class: Class Name, etc.
Offerings: Class Name (linked), Instructor 1 (linked), Instructor 2 (linked), Date
Instructors: Aminta, Iman, Catherine, Franklin
Class: Waltz, Tango
Waltz (linked), Aminta (linked), Franklin (linked), 8/31/20
Waltz, Franklin (linked), Iman (linked), 9/17/20
Tango, Franklin (linked), Catherine (linked), 9/24/20
The question is this:
Is there a structure that would allow me to pull instructor 1 and instructor 2 from the same table (like I’m doing) where I could also group them and get all the classes taught by each leader? For example, in this data set, if I group by instructor 1, I miss the waltz class taught by Franklin where he is instructor 2.
Welcome to the community, @Mary_Going2!
You’ve done a wonderful job of structuring your tables so far to accommodate your “many-to-many” relationship!
The changes that you’ll want to make are these:
In other words, each record in your offerings table will act as a “unique intersection” of Class Name + Instructor + Date.
Yes, I understand. If you want to group each instructor separately & track each instructor separately, then the instructors need to be separated out as separate records.
However, if you only want to see 1 record representing each offering, then you would need to create another table for “Offering Instructors” — and each offering from the “Offering” table would link to 2 new instructor records in the “Offering Instructors” table. So, in that new “Offering Instructors” table, each record would represent the intersection of 1 offering + 1 instructor.
So, regardless of which table methodology you choose, each instructor will ultimately need to end up in its own individual record in that particular table — if you want to group your instructors individually.
Alternatively, if you don’t care about grouping instructors individually, but you’re okay with grouping instructors “as a pair of 2 instructors”, then you would just delete the “Instructor 2” field, put both instructors into the “Instructor 1” field, and group by the “Instructor 1” field. Then, you’ll get groupings by pairs of instructors.