The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database Schema for Scheduling

I'm having a real hard time coming up with an elegant schema for a schedule.

The schema should allow for a task to be triggered on certian days of the week and should also allow for it to recur X times, once, or forever.

Cory R. King
Thursday, December 22, 2005
Just did a little thinking.  What is wrong with a design like this?



end_date would be null if it was forever
weekday would be null if it was daily or one-time

Is there anything wrong with this design?
Cory R. King
Thursday, December 22, 2005
I don't think there is an "elegant" schema because you're storing multiple relationships between an event (as one node) and a calendar day (as the other node).  Ultimately, you're going to have to pick a manageable number of relationships and derive the others from it in the logic layer.

For example, given the following requirements...

1) You can assign an event to a specific date.

2) You can assign an event to cover a date range.

3) You can assign an event to a day of the month (first of the month).

3) You can assign an event to every occurance of a weekday (every Monday).

4) You can assign an event to every rule based occurance of a weekday (every other Thursday).

You're not going to easily be able to capture all of those possible relationships in the schema.  At the very minimum, you may just have to calculate the specific date when you insert the event (and repeat the event as necessary, with different dates), and then do all of your queries by date.

If you find you have a lot of repeating events, you can make a separate table that tracks weekdays rather than specific dates, and then simply merge the two data sets at the presentation layer.

There are other considerations, but I think what I'm trying to get at is that you will need to do some performance tuning based upon the data itself - you can try to do it all in the database, but that's potentially a waste of effort for the gains you'd get.
Anonymous Coward
Thursday, December 22, 2005
If this is for a people-sensible calendar thingie, ignore this post.

If this is for system job scheduling, I think your basic schema is OK to define the events.  You may want consider adding some indicators - occurs X times, job type, run-time parameter lists etc - but those are dependent on your business.  I'd add 2 things:

- a 'current waiting queue' table that contains info identifiying the _next_ scheduled occurrence of any event. 
- a dispatcher that uses the queue table to decide what events/tasks to fire when.

The dispatcher or the tasks themselves can look at the task definition tables to decide if a new 'current waiting queue' entry needs to be written after the current one is consumed.

Our primary app does something similar to this-we have a master queue and a continuously running dispatcher.  In our case, the dispatcher writes new queue entries after completion (good or bad) is reported by the task jobs and the schedule data indicates more executions should be scheduled.
a former big-fiver Send private email
Thursday, December 22, 2005
Are you looking for something along the lines of recurring tasks in Outlook? I believe some people have cloned that functionality.
MBJ Send private email
Thursday, December 22, 2005
You can't store all that info directly in the database - some of it has to be calculated before storing and some of it has to be stored on a read.  Here's what I would do.



Now, when someone says, "I want this task to occur on Monays and I want it to occur twice" you compute an end date of two tuesdays from now, plug that in for the end date, and put an Y in the Monday field.

If they say, "I want this to occur weekdays, forver" you flag the weekdays, and leave end_date as null.

So you compute an end_date on storage if it's a number of occurances, or leave end_date null if it goes forever.
Lou Send private email
Thursday, December 22, 2005
I should have mentioned - this is schema is going to be used for "people scheduling" not sysadmin stuff.

Thanks for the replies so far!
Cory R. King
Thursday, December 22, 2005
When I was writing something for automatic scheduling, there were 3 categories of schedules:
1 - periodically (daily at 4pm, or monthly on the 15th)
2 - manually (the jones report is only done when mr jones asks)
3 - business rule (run on the last thursday of the month, unless that month is the end of the quarter, then run it every day that week at noon and 6pm).

so the schema was something like:

--info about the job omitted--
TypeCode (int)
StatusCode (int)
Interval (varchar(4))
Number (int)
LastRunDate (datetime)
DueDate (datetime)

TypeCode was something like 1-3 above (in addition, there would be a couple other types, like "disabled/do not run").
StatusCode would be indicators like hold, pending, loaded, running, rescheduling (statemachine info so that monitors could see what was going on)
Interval + Number are used in the DateAdd function in vb, sql server and vba.
LastRunDate shows the time that the job was last kicked off.
DueDate shows the time that the job is next due to be run.

In the reschedule phase, jobs of type 3 were responsible for rescheduling themselves.
Thursday, December 22, 2005
Resource scheduling apps can get quite hairy quite quickly, especially if you start talking about forecasts, budgets, etc. 

