Relational Problem (Might be a brain problem)

[Mary_Going2]

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

Tables:

Instructors: Name, Email, etc.
Class: Class Name, etc.
Offerings: Class Name (linked), Instructor 1 (linked), Instructor 2 (linked), Date

Sample Data
Instructors: Aminta, Iman, Catherine, Franklin
Class: Waltz, Tango
Offerings:
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.

[ScottWorld]

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:

1. In your offerings table, delete the “Instructor 2” field altogether.
2. Rename your “Instructor 1” field to just be “Instructor”.
3. Then, for every record in your offerings table, you will create 2 records — one record for each instructor.

In other words, each record in your offerings table will act as a “unique intersection” of Class Name + Instructor + Date.

[Mary_Going2]

ScottWorld: Thank you for this suggestion, but allow me to clarify: these are not two sections of the same class. The same class has two instructors who teach together. Two instructors, one class.

[ScottWorld]

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.