advice on code for checking availability

Absolute

Hi,

I got a php coder to develop a simple php / mysql reservation system for a car park and I’d like some advice for a design solution for a requirement for availability checking.

What I mean is when a user selects dates and hit the “check availability” button and the db is queried for availability, and outputs a result depending on whether space is available or not.

The requirement would be fairly straight forward if space was allocated in a per day basis (like an hotel rooms booking system for example), however space and booking are monitored in 30min segments each day (cars can be picked up at any time of day or night).

So if a car was booked in for 1 day the code would have to check 48 segments for a day for availability.

If a car was booked in for a week – the db would have to be queried 48 segments a day * 7 days = 336 queries. I guess this would be sending too many queries to the db?

Looking for advice or ideas about how this might be coded?

Cheers..any advice would be appreciated.

thanks in advance!

etully

The way that I would handle it (and this might not be a good technique) would be to have the reservation system store the time that I am reserving the space.

For example: When I reserve parking space #27 for a 1/2 hour period at 2:00 pm on Thursday, March 6, 2010, I would convert that time to Unix time like this:

$enter_time = mktime(14,0,0,3,6,2010);
$exit_time = mktime(14,30,0,3,6,2010);

And then in the reservation database, I would have two fields, one for enter time and one for exit time.

Now, When someone wants to reserve the car for 6:30 am on Wednesday, June 23, 2011 for a 3 hour period... I find the enter time AND the exit time of the desired reservation and I compare each of those to the times in the database.

Show me any records where my $desired_enter_time is > a previously reserved enter time AND my $desired_enter_time is < its previously reserved exit time AND my $desired_exit_time is > a previously reserved enter time AND my $desired_exit_time is < its previously reserved exit time.

So you are just performing one query instead of 336. It there are any results, then the space is booked for that time. If there are zero results, then the space is free for the whole 3 hour window of my desired reservation.

Bjom

You can have SQL handle the date comparison and save yourself the conversion to unixtimestamp, other than that I would suggest the same as etully.

Somehow reminded me of the example for the Boyce-Codd normal form about tennis court reservations that you can find here on wikipedia. Maybe it can give some inspiration.

Bjom

Absolute

Thanks for this just trying to understand the logic. I'm a little confused.
From what I can tell.... correct me if I'm wrong... this is just cheking the number of booking at my time of entry and the number at my exit time.

I ENTER AFTER THEM
$desired_enter_time is > a previously reserved enter time AND my
I ENTER BEFORE THEY EXIT
$desired_enter_time is < its previously reserved exit time AND my
I EXIT AFTER THEY ENTER
$desired_exit_time is > a previously reserved enter time AND my
I EXIT BEFORE THEM
$desired_exit_time is < its previously reserved exit time.

The first two statement would mean:
the number of bookings when i enter
and the third and forth would mean:
and the number of bookings while I exit.

The problem is I could be booking in for 2 weeks. Even if there are only 10 spaces, there could be 100s of other reservations booked in half hour segments during this two week period.

I don't think this resolves the problem unless I'm missing something.

bpat1434

