Join Table or Not?
Hi there, I'm developing a solution that tracks Kung Fu classes and the members that are enrolled in them. I'm also using it as a replacement to the current staff rota.
I'm linking instructors (from an instructors table) to instances of classes (in a table currently called occurrences) to show who is teaching them. I want to be able to show classes for a particular week in a portal, sorted by date, and have 3 drop down menus for the 3 instructors (Leader/Assistant 1/Assistant 2) that are teaching each class. The complication is that every now and again a class would have more staff, up to 12, but not very often.
I was hoping to use a join table between instructors and occurrences so I can have a flexible amount of instructors linked to any one class and avoid loads of un-necessary instructor fields in the occurrences table. But would it be possible to have 3 drop down menus for each class in the portal and use a "details" button to go to a layout that would allow you to add more instructors?
It's important that one person is identified as the "Leader" and all the other instructors as "Assistants". If I put a field in the join table containing "Leader" or "Assistant" would I be able to always show the leader in the first drop down menu in the portal?
If I did both (had three fields in the occurrences table for the usual 3 staff and had a join table for any extra staff) would this make reporting the amount of hours the instructors have worked each month overly complicated?
Thanks in advance for any help,