Обсуждение: help modeling a schedule system
I'm not sure if this is the correct list for this, but I'm hoping to get some help with a data model I'm struggling with. Let me know if this should be elsewhere! :)
We have an event schedule system that basically tells an event handler when an event should be fired, and keeps a history of fired events. An event would only be fired once per day, and can have the following settings.
a) Time of day (granularity of minutes, we're not worried about seconds):
e.g. 09:34, 17:00, etc.
b) Day of week (an event can be fired or not fired any/all days of the week):
e.g. sun-sat; mon-fri; mon,wed,thr; mon; etc.
c) Ratio in fired-event-days to all-event-days (an event may only fire on some of the days that it is suppose to):
e.g. 1:2 - would fire every other day, 1:1 would fire every day, etc.
d) Exceptions (events can have periods of set inactivity):
e.g. 20110201-20110210 would disable the event from February 1st, 2011 through February 10th, 2011, after which it would resume as normal.
note: we are expecting number of events to be in the 10's of thousands, but I would like a solution that can easily scale into the few millions.
The models I've come up with to satisfy these requirements seem bloated and overly complex to me. Having these events fire, on-time, is the biggest priority here. By having a complex, temporal system the probability of logic errors seems to be high; I want to avoid that.
Thanks for any help everyone!
Chris H.
Well, when I did something similar a very long time ago, I put a "next time to fire" column in the table. Each time an eventis fired, the application updates that column. Was a lot simpler than baroque searches for events that would need firing between the last check and now. Was a lot cleanerthan creating tons of instances out through the future. You could create a stored procedure, if you want, to calculate the next time based on all the possible criteria. You could, have a "last time fired" column that keeps track of when the event was most recently fired, for logging/debugging/info.You could have a trigger that updates that "next time to fire" column each time the "last time fired"column is updated. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice