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:
I would like to set weekend schedule for January 2011 from 7am to 5pm.
Then I would also enter a new schedule that overlaps with existing one but just for the first half od January:
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.
Each of them (per-day table and per-schedule) has its advantages and disadvantages:
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:
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.
Is there any standard way of saving schedule data? How would you suggest I should be saving this data?
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?"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With