SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Location
    Poole, Dorset, United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Design Thoughts For Creating Availability Time Slots For A Given Day

    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. #2
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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. #3
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Location
    Poole, Dorset, United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I'd consider just storing actual bookings in your database, available time slots could be a simple PHP array.

    Code:
    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:

    Code:
    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. #6
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Location
    Poole, Dorset, United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •