[loosus]

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

[blindman]

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?

[loosus]

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:

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?

[blindman]

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".

[blindman]

...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.

[loosus]

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. :\

[mike_bike_kite]

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.

[r937]

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 --
Absences between a date range...

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

[Pat Phelan]

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

[r937]

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

[andrewst]

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!

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:

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