Tutorials

Resource Booking

A common application requirement is resource booking. The resource may be a squash court or driving lesson booked by the hour; it may be an hotel room or hire car booked by the day; it may be a holiday villa booked by the week or the month. Whatever the time period, the principles are the same. You need to know what resources you have and keep a record of the times they are booked and by whom.

Requirements

For this tutorial we will consider a room reservation application for Mrs Beetsem-Daley's latest acquistion, an hotel which she recently inherited.

The hotel has six room sizes (single, double, twin, family, junior suite and suite) and three grades of accommodation (standard, executive, comfortable). Each of these has a different price per night. Friday and Saturday nights have prices different from the other weekdays. The owner would also like higher prices over the holiday weekends. As an hotel room is one of the most perishable items on the market, reception staff are authorized to negotiate a discount for late bookers rather than having an empty room.

The rooms also have various facilities. Some have a jacuzzi, some have a fridge and a safe etc.

More than one room may be selected and booked by the client (eg double for the parents and a twin for the kids).

Available Resources?

We have a choice of methods for storing a room booking. Supposing we have a booking for Room 123 for guest arriving on June 3rd and departing on June 6th then we can store in a bookings table as

  • Method A
            +------------+---------------+---------------+
            |   Room     |    Check In   |   Check Out   |
            +------------+---------------+---------------+
            |    123     |  2016-06-03   |  2016-06-06   |
            +------------+---------------+---------------+
    
  • Method B
            +------------+---------------+
            |   Room     | Date Occupied |
            +------------+---------------+
            |    123     |  2016-06-03   |
            |    123     |  2016-06-04   |
            |    123     |  2016-06-05   |
            +------------+---------------+
    

Using Method A

In the diagram below we have a client wanting to to book a resource between A and B. As we can see, resources 2, 3, 4 and 5 are already booked for part or all of this period and therefore they are not available. A booking overlaps the required period A-B if the booking starts before B and finishes after A

(Sn < B) AND (En > A)

Available resource conditions  © Barry  Andrew 2016

In SQL terms, if we have a resource table and a booking table, then a query like this will find available items between dates A and B

    SELECT r.resource_id
         , r.description
    FROM resource r
        LEFT JOIN booking b 
            ON r.resource_id = b.resource_id
            AND b.start_date < 'B' AND b.end_date > 'A'
    WHERE b.resource_id IS NULL

Using Method B

Although it requires more records, this method does have its advantages.

  • It's normalized, so matching against other tables is simpler
  • We can put a unique key constraint on room/date combination to prevent accidental double-booking

From an SQL viewpoint, the query is similar to method A

    SELECT r.resource_id
         , r.description
    FROM resource r
        LEFT JOIN booking b 
            ON r.resource_id = b.resource_id
            AND b.date >= 'A' AND b.date < 'B'
    WHERE b.resource_id IS NULL

Based on these requirements, we now have an initial data model

bookings data model © Barry Andrew  2016

Processing

Check-in Date and Number of Nights

When booking, the user will select a booking start date and the number of nights required.
checkin date form ©  Barry Andrew 2016
A list of available rooms, if any, will be displayed. The list will show the room type (eg executive double), facilities and the total price for the stay.
rooms available © B Andrew 2016

If no rooms are available, display a calendar grid listing all rooms and their availablity. This grid should also be available on request. (The arrows show the start of each booking)
room calendar © 2016 Barry Andrew

From the supplied check-in date and number of nights we need to do two things

  • calculate the check-out date
  • create a temporary table containing a date for each night

So if the check-in date is 2016-06-03 and the booking is for 3 nights then

  • check-out date = 2016-06-06
  • temporary table will contain
    • 2016-06-03
    • 2016-06-04
    • 2016-06-05

The initial processing will be


    /*********************************************************
    * Process the input and get the checkin and checkout dates
    **********************************************************/
    $checkin = isset($_GET['checkin']) ? $_GET['checkin'] : '';
    $nights  = isset($_GET['nights'])  ? (int)$_GET['nights']  : 1;
    if ($nights < 1) $nights = 1;

    $today = new DateTime();
    $dt1 = new DateTime($checkin);
    if ($dt1 < $today) $dt1 = $today;

    $di = new DateInterval("P{$nights}D");
    $dt2 = (new DateTime($checkin))->add($di);

    $checkin = $dt1->format('Y-m-d');
    $checkout = $dt2->format('Y-m-d');

    /********************************************************
    * Create the temporary table and write the dates to it
    *********************************************************/
    $db->query("CREATE TEMPORARY TABLE tempbook (night DATE)");

    $sql = "INSERT INTO tempbook VALUES (?)";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('s', $date);

    $dp = new DatePeriod($dt1, new DateInterval('P1D'), $dt2);  // create list of dates
    foreach ($dp as $d) {                         // loop through the dates in the list
        $date = $d->format('Y-m-d');
        $stmt->execute();                      // insert date
    }


