advice for storing reservation/booking date ranges...

I am building a small app that will display availability data for properties. I have a calendar that displays the dates for a whole year in month rows with each days colour representing the availability status, i.e. booked, on hold etc.

My question is about how to store these dates in the db. At the moment I have the table below:

TABLE Availability
[PropertyID] [int] NOT NULL ,
[StatusID] [tinyint] NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NOT NULL

I was planning on having four status's for any given date, unknown, available, on hold or booked.

Displaying the dates has proved pretty simple but updating availability means I would need to query the db to see if any of the dates overlapped, I would then have to add the new date range/status as well as change any date ranges that overlapped, either in the sp or in the code and this is what made me wonder if there was a better way.

Does this sound a reasonable approach? Any advice or pointers would be greatly appreciated, This is the first time I have had to store date ranges and I want to make sure I am doing it right.

Instead of an Availability table, I would think you'd want a Booked table. Assume the property is available and create records in the Booked table when it's booked or held. Don't create records if you'd have an overlap by doing so. If no record for a time frame, then it's available.

It seems the way you're doing it, you would have to create a record for every date for every property. That makes it tough to be more granular if you need to go to the hour or minute level.

|||

Yes, unfortunately I have to cater for users who are not keeping the calendar uptodate hence using both the unknown and available status.

The default will be unknown, so anything not in the table will be that status and then it is up to users to use the calendar to update the properties availability, so there are three status's, available, on-hold and booked. These three will be in the table.

I think I will proceed as is and see how I go...thanks for the input.