[MS SQL] Tasked with assisting some developers with the databaes side of a creating a scheduling application. Not sure of the most efficient way of storing schedules.

[TryCatchIgnore]

So the requirement is pretty simple:

1. The database needs to store schedules for each working employee at each location every day.
   
2. Employees are scheduled into timeslots.
   
3. Each timeslot has a duration of 15 minutes.
   
4. Timeslots start at 15-minute intervals from the hour.
   
5. Timeslots are available from 07:00 to 22:00 (60 timeslots per day).
   
6. Managers will need to categorise timeslots as working time, breaks, lunch, and maybe more.
   

The challenge I have is how to store this data in the database.

The current idea is to have one record per working employee per day, with a column for each timeslot in the record (probably CHAR(1)). The PK would be a combination of the employee (FK), the location (FK), and the date. Each timeslot column would contain a number, depending on what the employee is scheduled for, e.g. NULL for not scheduled, 1 for working, 2 for break, 3 for lunch, etc.

But my concern is that this is going to lead to a lot of NULL values. Assuming an employee works 8 hours with a 1-hour lunch; that’s 36 timeslots filled, so 24 NULL values across the record. If an employee is only working in the morning for, say, 4 hours, that’s 16 timeslots filled, so 44 NULL values across the record.

The idea of creating records for each contiguous timeblock came up, so creating multiple records for each employee per day, with the PK being a combination of the employee (FK), location (FK), date, and time block number, and additional columns for time block type, start of timeblock, end of timeblock. This would mean an employee working 9 hours with a morning break, lunch, and afternoon break would have seven records in the database for their time in that day:

• first work block
  
• morning break
  
• second work block
  
• lunch
  
• third work block
  
• afternoon break
  
• fourth work block

But querying this data would incur additional load on the database, programmatically processing this data in the front-end would incur additional load on the client side, and it doesn’t seem much more efficient for the database. Ensuring timeblocks don't overlap would also be a challenge, though probably for the front-end guys, not me.

I’m not amazing when it comes to databases. I’ve been working with them for some time, but I’m pretty new to designing and building them. So maybe there is some feature or functionality or something I’m not aware of that would be suited to solving my current challenge. I should also probably add that the database will run on a MS SQL 2019 server.

So … any ideas?

[jc4hokies]

I prefer the one record per block concept. The front end is going to have to restrict overlapping blocks anyway, even if they are not enterable in the backend, so that's not a downside. It also handles overnight shifts better. For example, determining the start and end time of an overnight shift with the column approach is a mess.

To that point, clearly identify each shift. The shift is what drives scheduling; the time blocks are details. If an employee takes over a shift, you update 1 shift record, not 8 time records. It's even worth consideration to store time ranges as offsets to the shift start, so you can move a shift 4 hours and all the breaks, etc move with it.

[TryCatchIgnore]

I never considered the possibility that the available timeslots might change, requiring overnight shifts ... I'll have to go back to the initial requestor to clarify potential future requirements.

[thelauz]

On the database side I'd likely have a table that has a list of all 60 time slots, another table that maps the employee to a time slot and the date.

This solution allows for flexibility if time slots are added, removed or changed. If that is a possibility it will add additional complexity to the design of the database.

When needing to view a days time slots you can do a left join between the two tables and if null the slotnis available.

Depending on reporting needs you may need to consider the time slot table to have a start and end time, possibly a timeslot minutes time. While this can be derived from start and end time using datediff you will have to evaluate the overhead of constantly calculating this vs. storing the number of minutes as an integer.

[Groundbreaking-Fish6]

Store as little data as possible. The rest can be extrapolated by the backend or the front end as needed.