Hello all!
I'm working with a small school that is restructuring how we track staff time-off and homeroom assignments per year. Staff get reassigned to different homerooms each school year, and I need sick/personal-day totals to be tracked and reset cleanly per year without losing history once a school year closes. I was thinking a junction table might work but would love to hear any other options I might have overlooked or if I’m overengineering this!
(Bold indicates table, all listed items are the fields. Homerooms, Staff Year Assignments, and Staff Time-Off would be the new additions)
Staff (existing table)
Homerooms
- Homeroom Name
- Active Status
- Assigned Students (linked to Students table)
- Current Staff Count (rollup from Staff Year Assignments table)
- Min Staff Required (formula)
- Ratio Status (formula for staff per student ratio)
Staff Year Assignments (junction — one record per staff member per school year)
- Staff (link)
- School Year (link)
- Homeroom (link, blank for non-teaching staff)
- Assignment Role (Lead Teacher / Co-Teacher / Assistant / Contractor)
- Sick Days Used (rollup from Staff Time-Off, this year only)
- Personal Days Used (rollup)
- Makeup Hours Logged (rollup)
Staff Time-Off
- Staff Year Assignment (link — not a direct link to Staff or School Year)
- Request Type (Sick / Personal / Makeup)
- Date(s)
- Approval Status
- Conflict Flag (formula/rollup to check staff per student ratio)
Is routing through the junction table (instead of a direct Staff link) overkill, or is this the best path for relationships that change yearly?
Is a self referencing formula/rollup field the best way to check overlapping records in the same group? (i.e. flagging a time-off request if another staff member in the same Homeroom already has an approved or pending request overlapping the same date.)
Thanks!!!😀
