Best practice for saving calendar data

A scheduling calendar consists of "resources" and each resource has, at a minimum, the following characteristics:

Name (in your case this might be the room number)
User (this might be the person reserving the resource)
Start DATETIME
End DATETIME

Simplistic as it sounds, that is ALL you need, and everything else is optional.  The optional things might include descriptions, URLs, contact email addresses, etc.

There are a lot of things you can add to this problem (and as you add them you will quickly come to see why the calendar project is a college semester) such as permission levels.  For example, if I schedule a room for a week, but my boss want to take the room away from me on Wednesday and Thursday, then he changes his mind and wants to give back Wednesday, but HIS boss says, "no"...

For query speed, you would usually add indexes to your tables, but in calendar processing you can't really do this - the DATETIME fields will almost certainly contain duplicate values.  But having said that, let me reassure you that unless you are scheduling many thousands of rooms, you will never see a speed issue in a calendar application.

To allocate a resource to a user, you add an element to the calendar table with all four of those fields filled in.

To determine if a resource is available over a particular timeframe, you query the table for any rows that match the resource and have an expressed start or end that is within the timeframe (use BETWEEN in the MySQL query).

That's all there is to the framework.  Please post back here if you have any questions.

best regards, ~Ray

Hi Ray.

Thanks for the input. Sounds simple enough. So to query for availability, I would have to query all "Name" where "date of choice" is not between the start date and end date. Even with indexes, that sounds like an slow/involved query. I will have thousands of rooms.

I was originally wondering if it would be faster to create a DB entry for each day in the range. Then to query availability I could query all "Name" where "date of choice" not equal to "date". I think that would be faster, but a messy db.

it is easy to show what is used on a given date. It is the unused ones that I will need to find and was concerned about the speed.

Thanks

HI Ray,

Wow, thanks for the sample. I understand that this would tell me if a specific room is available during a time. But, what I need to find is all the rooms that are NOT books for a specific time. So I think it would be something like sample below. Correct me if I am wrong, but my query below looks like the DB will be building some temporary tables to do this query. Is there a way to speed this up? Is there a better way to query what I need?

Thank you so much for your help on this.

// QUERY TO FIND THE RESOURCE FREE DURING THIS TIME
$sql = "SELECT id FROM calendar WHERE ( ('$my_start' NOT BETWEEN start AND end) OR ('$my_stopt' NOT BETWEEN start AND end) )";

Select allOpen in new window

Thanks for the points - it's a good question.  Hope it comes together well for you, ~Ray

"I need to find all the rooms that are NOT booked for a specific time..."

How many rooms are there?  I would guess that this is a fairly static list, right?

This query would probably work (note the conjunctive, not disjunctive qualification - rooms must be available for the entire time to qualify.

After this query, you would iterate over the results set.

// QUERY TO FIND A RESOURCE THAT IS FREE DURING THIS TIME
$sql = "SELECT id, name FROM calendar WHERE ( ('$my_start' NOT BETWEEN start AND end) AND ('$my_stopt' NOT BETWEEN start AND end) ) ";
 
// RUN THE QUERY AND TEST THE RESULTS
$res = mysql_query($sql);
if (!$res) die( mysql_error() );
 
// IF NOTHING WAS FOUND, EVERY POSSIBLE name IS ALREADY SCHEDULED
if (!$num = mysql_num_rows($res))
{
   die('NOTHING AVAILABLE');
}
// SHOW HOW MANY name RESOURCES CAN BE FOUND
echo "<br/>$num RESOURCES ARE AVAILABLE \n";
 
// ITERATE OVER THE RESULTS SET
while ($row = mysql_fetch_assoc($res))
{
   extract ($row);
   echo "<br/>AVAILABLE: $id $name \n";
}

Select allOpen in new window