Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Storing Scheduling Information

    We currently have an issue where there are time conflicts when people book classrooms for meetings at inappropriate times. Therefore, I am trying to make a very, very simple application that simply indicates when certain rooms are booked for meetings or other events. For example, a person may want to book Room 48 on April 10, 2008 from 10:30 AM to 12 noon, but if someone else has already booked Room 48 during that time span, then there would be a scheduling conflict.

    My question is this: is there a good way to store these schedules in a database? I am trying to wrap my head around how to get it in there and still be decently efficient. I will likely be using MySQL, but right now, I'm just trying to get the basics of the database itself nailed down.

    Any guidance would be appreciated!

    Jason

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Scheduling is never "very very simple", so disabuse yourself of that notion right now.
    There are several ways to implement this. Some require very little SQL coding knowledge, but need a lot of administrative time and are prone to breakage.
    Others are very robust, but require at least moderate-to-advanced database design and SQL programming skills.
    Which challeng are you up for?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2008
    Posts
    3
    Oh, boy. Thanks for the vote of confidence, blind.

    I don't have many SQL skills. I'll be using in conjunction with PHP, which a know a little more about.

    I was thinking about a table like this:

    Code:
    ID		ROOM		DATE		BEGIN		END
    0		48		9/9/2008	8 AM		9 AM
    1		50		9/9/2008	10 AM		1 PM
    2		46		10/11/2008	8 AM		5 PM
    Then, when someone requests, for example, Room 50 from 11 AM to 12 noon on 9/9/2008, it'll inform them that it has already been booked.

    Does this make sense? Or am I oversimplifying it?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    In a way, you are oversimplifying it.

    Imagine the room is booked from 10 am to 2 pm for a meeting that runs over lunch. You will need to determine that the following scheduling attempts would conflict with that booking:
    9am-11am
    9am-3pm
    9am-1pm
    1pm-3pm
    Notice that none of these examples even have the same start or stop times as the existing booking, so an SQL statement with a conventional JOIN will not detect the conflict.
    So again, we can show you how to do this, and it is not the most difficult SQL problem one can come up with (it is pretty common, actually), but it is definitely a step beyond "beginner".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    ...the alternative is to have a table of all possible bookings for all your rooms, for the next year or so in, say, 15 minutes increments. Then you just mark these records as reserved when the room gets booked. Easier SQL, but obviously not as robust and you need to keep adding potential reservation times at regular intervals throughout the year.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2008
    Posts
    3
    Thanks for all the input, blind. This is great.

    Okay, for determining whether something is booked or not, could I do this?

    1.) From 8 AM to 11 AM on June 13, 2008 is booked.

    2.) Someone attempts to book from 10 AM to 12 noon on June 13, 2008.

    3.) The database returns all existing records (i.e., bookings) for June 13, 2008.

    4.) PHP checks the beginning and ending time stamps for the requested booking.

    5.) PHP checks the beginning and ending time stamps for all existing records (or bookings) of June 13, 2008.

    6.) PHP compares the beginning time stamp of the requested booking to both the beginning and ending time stamps for all existing bookings for June 13, 2008.

    7.) PHP discovers that the beginning time stamp of the requested booking falls between the beginning and ending time stamps one existing booking for June 13, 2008.

    8.) Because the beginning time stamp of the requested booking is in conflict with an existing booking of June 13, 2008, the ending time stamp of the requested booking is not compared. The user is told that there is a conflict.

    Does this make any sense? Am I forgetting something? I feel like I am. :\

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I presume you've tried using the room booking facility using the calendar in Microsoft Outlook. It's worked quite well at every company I've been at.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by loosus
    My question is this: is there a good way to store these schedules in a database?
    yes, there is

    store the start datetime and end datetime for each booking

    (do not separate the time from the date, that will make your SQL horribly complicated)

    then when you want to do a booking, take the start datetime and end datetime of the requested booking, and use them to search for any existing booking

    it helps to visualize what's going on with a timeline diagram

    see this thread --
    http://www.dbforums.com/showthread.php?t=1626870

    thus, when someone requests, for example, Room 50 from 11 AM to 12 noon on 9/9/2008, your query would say:

    SELECT 1 FROM bookings
    WHERE booking_end >= '2008-09-09 11:00'
    AND booking_start <= '2008-09-09 12:00'

    if you get any results from this query, it means there's a conflict and the requested booking cannot be made
    Last edited by r937; 06-12-08 at 08:36.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'm picking a really fine point, because I agree with r937 and his proposed solution almost completely. The difference that I see is that I never consider the equality to be a problem for booking rooms, so that if one meeting runs from 10:00 to 11:00 and another meeting runs from 11:00 to 13:00 then r937 would consider that a conflict but I don't.

    I would STRONGLY second his recommendation to keep date and time in one column, that makes life so much easier!

    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    starting another meeting at the same time that a previous one ends never works (as you know if you've ever tried it)

    however, i think you should take another look at my solution, pat

    you've implied that my solution doesn't allow for one meeting to start at the same time as another ends

    i think it does

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937
    you've implied that my solution doesn't allow for one meeting to start at the same time as another ends

    i think it does

    Oh no it doesn't!
    Code:
    SQL> create table bookings (booking_start date, booking_end date);
    
    Table created.
    
    SQL> insert into bookings (booking_start, booking_end) 
      2  values (timestamp '2008-09-09 10:00:00', timestamp '2008-09-09 11:00:00');
    
    1 row created.
    
    SQL> insert into bookings (booking_start, booking_end)
      2  values (timestamp '2008-09-09 12:00:00', timestamp '2008-09-09 13:00:00');
    
    1 row created.
    
    SQL> select 1 from bookings
      2  where booking_end >= timestamp '2008-09-09 11:00:00'
      3  and booking_start <= timestamp '2008-09-09 12:00:00'
      4  /
    
             1
    ----------
             1
             1
    But:
    Code:
    SQL> select 1 from bookings
      2  where booking_end > timestamp '2008-09-09 11:00:00'
      3  and booking_start < timestamp '2008-09-09 12:00:00'
      4  /
    
    no rows selected

  12. #12
    Join Date
    May 2009
    Posts
    5
    HI,
    as i am also developing a website for booking resources.
    Thanks for your messages.With this forum i have learnt much about sql queries.

    But i have one doubt....

    with the booking table(startdate and enddate),how will you show the avilability of timings to the users.

    Sorry first i will explain my situation...

    i want to display the avialbility an dreserved ones of resources in a gridview in .net,i am using sql server 2005 as backend.

    So i am dividing one hour to 4 parts(i.e from 8-9 is 8.15,8.30,8.45,9.00).so i want to show the avilability of all the (8 hours *4) timings.

    i am in a bit confusion of how to display this.for now i have createed a table with (8 hours * 4) columns.But i think this doesnt make sense.

    Can anyone help me in how to creat a table for this.

    Thanks.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You will create a virtual table of possible times in the range your are examining, left outer join it to the list of booked times, and show only those possible times for which there is no corresponding booked time.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    May 2009
    Posts
    5
    Hi blindman,
    Thanks for the reply.

    But creating a table with possible times means a table consistes of 8 hours * 4 columns.it takes so much memory i.e,for the unreserved times there will be a null value.suppose in a day,only 1 hour is booked by some one ,then other fields on that day will be null,so memory of database will be wasted.

    i have table with columns as:
    bookingID Date rooms starttime endtime

    so i want to calculate the differenece betweenthe starttime and endtime.
    forexample: if strattime is 08:00:00 and endtime is 09:00:00
    then i want to do calculate the difference between 8 & 9 and get the result as 8:15,8:30,8:45,9:00. With this i can show the reserved ones in the application.
    How can i write a query so that i should get this result.

    Thanks in advance.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by sanvga
    How can i write a query so that i should get this result.
    You've got the right idea, do this in the application.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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