Complex time slot management

doze · #1 (**permalink**) 06-11-09, 08:57

Hello,

I'm about to desing table structure for very complex time slot management. This is the environment:

- One table defines Offices.
- Offices set working day start time and end time (datetime field)
- Employees belong to Offices
- Employees have bookable time slots from working day start time to end time divided by 5 minutes
- Employees have services that can be booked by customers
- Services have information on how many blocks of 5 minute they take

These are the requirements:

1. I need to be possible to do a query to find next available time slots for certain amount of 5min blocks from certain or all employees.

2. I need to build a time schedule calendar for certain employee for certain time span, for example a week calendar.

I have some ideas on how to do this, but I'd like to get some advice on how to do this efficiently. What kind of table structure to create for the time slots table, how to query the next free time slots or time slot information generally to create the time schedules.

I have the tables for offices and employees and services etc.. I'm after for the help with just this time slot table and how to query it.

Thank you very much in advance for any help!

doze · #2 (**permalink**) 06-11-09, 10:40

This is what I have so far based on this article.

Table:

Code:

CREATE TABLE `time_slot` (                         
	`int` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,  
	`date` DATE DEFAULT NULL,                        
	`time` TIME DEFAULT NULL,                        
	`booked` INT(1) NOT NULL,                        
	`employee_id` INT(11) DEFAULT NULL,              
	PRIMARY KEY (`int`)                              
) ENGINE=MYISAM DEFAULT CHARSET=utf8

Some test data:

Code:

INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'1','2009-06-11','09:00:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'2','2009-06-11','09:05:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'3','2009-06-11','09:10:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'4','2009-06-11','09:15:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'5','2009-06-11','09:20:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'6','2009-06-11','09:25:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'7','2009-06-11','09:30:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'8','2009-06-11','09:35:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'9','2009-06-11','09:40:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'10','2009-06-11','09:45:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'11','2009-06-11','09:50:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'12','2009-06-11','09:55:00','0','1');
INSERT INTO `time_slot`(`int`,`timeslot`,`date`,`time`,`booked`,`employee_id`) VALUES ( NULL,'13','2009-06-11','10:00:00','0','1');

Then the query:

Code:

SET @timeslotsneeded = 12;

SELECT s.date, s.time, s.timeslot AS start_timeslot, MAX(s2.timeslot) AS end_timeslot, MAX(s2.time) AS end_time
FROM time_slot s
INNER JOIN time_slot s2
   ON s2.date =s.date AND
      s2.timeslot BETWEEN s.timeslot AND s.timeslot + @timeslotsneeded - 1
WHERE
  s.booked = 0 AND s2.booked = 0 AND s.employee_id = 1 AND s2.employee_id = 1
GROUP BY s.date, s.timeslot
HAVING COUNT(*) = @timeslotsneeded;

It almost works, but not quite..

With the above query I get following results:

Code:

DATE         TIME       start_timeslot   end_timeslot   end_time   
2009-06-11   09:00:00   1                12             09:55:00   
2009-06-11   09:05:00   2                13             10:00:00

So the problem is:

There is one hour for available time slots in the database. I'm querying free time for 12 time slots. It should return only one row starting from 09:00 and ending 10:00.

As I really don't fully understand the query, I'd like to know why it returns the two rows and they contain only 11 time slots?

Thanks!

doze · #3 (**permalink**) 06-11-09, 10:57

Well.. In any case this works fine (just removed the -1 in BETWEEN condition and added +1 to the having), but I don't really understand what it does.. too complex for my mind.

Code:

SET @timeslotsneeded = 12;

SELECT s.date, s.time, s.timeslot AS start_timeslot, MAX(s2.timeslot) AS end_timeslot, MAX(s2.time) AS end_time, s.employee_id
FROM time_slot s
INNER JOIN time_slot s2
   ON s2.date =s.date AND
      s2.timeslot BETWEEN s.timeslot AND s.timeslot + @timeslotsneeded
WHERE
  s.booked = 0 AND s2.booked = 0 AND s.employee_id = 1 AND s2.employee_id = 1
GROUP BY s.date, s.timeslot
HAVING COUNT(*) = @timeslotsneeded+1;

But I'd still like to get an opinnion for you experienced SQL users, is this a good way to solve this?

When doing the time slot management like this, I need to fill that time_slot database table for each day with 5 minute increments for every employees in the office.. and it's the same table for all offices in a company.

So by quickly calculating the possible row amount for the table with a company that has 2 offices with 10 employees each with working day from 9:00 to 21:00 and one month in the time_slot table its:

12 (rows of one hour) * 12 (hours) * 20 (employees) * 31 (days) = 89280 rows to the time_slot table.

Is that kind of row amount fine for a query like above? When executing the above query, I will be using LIMIT to limit the results only for the couple first or certain time span (max one week).

How many rows is sensible to have in a MySQL table?

Internet Services

Web Development

Digital Marketing

Consumer Tech