How should I (how would you) save schedules in database

Question

I have an application where users are able to set schedules for certain entity within a certain date range. Schedule editing will be similar to entering calendar appointments:

• when it starts and when it ends (hours)
• start date and set weekly recurrence days (ie. every Saturday and Sunday or every Moday only)
• end of recurrence date - sets date when recurrence should end

Example

I would like to set weekend schedule for January 2011 from 7am to 5pm.

1. I set start date as 1.1.2011
2. Set end date as 31.1.2011
3. I set start at 7:00 and end at 17:00
4. enable Monday on weekly recurrence mask

Then I would also enter a new schedule that overlaps with existing one but just for the first half od January:

1. Set start date 1.1.2011
2. Set end date 14.1.2011
3. Set time from 9:00 to 15:00
4. Enable Monday.

Question

I could be storing schedules for each day in a table like:

create table EntitySchedule (
    EntityID int not null
        references Entity(EntityID),
    ForDay date not null,
    StartAt time not null,
    EndAt time not null
)

But I will get lots of records in this table:

# of records per schedule = (# of entity records) * (number of days in schedule range)

Or I could store data similar to entering data:

create table EntitySchedule (
    EntityID int not null
        references Entity(EntityID),
    StartRange date not null,
    EndRange date not null,
    StartAt time not null,
    EndAt time not null,
    WeekdayMask tinyint not null -- bitmask of days (7 bits)
        default (0)
)

This table would have a lot less records than the previous one.

Comparison

Each of them (per-day table and per-schedule) has its advantages and disadvantages:

1. Per day table would be easy to get schedule for a particular day
2. Per day schedule would have no possibility of editing recurrence data, you would always enter a new schedule overriding existing data
3. After few years per-day table will have huge amount of records which would have to be mitigated
4. In case of per-schedule table getting schedule on a particular day isn't possible without calculation
5. Per-schedule table allows copying of schedule from one date range to another, which would be a very welcome feature that would make creating schedules simple by simply importing schedule data from a different date range and applying it to a new date range.

Usage scenario

Don't think of this as usual calendar appointments that we know from our personal calendars. Rather think of this as very flexible shop/store (entity) opening hours (schedule). So my database will have many many stores and they have very flexible opening hours that usually repeat on weekly basis.

Editing schedules is usually an overwrite of existing data, so when opening hours within a defined date range already exist we wouldn't really update existing schedule definition but rather create a new one that would override the existing one. In case of having a per-day schedule this is very simple. I'd just overwrite those days that are applicable for the new schedule date range.

But in the case of per-schedule table this becomes more complicated:

1. I could just insert a new record in the table and then when reading schedule of a particular day newest record wins (kind of LIFO approach). This means that every schedule read (select) will include a bit more complex query where I would have to return all records related to a particular entity that define dates in correct range and then return the last one. This is fine for getting schedule of a particular day but makes my life hard when I'd want schedules for a date range...
2. Defined schedule ranges don't overlap and when I would insert a new one It could as well mean that an existing schedule definition (or many of them) should be changed or even split into two.

The first one seems a better approach. But query of getting schedules for a date range becomes rather complex and probably not very fast. Imagine of getting entity schedule for January 2011. Reading data should always yield results in the form of per-day table.

Question

Is there any standard way of saving schedule data? How would you suggest I should be saving this data?

Answer 1

I'm not aware of any standard way of storing schedule data.

I would go with your second approach - storing the data required to create your schedule, as this is what was defined by the user (consider, for example, how you would load from the "per-day" table in order to allow the user to edit their schedule - this would be tricky!).

I'd add to your table above with a "Last Run" field, then depending on how you were going to be using this, a "Next Run" Computed Column which would use the other info in the row to calculate the next time this scheduled event should occur. This would allow you to do a "get all events happening today" from the database.

EDIT: Now that you've clarified the question to be about opening hours (something where you'd need to be able to list future occurrences of the schedule) rather than what I'd assumed of a task scheduler, where all you'd need is the "Next Run" time, the Next Run field is less useful.

However, I'd still go with storing the schedule data, since this is actually what the store will have defined (they're going to say "we want to open between 9-5 on weekdays", not "we'll open 9-5 next mon,tues,wed,thur,fri then the next mon...."). While I appreciate that it makes some things more complicated, it's more accurate to store this and derive the actual day-to-day data. You could still use a stored procedure or a table-based user-defined function in order to be able to select the day-to-day data. The implementation of this will depend again on your usage scenario - will you only ever want to list opening hours per store, or would you want to have the functionality of "get all stores which are open on XX?"

Answer 2

I have wrestled with this problem a few times. I think the best way is to simply store the start date/time and end date/time either as a single field for start and end or as a date and a seperate time field as you have suggested.

I think this is the most flexible and although it requires more calculation at your app layer for overlapping schedules etc, its the 'least bad' of all the alternatives. I'm not sure its a standard way though.

ps. I did once implement a system where each day of the year had a record in a table and a seperate table stored all the schedules using that day, but it just becomes more hassle than its worth as it requires substantial coding on the app layer also, but in differnt places. using Date/Times I think is the best solution.