When we cross join the temporary table with the room table we will have, for each room, records for each night of the stay. Matching against the roomprice table we get the price for each night.

This table subquery, using the temporary table, will calculate the room prices for the planned stay ...

    SELECT 
        r1.room_id
      , SUM( CASE dayofweek(night)
            WHEN  6 THEN wkend_price    -- friday
            WHEN  7 THEN wkend_price    -- saturday
            ELSE wkday_price
        END) as price
    FROM room r1 
    CROSS JOIN tempbook
    INNER JOIN roomprice p 
        ON p.grade_id = r1.grade_id
        AND p.size_id = r1.size_id
        AND night BETWEEN p.from_date AND p.to_date
    GROUP BY r1.room_id

... and we'll use the check-in and check-out dates when we match against the bookings to find which rooms have no bookings between those dates

    SELECT
        .
        .
    FROM room r
    LEFT JOIN room_booking rb ON r.room_id = rb.room_id 
        AND rb.date >= '$checkin' AND br.date < '$checkout'
    WHERE rb.room_id IS NULL

Booking the Room

Our list of available rooms has checkboxes so the user can select the room for booking. The user chooses the room(s) and the rooms are reserved by inserting records into the room_booking table for each of the required nights.

However, there is a potential problem!

At the time the list of rooms was displayed, the rooms were available, but, between then and now it is possible that someone else could have reserved one or more of these rooms. We are going to use AJAX to update the room_booking table and give instant feedback on whether the reservation was successful ...
reservation accepted ©2016 Barry Andrew

... or if it failed ...
reservation rejected © 2016; Barry  Andrew
... when the user clicks a box to select a room.

The AJAX call

On clicking the checkbox for a room, the following data is posted to the booking process on the server

  • Booking id (0 if we don't have one yet)
  • Room id
  • Check-in date
  • Number of nights
  • Check box value, Book (1) or Unbook (0)

The booking process returns

  • the booking id (which is stored in a hidden form field)
  • the room id. The row for the room is highlighted to indicate success or failure
  • success flag (or or 1)
  • the booked or unbook value. If unbooked and successful, the row for the room is reset

The Booking Process

We created a unique key constraint on the room_booking so we will get a duplicate key error if we try to insert a new record for a room that is already booked for that day. This is the pseudocode for the booking process.

    start SQL transaction
    try
        if checkbox value is 1        //book the room
            if booking id  = 0
                create booking record
                booking id = id of new record
            end if
            for each night booked
                insert booking id, room id and date into room_booking table
            end for
        else                          // unbook the room
            delete the room_booking records for this (bookingid,roomid)
        end if
        commit the transaction
        set success flag to 1
    catch SQL exception
        rollback the transaction
        set success flag to 0
    end try/catch
    
    output the return values

Confirmation

The final part of the process is to get, and store, the client details. At this stage you might also want to get and validate a payment card number. Even if you don't take the money immediately, you will want to charge the card on checkout or in the event of a no-show. Handling card payments is outside the scope of this tutorial but I would advise you use a third party payment gateway (eg Paypal, WorldPay) and do not store the card details yourself.

Once the client details have been validated and verified, write a record to the client table and get the id of the inserted record. Write this client id to the booking record.

Releasing expired reservations

Users may have reserved rooms and then abandoned the process or their details failed validation. Whatever the reason, we may end up with booking records that are not confirmed with a client id. We will make room reservations valid for 15 minutes. After this time records can be removed from the room_booking table if the matching booking record has no confirming client id.

This releases those rooms back into the available rooms stock for resale.

    DELETE rb
    FROM booking b 
    INNER JOIN room_booking rb 
        ON b.booking_id = rb.booking_id
    WHERE b.client_id IS NULL
        AND b.time_booked < NOW() - INTERVAL 15 MINUTE

This cleanup process will be run just prior to our query to find available rooms.