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

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?

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:

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?

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

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

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

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.

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

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

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

Oh no it doesn't!
But:

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.

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.

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.

You've got the right idea, do this in the application.

-PatP