I'm fairly new to FM but have a moderate grasp of database design. I'm building a solution for a small business that teaches Kung Fu classes.
The database needs to track many things including regular weekly classes, one-off events, regular/one-off payments (both in and out), instructor hours, class registers, email lists and a bunch of other stuff.
I investigated using a calendar plug-in for the class scheduling but our class details are so specific (Kids age range, Instructor count, Lead Instructor, Assistant 1, Assistant 2, Extra instructors, Venue, Weekly repetitions, Termly repetitions and tracking which staff have said they can not work a particular class, instructor pay grade, holiday, sickness, volunteer/paid instructors) that it seemed easier to build a custom solution that uses a date ordered list to staff and organise the classes.
I'm having trouble with how to organise the schema for the classes and events. I want to be able to provide a layout with a list of all the classes AND one-off events for a particular week, showing which instructors are working that class/event, as a way of replacing a paper rota system.
If I want to show both regular classes and one-off events in a portal do they have to be stored in one table? I'm guessing yes but with classes and event having different details (fields) this seems inefficient.
I have attached some rough plans for my schema showing my overall idea (top) and further complications about events (bottom).
The top shows how members will be enrolled in classes through a join table (Class Attendance) and then instances of each class will be generated in a further table, so specific staffing details can be attached to each one with it's own specific date.
The bottom shows how the events are actually split into categories (seminars and parties), again each with differing details. Here I have attempted to use a join table between the events and the members (Event Attendance) but I'm not sure exactly how to do it. Can I have an event-id field and populate it with either a party-id OR a seminar-id? Or do I have to have one field for party-id's and one for seminar-id's?
Ultimately there's more categories than this and I will also need to employ similar techniques to payments where there are regular and one-one instances, but some design advice would be much appreciated.
Thank you in advance! :)