We have numerous events throughout the year that are supported by multiple teams. Team A will support the entire event duration while Team B will support segments (typically per week). It is common for the event start date and duration (typically 10-30 days) to shift independent of other events creating a problem tracking gaps and overlap in coverage of these events. We want to create a central schedule so each team can see how event changes creates gaps and/or overlaps in assignments.
Event StartDate Duration (Days) E1 someDate1 25 E2 someDate2 14 E3 someDate3 22
Team User A 1 A 2 A 3 B 4 B 5 B 6
Event User DayStart DayEnd E1 A1 1 NULL E1 A2 1 NULL E1 B4 1 7 E1 B6 15 NULL E2 A2 1 NULL E2 B7 1 7 E2 B4 1 7 E2 B5 8 14 E3 B5 1 7 E3 B6 8 14
The above example data has a gap for Team B during Event 1 (days 8-14) and Team A has no coverage for Event 3.
The thought is to have a single assignment table where
NULL signifies that the individual is covering the remainder of the event. Is this a reasonable way to structure the tables? Would it be better to have separate tables for each team (less than five total teams)? Should
NULL be replaced with some sufficiently high number such as 999? Each team will also have role assignments within events, but believe that will just be an additional table and not relevant to this question.
Disclaimer: this question was asked on stackoverflow.com without response