CODEWITHSUNDEEP
databasedatabase-designschema
user56062
user56062

Reputation: 369

Best practice database schema for property booking calendar

I am working on a multiple properties booking system and making me headache about the best practice schema design. Assume the site hosts for example 5000 properties where each of it is maintained by one user. Each property has a booking calendar. My current implementation is a two-table-system with one table for the available dates and the other for the unavailable dates, with a granularity of 1 day each.

property_dates_available (property_id, date);

property_dates_booked (property_id, date);

However, i feel unsure if this is a good solution. In another question i read about a single table solution with both states represented. But i wonder if it is a good idea to mix them up. Also, should the booking calendar be mapped for a full year with all its 365 days per year into the database table or was it better to map only the days a property is available for booking? I think of the dramatically increasing number of rows every year. Also i think of searching the database lately for available properties and am not sure if looking through 5000 * 365 rows might be a bad idea compared to i.e. only 5000 * av. 100 rows.

What would you generally recommend? Is this aspect ignorable? How to best practice implement this?

Upvotes: 1

Views: 2477

Answers (1)

No'am Newman
No'am Newman

Reputation: 6477

I don't see why you need a separate table for available dates. If you have a table for booked dates (property_id, date), then you can easily query this table to find out which properties are available for a given date

select properties.property_name
from properties where not exists
(select 1 from property_dates_booked
 where properties.property_id = property_dates_booked
 and property_dates_booked.date = :date)

:date being a parameter to the query

Only enter actual bookings into the property_dates_booked table (it would be easier to rename the table 'bookings'). If a property is not available for certain dates because of maintenance, then enter a booking for those dates where the customer is 'special' (maybe the 'customer' has a negative id).

Upvotes: 1

Related Questions