0

i am on a reservation system for guided tours. And before anyone complains about this question to be a dublicate of this one here: Help with SQL query to find next available date for a reservation system i want to point out that I don't want to use a calender table.

Some basic information:

Reservation table structur:

CREATE TABLE IF NOT EXISTS `reservations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `guider_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `duration` int(11) NOT NULL,
  PRIMARY KEY (`id`),
) ;

userStartDateTime = the start date and time the user choose in the reservation system userEndDateTime = the start date and time the user choose in the reservation system + the duration

Minimum duration for a tour is 15 minutes.

Here's the deal: Customers come to the reservation system. They choose a date and a time, a duration and a range in case the choosen date/time is not available anymore.

We have different guides but it's always the same tour only differed by the duration they take and the places to see (which does not matter at this point). So the reservations can overleap themselves up to a certain level. Every guider is available at everytime of the day, 24/7. But there is a buffer of 5 minutes after every tour to avoid delays.

I generate the end date by adding the duration of a tour to the start time. I searching in the reservations table for all taken guides with a BETWEEN where clause like this:

SELECT guider_id FROM reservations as r
WHERE 
 (((r.startDateTime - interval 5 minute) BETWEEN userStartDateTime AND userEndDateTime)) OR
 ((r.startDateTime + interval r.duration minute + interval 5 minute) BETWEEN userStartDateTime AND userEndDateTime))

Afterwards I use "NOT IN" on the guiders table to find available guiders (like mentioned every guider is available at every time of day). If i find one: yeah, reservation successfull. If not...I need to find a date/time where one is available.

That is where i am stucked. I do not want to create a table where every available date time combination is stored because that is hell of a memory waste and means hell of a lot work and performance. Because every time of the day could be choosen. Which means, with every reservation i need to re-arrange the whole table for that day. That table would hold already 1440 entries for a day and for a year (assuming 360 days) 518400. To fill that table for the upcoming ten years...you can calculate yourself. That's not even worth thinking about it.

So what i need to do now is: i need to find a new date/time within the range from the userStartDateTime. First forward (preferred) and if no free guider has been found by that backward from that.

So how may i do this?

I just have no working idea about that. Just adding the range to my calculated end date would not work, 'cause it would catch to much guiders which have a tour starting and finishing within it.

Greetings func0der

Community
  • 1
  • 1
func0der
  • 2,192
  • 1
  • 19
  • 29
  • A calendar file (filled only with `date`s) is a useful thing, and I't still recommend creating it. Other than that, it's usually recommended to avoid the use of `BETWEEN` (especially for dates/times/timestamps) and instead use an exclusive upper bound. – Clockwork-Muse Feb 03 '12 at 00:34
  • You're going about this backwards, I think - instead of looking for times where you have a guide free, look for a guide who has sufficient time free; perform gap analysis to find all gaps of sufficient duration (within the specified range, and you'll have to generate 'phantom' entries for upper end dates), and sort by absolute interval from original/desired start time. mySQL doesn't have CTEs, so I don't want to bother writing this... – Clockwork-Muse Feb 03 '12 at 00:44
  • Okay. I am no pro in mysql. So i would like you to explain "exclusive upper bound" and "CTE". And do you have an example for the "gap analysis" you were takling about? – func0der Feb 03 '12 at 11:06
  • Exclusive upper bounds are used to avoid off-by-one errors, especially with dates - to get all dates within a specified month: `WHERE dateCol >= @monthStart AND dateCol < @monthStart + 1 MONTH` - this is less problematic then attempting to find the _end_ of the month, and is even more relevant for timestamps. CTEs (Common Table Expressions) are basically creating inline temp tables, extracted to a single reference - the closest mySQL comes is `SELECT * FROM (SELECT ...)` - except I only have to declare the inner select _once_, but can reference it multiple times. – Clockwork-Muse Feb 03 '12 at 16:35
  • A good example of gap analysis can be found on this [question](http://stackoverflow.com/q/1315262/812837), along with the answers. Unfortunately, mySQL may not have some of the better functions to use... Also, please note that this type of analysis is inherently 'expensive' - and it's not something you can dodge in this case. – Clockwork-Muse Feb 03 '12 at 16:42
  • I still don't get the use of that exclusive upper bound 'cause while i'm creating the date with php and unix timestamps that just don't make any sense. Also i thought timestamps are kind of deprecated because of the 4bit use only? Couldn't you solve the CTE problem with a on the fly view? – func0der Feb 04 '12 at 00:37
  • I'm not sure about the use of mySQL's timestamps, but in most other dialects they're accurate to the nano-second - which would be annoying to have to write out completely, to say nothing of the fact that midnight not exist for the specified date (daylight savings time). If by 'on-the-fly view' you mean running a `CREATE` statement, the beauty of a CTE is that it's scope is _only_ for the `SELECT` statement it's a part of; although if sufficient statements are using a particular set of code, you can extract it to a view. – Clockwork-Muse Feb 06 '12 at 16:34
  • You're right about the accuracy of the timestamps. But try this one in on your mysql server "SELECT UNIX_TIMESTAMP('2039-12-11');". So using that means to rewrite the whole application about after 2037. I know that this is very far away in the future and this earth ends this year anyway, but that's exactly the reason i picked date time. I found a solution to all that now by ignoring my question a bit. I used another table to cache the free times for each guider. I will do a full report here, to show what i mean by that. – func0der Feb 06 '12 at 19:04
  • It doesn't necessarily have to be a unix timestamp, just a timestamp in general - the one on DB2 is good until `9999-12-31` (because it's using a character field, not a numeric-based field). And a timestamp based on a `long` that counts micro-seconds (goofed on my unit-of-measure earlier) is good for 200,000+ years – Clockwork-Muse Feb 06 '12 at 19:24

1 Answers1

1

What about creating a new table called scheduledTour (just for example). Then just UPDATE the table whenever you need to add a new guided tour, or remove one. So, essentially, when the table would first be made it would be completely empty.

The scheduledTour table would have information like: tour_id, guider_id, customer_id, startDateTime, endDateTime.

The tour_id would just be an auto-increment INT value that you can use to keep entries unique. The guider_id is the one you mentioned in your question, it would reference the assigned guider from the table you already have, and the same with customer_id (if you have a customer table. If not you can replace w/ ticket number, name, etc.).

This way there is no limit to how few or many scheduled tours, and you can easily create an add/drop page. You can pull the tours for specific guides, all of the tours, or tours w/ specific start and end dates/times.

Also, if all tours have a 5min. buffer, then just add that to the start and end times when you create the tour.

For example, in your "UPDATE" statement right after the date you are entering:

... DATE_ADD([startDateTime], INTERVAL 5 MINUTE) ...

Hope this helps!

Khasm08
  • 236
  • 3
  • 10