Schedules and the database

So, I've previously developed an employee scheduling system in php. It was VERY inefficient. When I created a new schedule, I generated a row in a table called 'schedules' and, for every employee affected by that schedule, I generated a row in a table called 'schedule_days' that gave there start and stop time for that specific date. Also, editing the schedules was a wreck too. On the editing page, I pulled every user from the database from the specific schedule and printed it out on the page. It was very logical, but it was very slow.

You can imagine how long it takes to load around 15 employees for a week long schedule.开发者_StackOverflow中文版 That would be 1 query for the schedule, 1 query for each user, and 7 queries for each day for every user.. If I have 15 users thats too many queries. So I'm simply asking, whats someone else's view on the best way to do this?

For rotation based schedules, you want to use an exclusion based system. If you know that employee x works in rotation y within date range z, then you can calculate the individual days for that employee on the fly. If they're off sick/on course/etc., add an exclusion to the employee for that day. This will make the database a lot smaller than tracking each day for each employee.

table employee {EmployeeID}
table employeeRotations {EmployeeRotationID, EmployeeID, RotationID, StartDate, EndDate}
table rotation {RotationID, NumberOfDays, StartDate}
table rotationDay {RotationDayID, RotationID, ScheduledDay, StartTime, EndTime}
table employeeExceptions {EmployeeExceptionID, ExceptionDate, ExceptionTypeID (or whatever you want here)}

From there, you can write a function that returns On/Off/Exception for any given date or any given week.

Sounds like you need to learn how to do a JOIN rather than doing many round trips to the server for each item.