Building a hotel reservation system

Lets say you run a mini-hotel and need to build a very simple reservation system. In this article we will show how avoid double-booking only showing the rooms that are available for selected date range. Similar approach can be applied to any other reservation system i.e. if you need to build conference rooms reservation app.

In our database we need two tables, Rooms and Reservations.

Rooms table simply stores a list of rooms.

Each reservation is a record in Reservations table.

We can see that if someone comes and tries to reserve a room for one night, September 14-15, they should only see rooms 17 and 33. Rooms 23 and 27 are already booked for these days.

If we were to write this kind of select query manually this is how it supposed to look:

The only problem is that this query needs to be dynamic and needs to change based on DATEFROM and DATETO fields. SQL variables come to the rescue. In RoomID Lookup wizard we can use SQL variables in WHERE clause. This is how it is going to look:

And as a text that you can copy and paste to your project:

id not in ( select roomid from reservations where not
	 (':dateto' < datefrom OR ':datefrom' > dateto) )

This is all the code you need. And it works the same way in PHPRunner, ASPRunner.NET and ASPRunnerPro.

Here is how it looks in generated application:

More about SQL variables:
PHPRunner manual
ASPRunner.NET manual
ASPRunnerPro manual

And here is the live demo project. Go to Add Reservation page and play with dates to see how it works.