What aspect of my post above does not meet your criteria/needs?
Lou Send private email
Thursday, December 22, 2005

That is actually a pretty darn good schema.  Toss in a time to run and life is golden.

Thanks everybody!
Cory R. King
Thursday, December 22, 2005
Here's a design I just got done with:


ixSched     (identity)
dtStart        (eg. '12/1/2005')
dtEnd        (null)
sStartTime    (eg. '09:00 AM')
bSUN        (eg. TRUE/FALSE, 1/0)

The reason for having dtStart/dtEnd AND sStartTime/sEndTime is so that dtEnd (and optionally dtStart) can then be nullable. If dtEnd is nullable there is no end DATE but there is a TIME (sEndTime) that the task should end. You could also make sEndTime nullable meaning that the task should just end when it's done...

If you need to run something twice on Monday you add two different schedules with bMON = TRUE.

Since it's a mult-user database, the process that executes the schedules runs the following query every minute. It gets any tasks that should be running right now and makes sure they're already started or starts them. Polling kind of sucks but the only other way is to message the execution process when a change occurs (could be done with a trigger or a messaging system). For a simple scheduling system, polling is a better choice though.

FROM TaskSchedule ts
WHERE bEnabled = 1 AND
GetDate() BETWEEN dtStart AND
    IsNull(dtEnd, GetDate()) AND
    Convert(datetime, Convert(varchar(10),
        GetDate(), 101) + ' ' + sStartTime) AND
    Convert(datetime, Convert(varchar(10),
        GetDate(), 101) + ' ' + sEndTime) AND
    CASE DatePart(dw, GetDate())
) = 1

When it starts a task, it marks the "task object" with it's end time. Every minute, it checks the collection of running tasks to see if any should be ended...
Semi-nonymous Send private email
Thursday, December 22, 2005
Go into Outlook and pull up the "Tasks" tab, then create yourself a new task.  Note all the options it has there for recurring tasks--there's a whole lot of good ideas to pull from.
pds Send private email
Friday, December 23, 2005
I am stunned!  This is a software design forum of some description and what do I see, people designing software with a database schema.  Build your objects; task object, schedule object (you'll probably have a base with a number of extensions) and then create your schema once your object takes form.

I'm sorry to throw the spanner in here but I just can't sit on the fence and let this go past.  You should all be ashamed of yourselves.
MurrayCollingwood Send private email
Monday, December 26, 2005
We're too busy solving problems to be ashamed.  When we run out of problems, check back with us.
Christopher Send private email
Tuesday, December 27, 2005
Murray, you can look at this from two directions.  One is focused on the objects you'll need and retro-fitting a database schema into it.  That schema may not be optimal.

Or you can start with a schema and build your objects on top of it.  Neither is the correct solution and it depends on the situation.

Me, I find it easier to think in terms of a database, so I start there.  Its easy for me to break my problems down into tables and relationships, while it is much more difficult to break it down into objects.  So I start at my easier tier.  If I find that what I have doesn't work optimally in code I'll change the schema or put some views in.  It's a choice.

Oh, and database design qualifies as software development from where I'm standing.  I can't break the two apart.
Lou Send private email
Tuesday, December 27, 2005
I'm kinda in the Murray camp on this.

Martin Fowler has something to say about schedules, here:
Mark Smith Send private email
Tuesday, December 27, 2005
There's nothing wrong with defining software applications in terms of databases, but I would deal with the facts well before the schema.

In short (I haven't mentioned this for at least a month), Object Role Modeling, the first true ORM.

Describe the facts of the process then the data design falls out of that and the interfaces to it, programmatic and UI drop like ripened fruit into the hand.
Simon Lucy Send private email
Saturday, December 31, 2005
Heres another way.

Tasks      ScheduleRules
 * TaskID    * TaskID
              - Type
              - Subtype
              - Time
              - LastWindowEnd
A task can have one or more rules. Run procedural code over the rules to build a third table 'Schedule' over an operational date window ie from StartDate till WindowEndDate.  Insert a TaskID, Datetime for each scheduled event into 'Schedule'.  Maintain LastEindowEnd in rules each time schedule is updated.  You can use Schedule table as a tool to track when the task is completed with a status etc for operational control.

Rules Type = Monthly,Daily,OnceOff etc.  Subtype (depends on type Sorry) = day of week, day of month. Procedural code handles logic to build schedule.
Simon Andrews Send private email
Wednesday, January 11, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz