Room Booking... any thoughts?

shoez has asked for the wisdom of the Perl Monks concerning the following question:

Hi all,

I have been building a web based room booking application for my local school, using my newly founded Perl-OO skills (thanks goes to the Perl CD) :) More specifically it is for the media room, where reprobates, sorry, us students (12-18 y/o) go to perform part of their studies. These three rooms are normally booked, independently, for a period of days.

As I currently have it, the date ranges in which these rooms are booked get stored in a MySQL database. To query if a room is free for a particular period, all I do is perform an SQL query, with a WHERE clause something like -

(($startdate BETWEEN BookingStart AND BookingEnd) OR ($enddate BETWEEN BookingStart AND BookingEnd)) AND RoomID = ?

Now this is all well and good, and maybe this isn't even a Perl question, but I have racked my brains for some funky alternative method of performing it using my fav. language. The problem is, I just don’t see this being an efficient way. Could I ask the indulgence of the venerable monks in giving me a substitute means of either storing or querying this data please ?

Kind Regards,

shoez

Comment on Room Booking... any thoughts?

Replies are listed 'Best First'.
Re: Room Booking... any thoughts? by BazB (Priest) on Jan 29, 2002 at 05:22 UTC
This is a very common problem - it's part of MIT's 6.916 course - they define the problem here. MIT just happen to expect you to use Tcl (and the ArsDigita Community System) and Oracle to solve the problem, but there's no reason why you can't do this in Perl with MySQL or PostGreSQL. Stick with using a database to store the room and reservation information - the main problem is making sure that you don't have concurrency issues. I'd suggest you read SQL for Web Nerds over at ArsDigita (I tackled this problem at one of their bootcamps). SQL for Web Nerds is a very interesting introduction to SQL and gives examples that are relevent to your problem, although the Oracle queries shown there might need tweaking for use with MySQL. The final implementation is left as an exercise for the reader.	[reply]
Re: Room Booking... any thoughts? by poqui (Deacon) on Jan 29, 2002 at 03:15 UTC
A very similar problem is treated in "the Panther" aka: Advanced Perl Programming by Sriram Srinivasan. Chapter 2. Professors, Students, Courses He treats each item (IE course) as a hash, and a professor's (or course's) schedule as a bitmap vector. Each bit represents a time slot that can be reserved/scheduled. For example, to see if a professor is available to teach a course, AND his schedule vector with the Course's. Any non 0 result represents a collision.	[reply]
Re: Room Booking... any thoughts? by radiantmatrix (Parson) on Jan 30, 2006 at 21:20 UTC
Um, you are using DBI's placeholders, right? Because if you're passing user data into those variables, you're leaving yourself wide open for SQL injection attacks... <-radiant.matrix-> A collection of thoughts and links from the minds of geeks The Code that can be seen is not the true Code I haven't found a problem yet that can't be solved by a well-placed trebuchet	[reply]