Booking system dates in database

Question

I need some help with the following:

I am setting up a booking system (kind of hotel booking) and I have inserted a check in date and a check out date into database, how do I go to check if a room is already booked?

I have no clue how to manage the already booked days in the database. Is there anyone who can give me a clue how this works? And maybe where I can find more information?

Answer 1

Well, I didn't understand very well your question, but my suggestion is to you to add a state field, in which you can control the current state of the "booked" item. something like

• Available
• Under Maintenance
• Occupied

Or whatever bunch of states that work for you.

[EDIT] The analysis that I use to do for that case is as follows: Take for instance, your room is currently booked with these date range:

• Init Date: Feb 8
• End Date: Feb 14

Success Booking Examples

• Init Date: Feb 2

• End Date: Feb 6

• Init Date: Feb 15

• End Date: Feb 24

You should check that the booking attempt satisfies these conditions:

1. Neither "Booking Init Date" nor "Booking End Date" can be inside of the already booked date.

Example:

• Init Date: Feb 2

• End Date: Feb 10 (Inside the current range (Feb 8 to 14))

• Init Date: Feb 12 (Inside the current range (Feb 8 to 14))

• End Date: Feb 27

• if "Booking Init Date" is less than current init date, "Booking End Date" should also be less than current init date

• Init Date: Feb 2.

• End Date: Feb 27 (Init date before, but end date later)

Answer 2

It gets a little more complicated than what you're describing. Let's assume you have three types of rooms (One King Bed, Two Queen Beds, Luxury Suite). You probably won't be interested in booking for a particular room, rather if >= 1 room of a desired class of room is available for the timespan in question.

Start with a solid database structure. For brevity's sake, I'm just listing fieldnames, hoping the data types for each are obvious. Here's what initially comes to mind:

RoomClass: (RoomClassID, DisplayName)
Room: (RoomID, RoomClassID)
Booking: (RoomClassID, ArrivalDate, DepartureDate)

I'd create a procedure that would calculate the number of available rooms for a particular class for a day:

int GetRoomCount( RoomClassID, Date )

Then, to determine availability, iterate over this function for each day of the guest's visit, bearing in mind that the ArrivalDate is typically inclusive and the DepartureDate is typically exclusive. That is, a visit from November 1 through November 4 is for the nights of November 1, 2 and 3, but not the fourth.

Answer 3

This is quite a complex problem. There was a very similar SO question (about booking holiday cottages) just yesterday. There you will see some example solutions that you may be able to adapt.

Answer 4

This is an interesting question - not least because I don't believe that there is a single ideal answer as it will depend to some extent on the nature of the "Hotel" and the way in which people are placed in rooms and to a further extent on the scale of the question.

At the most basic level you have two ways that you can track occupancy of rooms:

1. You can have a table with one row per room per day that defines the state of that room on that date (and possibly the related booking if occupied)
2. For each room you maintain a list of "bookings" (which as already suggested will have to include states for when a room is unavailable for maintenance).

The reason that its an interesting question is that these both immediately present you with certain challenges when maintaining the data and searching for occupancy in the case of the former you're holding a lot of data that may not be needed and in the case of the latter finding gaps in the occupancy for new bookings is perhaps more interesting.

You then (in both cases) have a futher problem of resource allocation - if your bookings tend to be for two days and you system results in 1 day gaps between bookings you're going to need to re-arrange the occupancy to optimise usage... but then you have to be careful with bookings that need to be explicitly tied to specific rooms.

Ideally you would defer assigning a booking to a room for as long as possible (which is why it depends on the hotel - fine for 400 modular rooms rather less so for a dozen unique ones) - so long as there are sufficient rooms of the necessary standard available (which you invert, so long as there are fewer rooms booked than real rooms) during a target period you can take the booking. Of course you've still then got to represent the state of the rooms so this is in addition to the data you've got to manage.

All of which is what makes it interesting - there is considerable depth to the problem, you need to have a fairly decent understanding of the specific problem to produce an appropriate solution.