Design Thoughts For Creating Availability Time Slots For A Given Day

PHP
1

Hi All
I’ve designed a system where my staff can set their availability throughout the day ie 09:00 - 09:30 and so on.
This is stored in the database as minutes from 09:00 - 09:30 as unix time stamps and as available next to each. So we have 30 records for 30 minutes of time.
Multiple this up through out the day and you have alot of records, or even more over several weeks.

When someone sees if this person is available from same 09:00 - 09:30 , the script checks the timestamps in an array between these times and checks to make sure any timestamp are not
available, if not the time is not shown. The availability time can vary massively so it could be 9:25 - 10:30

My concern is over a week alot of records are going to be generated, but I can’t see a method of compacting the times down so you can check any time in minutes.

Anybody got any ideas as I’m stuck on how to check in minutes, without creating millions of records

Steve

2

This design feels wrong to me. What if you wanted to extend your system to one-second accuracy? You’d have to multiply the number of records by 60!

Why not just have one record per one time slot? The record would have two TIME or DATETIME columns - from and to. Then if you want to look for a person available from 09:00 to 09:30 you do this:


SELECT * FROM persons WHERE av_from >= '09:00' AND av_to <= '09:30';

You can do other searches depending on your needs. Of course, you’ll have to handle to proper logic of inserting/udating records so that they don’t overlap, etc.

3

Thanks for your reply, for a staffs weekly slots which are shown as Mon - Sun and say their day runs 09:00 - 17:00 and each time slot interval is 30 minutes,
is that alot of queries to pull from the db for each day. that is shown ?

4

I don’t know what you mean exactly but you can make your system to accept constant hours on a weekly basis and add for this purpose another column weekday - let’s assume it is a number from 1 to 7 (Mon.-Sun.). For example find the free time slot on Monday:


SELECT * FROM persons WHERE weekday=1 AND av_from >= '09:00' AND av_to <= '09:30';

But then the disadvantage is how to cope with exceptional events, for example someone is away on a certain day. Whatever you do I think you should get rid of the concept of having one row per minute.

5

I’d consider just storing actual bookings in your database, available time slots could be a simple PHP array.


time_availability
============
id | 23
person | 1
time | 2013-02-01 10:00:00
reason | 1

If Bob is person 1, then he has a booking today to see a client (reason 1)

If Bob is person 1, and that is the only record for today, or this week, then everything else is bookable for him.

The same table, same fields, then imagine this:


time_availability
============
24  | 2 | 2013-02-01 9:00:00 | 3
25  | 2 | 2013-02-01 9:30:00 | 3
26  | 2 | 2013-02-01 10:00:00 | 3
27  | 2 | 2013-02-01 10:30:00 | 3
28  | 2 | 2013-02-01 11:30:00 | 3
29  | 2 | 2013-02-01 12:00:00 | 3
// etc

Jenny is person 2 and has the morning off for training (reason 3)

Use proper time fields for time slots, its easier to understand and easier to filter with Mysql’s time based functions. Makes sense when you browse the data too.

Arguably the id field could be redundant because the person / time record should be unique.

6

Thanks for your input, I have alot of other parts of the system that use the time slot one minute idea, so I need to reduce the amount of slots created for a time band ie 9 - 10.
Idea I’ve had is to store fromTime and toTime ie 9:00 - 9:30,
Pull this out in an array and drop into a loop in minutes so I get the same result as having 9:00, 9:01, etc. This part works fine.

When someone books a 35 minute session at say 9:35 this needs to fill up 9:35 - 10:05 as not available, before I could just update each record in as I just searched for that day and time.
I know have bands of time stored, my logic was to search between for this time band, reduce the to time down to the start of the session and create a record for the session, but cant figure out
how to do this is it falls over two records ie 9:30 - 10:00 and 10:00 - 10:30

Example
id 1 : 09:00 - 09:15
id 2 : 09:15 - 09:30
id 3 : 09:30 - 09:45
id 4 : 09:45 - 10:15

Session at 09:14 for 45 minutes, seach using between for 09:15 & 10:00, results back are id 2, 3, 4
change id 2 to booked, change id 3 to booked
change id 4 to end of book time ie 10:00

What over logic do I need to think about, any thoughts ?