How to implement check availability in hotel reservation system

Question

I am developing website for my friend's very small resort, please see www.shivgangaresorts.com/dev

This resort has 8 rooms in total and all rooms are of the same type, so every room is equally priced.

I just need code or logic to check availability of given no. of rooms on given check-in and check-out dates.

When somebody makes a reservation, details that go in reservation table are:

• booking_id
• customer name
• email
• check-in date
• check-out date
• no. of rooms

Please help. This is very critical as I will be integrating PayPal also, so there should be no confusions...

Answer 1

If all rooms are available for 24 hours a day, 7 days a week then your reservation should have a start_time (timestamp) and end_time (timestamp) rather than just the date.

A standard reservation likely has a specific start time (~6pm) and ending (~11am) but you may want to be specific in order to offer early arrivals or late checkouts.

This is the logic I'd user:

1. User picks check-in date and check-out date.

2. Your program grabs 6pm of check in date and 11am of checkout date and selects the rooms are not booked within those times based on the current reservations. (return rooms)

This sounds like a fairly simple approach but you may need to account for maid times, splitting up the reservation between rooms, rooms on hold, etc.

Answer 2

You need a procedure to determine how many rooms are available. The simplest answer for a small number of rooms would be to loop through each room and check availability during the period. That is, if a booking exists for a given room during the period, it's not available.

If you want to consider splitting a booking between rooms, it becomes more difficult, but you should then check availability for each room for each day during the period, then aggregating the results.

Answer 3

Here is an existing one, but IIRC, the source code is Italian

Here is a small tutorial which was a good starting point for when I wrote one a while back

Answer 4

I guess if you're checking the availability on every page refresh, I'd compute how many free rooms by grabbing how many bookings are in effect at the current time:

select sum(number_of_rooms) from reservations where
  check_in_date <= now() and
  check_out_date >= now()

This will give you your current rooms that are occupied. Then simply subtract this value from a hard-coded 'room total' value.

It might help to normalize the database by having a customer and individual room table with a linking table to the booking table. However I am unaware of your contraints.