What is the best database schema for an availability calendar that allows scheduling appointments(reoccurring and single))

Question

In my application I have a provider that has a schedule and clients that book appointment from the schedule. I need the following features.

Provider: - Be able to specify reoccurring availability. For example Mon 9-5, Tues 10-3, etc. - Be able to black out datas. For example - not available on this Mon. - Be able to add single, not reoccurring dates/times slots. For example - This Sat 9-5.

Customer: - Be able to book single appointments. - Be able to book reoccurring appointments. (Every Mon 9-4).

So far I came up with 3 options:

1. Divide the schedule into 30 min intervals and create a database entry for each interval/provider pair. Each interval can be either free or booked. When a customer books an appointment we mark the intervals as booked. The problem with this approach is that it wastes a lot of space, and I am not sure how good the search performance would be for a reoccurring booking.
2. Save each availability period as an "event". If it is reoccurring, duplicate the event. When searching for free slots search the booking table to make sure that there is no over lapping booking. In this case, searching for reoccurring slots seems a bit awkward. To find all the providers that are available on Mon 9-5 for the next year we will have to search for all the matching 'events' and find all the providers that have 52 matched events.
3. Save each availability period as an "event". Add a flag if it is reoccurring.When searching for free slots search the booking table to make sure that there is no over lapping booking. It makes it easier to search for reoccurring appointments. To "black out" slot that are suppose to be reoccurring we can just insert a fake booking.

Answer 1

1.Create a event table:

a) With the basic columns eventdate, starttime, endtime, with other details for the event - these are the busy times so are what you block out on the calendar

b) Recurring Events - add columns: - isrecurring - defaults to 0 - recurrencetype (daily, weekly, monthly) - recurevery (a count of when the recurrence will occur) - mon, tue, wed, thur, fri, sat, sun - days of the week for weekly recurrence - month and dayofmonth - for monthly recurrence

2.The challenge comes when creating the recurring events on the calendar: - if you create all of them at once (say for the next 6 months), whenever you edit one the others have to be updated - If you only create an event when the previous one has passed then you need complex logic to display the calendars for future dates

3.You also need rules to take care of whether events are allowed to overlap each other, what resources are to be used, how far ahead the events can be scheduled