I had been lately requested a job interview question on the hypothetical internet based booking system and just how I'd design the database schema to reduce duplication and maximize versatility.

The utilization situation is the fact that a admin would go into the accessibility to a house in to the system. There might be multiple period of time set. For instance, first of April 2009 to 14th of April 2009 and 3rd of This summer 2009 to 21st of This summer 2009.

A person will be only able to position a booking within the periods provided of equal or shorter periods.

How does one store these details inside a database?

Can you use something as basic (really simplified) as

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

Would you then easily create a web site that demonstrated a calendar of availability with periods which have been reserved blanked out. Will it be simple to build reviews out of this database schema? Could it be as simple as it appears?

It may be simpler to utilize just one table for availability and booking, having a granularity of just one day:

property_date (property_id, date, status);

Column status might have (a minimum of) the next 2 values:

  • Available
  • Reserved

Entering a time of availability e.g. first to 14th of April would entail (the applying) placing 14 rows into property_date each having a status of 'Available'. (Towards the user it will appear just like a single action).

Booking the home for that period 3rd to eleventh April would entail checking that the 'Available' row been around for every day, and altering the status to 'Booked'.

This model may appear a little "verbose", however it has some advantages:

  1. Checking availability for just about any date is simple
  2. Adding a booking instantly updates the supply, there is not another Availability table to help keep synchronized.
  3. Showing availability inside a web site could be quite simple
  4. You can easily add new statuses to record various kinds of unavailability - e.g. closed for maintenance.

NB If "available" is easily the most common condition of the property, it might be easier to turn back logic to ensure that there's an 'Unavailable' status, and the lack of a row for any date means it's available.