Database Schema - Booking/Availability System

Question

I was recently asked an interview question on a hypothetical web based booking system and how I would design the database schema to minimize duplication and maximize flexibility.

The use case is that a admin would enter the availability of a property into the system. There could be multiple time period set. For example, 1st of April 2009 to 14th of April 2009 and 3rd of July 2009 to 21st of July 2009.

A user is then only able to place a booking in the periods made available of equal or shorter periods.

How would you store this information in a database?

Would you use something as simple (really simplified) as;

AVAILABILITY(property_id, start_date, end_date);
BOOKING(property_id, start_date, end_date);

Could you then easily construct a web page that showed a calendar of availability with periods that have been booked blanked out. Would it be easy to build reports from this database schema? Is it as easy as it seems?

Answer 1

It might be easier to work with a single table for both availability and booking, with a granularity of 1 day:

property_date (property_id, date, status);

Column status would have (at least) the following 2 values:

• Available
• Booked

Entering a period of availability e.g. 1st to 14th of April would entail (the application) inserting 14 rows into property_date each with a status of 'Available'. (To the user it should seem like a single action).

Booking the property for the period 3rd to 11th April would entail checking that an 'Available' row existed for each day, and changing the status to 'Booked'.

This model may seem a bit "verbose", but it has some advantages:

1. Checking availability for any date is easy
2. Adding a booking automatically updates the availability, there isn't a separate Availability table to keep in sync.
3. Showing availability in a web page would be very simple
4. It is easy to add new statuses to record different types of unavailability - e.g. closed for maintenance.

NB If "available" is the most common state of a property, it may be better to reverse the logic so that there is an 'Unavailable' status, and the absence of a row for a date means it is available.