Think of it this way. If you have one table which holds reservations, and you have a static number of spots (we'll use 100). Each spot can be reserved in 30 minute increments (as you specified).

Jon reserves spot #83 on August 24, 2009 from 12:00pm until August 31, 2009 at 11:30 am.
Jane reserves spot #84 on August 24, 2009 from 12:00am until August 31, 2009 at 11:00 pm.

Now, say that Dave wants spot #83 (because it's near the pool on the roof of the building). He's planning a trip from August 14 at 8:00 am through August 28 at 11:00 am(a two week window). So Dave comes to your site and says "I want spot #83" and enters his start date and end date into your form.

Your code then takes his start date and looks for any record in your database where someone is already there (e.g. their exit time is > Dave's start time). You'll also need to check to make sure that Dave's exit time doesn't overlap with someone who has already booked a slot for later (e.g. Dave's exit time is > the other persons start time).

Essentially you'd be looking for a query like:

SELECT `id`
FROM `reservations`
WHERE (
    (
        UNIX_TIMESTAMP('{$daves_start}') < UNIX_TIMESTAMP(`exit`)
    )
    OR
    (
        UNIX_TIMESTAMP('{$daves_end}') > UNIX_TIMESTAMP(`start`)
    )
)
AND
(
    `spot_id` = {$daves_requested_spot}
)

Now, to hopefully make this more clear for you...

Say that Jon reserves spot #15 from December 18 at 4:00 pm until December 30 at 8:30 am. Then Jane comes in and reserves spot #15 from January 6 at 8:00 am until January 20 at 11:00 am.

There is a one week window where spot #15 is open (between December 30 at 8:30 am and January 6 at 8:00 am). Say Dave comes along and is planning a 2-week vacation which spans the new year from December 20 at 12:00 pm until January 3 at 8:00 am.

When Dave goes to check the availability for spot #15, it would be caught because Jon already reserved it until the 30th, and Dave can't overlap his start date with Jon's end date. But Dave could keep his end date since Dave will vacate that spot prior to Jane taking over.

Now, if Dave tries to rework his plans so he leaves on December 30 and decides to want spot #15 on December 30 at 3:00 pm until January 12 at 8:00 am, he would again be stopped because his ending time overlaps with an already scheduled start time (Jane's).

The only way Dave could get that spot over the holiday would be to take it from December 30 at 8:00 am and hold it until sometime before January 6 at 8:00 am.

Hope that makes it clearer.

Absolute

Hey, thanks for this but you cannot assign booking to specific spaces like that.
Users do not have the choice of picking a specific space.

In your case that would mean if for example.
Lets say there are 4 customers ….P1 to P4 and 3 car park spaces
P1 needs parking on tue
P2 needs parking on wed
P3 needs parking on thur
P4 needs parking mon to fri

P1 is assigned space 1, P2 is allocated space 2 and P3, space 3
P4 now cannot book as there is no free space.

We need to know if there are ANY space available during booking period (not specific spaces)

etully

Absolute: You are taking us a little too literally. BPat's advice happens to be for checking a specific space... but that doesn't mean that that's the only way to use the code.

We were showing you how to check for a specific space because that was your specific question... if you want to run a loop and check availability for all the spaces, there's nothing wrong with that.

By your definition in your original post, people can reserve time down to 30 minute increments and you wanted to know how you could write the program without having to perform a query for every single 30 minute slot. Our technique shows how you can check for availability with one single query instead of 48 segments a day * 7 days = 336 queries but we didn't write the whole computer program for you. Now that you know how to check for "collisions", you can use that technique in whatever logic you want.

So first of all, let's go with your assumption that there are 3 spaces and:
P1 has reserved space #1 on Mon
P2 has reserved space #2 on Tues
P3 has reserved space #3 on Wed
Then your programming flow would look like this: To handle customer P4, we need a space that is open Mon-Fri. desiredTimeIN=Mon, desiredTimeOUT=Fri. Start loop through all three spaces. Check space 1. Perform exactly one query to see if there are any reservations in Space 1 where desiredTimeIN or desiredTimeOUT interfere with anyone else's reservation. If yes, then finish loop and move on to space 2. Complete loop for all spaces. If you get to the end of all the spaces and there were collisions in every parking space, then we can't accommodate this customer. And in your assumptions (P1 has reserved space #1 on Mon, P2 has reserved space #2 on Tues, P3 has reserved space #3 on Wed), then threre would be a collision in every space so we have to tell P4 that we can't fit him in. So the program works just as you wanted it to.

But here's the thing: How on earth did P2 get to reserve space #2 on Tues? And how did P3 reserve space #3 on Wed? That's shouldn't have been allowed to happen because it wastes parking spaces.

If you had used the loop I just described when picking a space for P2 and P3, then they would have each been assigned to space #1. That way, when P4 comes along and wants a space from mon-fri, then space #2 would have been available (and space #3 would have been available too).

So let's look at it from the beginning:
There are no reservations yet.
P1 wants a space on Monday.
We start the loop through all the spaces, starting with space #1.
Since there are no reservations yet, space 1 is open on Monday.
Put P1 in space 1 on Monday.
P2 comes along and wants a space on Tuesday.
We start the loop through all the spaces, starting with space #1.
Is space 1 available on Tuesday? Yes. Give him space one.
P3 comes along and wants Wednesday.
Start loop at space #1.
Is it available on Wednesday? Yes. Give him space 1 on wed.
P4 comes along and wants Mon-Fri.
Start loop at space #1
Are there any reservations that would collide with a Mon-Fri reservation.
Yes, there are one or more reservations (three actually) that would interfere.
Finish loop and move on to the next parking space in line (#2)
It's open. Give him space #2 from Mon-Fri.

Writing the code for your project, you can do that however you want. All we did was show you how to reduce the number of queries so that you don't have to check every single 1/2 hour slot for every single day (336). But how you choose to write your entire reservation system, that's up you.

Absolute

thank etully, thats helpful...
I guess a solution like this might work, the only issue might be that there are approx 1000 spaces.

etully

Well, that's where some clever coding will come into play. You might design the query to say, "Show me the number of spaces where there would be a collision. If mysql_num_rows() returns 1000, then there are 1000 parking spaces where the newly desired reservation won't fit... so we can't help this guy and you can skip the whole process of looping through all the spaces (which reduces server load). If the number of rows is less than 1000, then there is at least one space that will fit so you start looping through the spaces (starting with #1) to find one that will accommodate the desired reservation. Even if there are 1000 spaces, you probably won't have to check every single space to find one that's open. Of course, eventually, on a really busy week, you will have to loop through all 1000 spaces, and when that day comes, you will be thankful that you only had to perform 1000 queries instead of 336 slots per day * 1000 spaces = 336,000 queries. And your server will have the resources available to perform all 1000 queries because of all the efficiency you've picked up in other parts of your programming.

Or another way to look at it is what some people call, "the good problem to have". If you are taking so many reservations that your computer is overloaded trying to find an open space, then you are making enough money to buy a more powerful computer (or enough money to hire me to add further efficiency to your programming!) Too much business is a pretty sweet problem to have. 🙂

bpat1434

The problem could also be reversed. Instead of "checking" to see how many slots are taken, you can see how many slots are empty. A more complex query to run yes; however, in one query you could pull back the IDs of the spots you want and know in one query which spots are available and assign them to the first available (or present them with a list of options).