In this episode, we take a hands-on approach to building two essential parts of our employee scheduling system: the employee schedules table and the schedule exclusions table.
First, we dive into designing the employee schedules. Employees can have multiple schedules—for example, one per year. Each schedule, besides referencing the employee, has start and end dates indicating the valid period, and then a column for the start and end time for each day of the week (e.g., Monday starts/ends at, Tuesday starts/ends at, etc.), all of which can be nullable to handle different working setups. This gives us super fine-grained control over when an employee is available. We run the migration and set up the model relationships, like connecting employees to their schedules and ensuring date fields are correctly cast so we can easily handle them in code.
Next up, we set up the schedule exclusions table. This table is a bit simpler—it just tracks the periods where an employee is unavailable, like vacations or leaves. Similar to the schedules table, we add start and end dates and hook up the model relationships so we can easily query exclusions for each employee.
Throughout, we point out how these two tables work together: you build up employee availability from their schedules, and then subtract stuff that's in their exclusions, resulting in the final availability we'll use later. Now that we have these database tables in place, we're ready to move on to the core scheduling logic in upcoming episodes!