How to handle recurring dates (dates only) in .NET?

Question

I am trying to figure out a good way to handle recurring events in .NET, specifically for an ASP.NET MVC application. The idea is that a user can create an event and specify that the event can occur repeatedly after a specific interval (e.g. "every two weeks", "once a month" and so on).

What would be the best way to tackle this? My brainstorming right now is to have two tables: Job and RecurringJob. Job is the "master" record and has the description of the job as well a key to what customer it's for, while RecurringJob links back to Job and has additional info on what the occurrence frequency is (e.g. 1 for "once a month") as well as the timespan (e.g. "Weekly", "Monthly"). The issue is how to determine and set the next occurrence of the job since this will have to be something that's done regularly. I've seen two trains of thought with this: This logic should either be stored in a database column and periodically updated, or calculated on the fly in the code.

Any thoughts or suggestions on tackling this?

Edit: this is for a subscription based web app I'm creating to let service businesses schedule their common recurring jobs easily and track their customers. So a typical use might be to create a "Cut lawn" job for Mr Smith that occurs every month The exact date isn't important - it's the ability for the customer to see that Mr Smith gets his lawn cut every month and followup with him about it.

Let me rephrase the above to better convey my idea. A sample use case for the application might be as follows:

• User pulls up the customer record for John Smith and clicks the Add Job link.
• The user fills out the form to create a job with a name of "Cut lawn", a start date of 11/15/2009, and selects a checkbox indicating that this job continually occurs. The user is presented with a secondary screen asking for the job frequency. The user indicates (haven't decided how at this point - let's assume select lists) that the job occurs once a month.
• User clicks save.

Now, when the user views the record for John Smith, they can see that he has a job, "Cut lawn", that occurs every month starting from 11/15/2009. On the main dashboard when it's one week prior to the assumed start date, the user sees the job displayed with an indicator such as "12/15/2009 - Cut lawn (John Smith)". A week before the due date someone from the company calls him up to schedule and he says he's going to be out of town until 1/1/2010, so he wants his appointment rescheduled for that date. Our user can change the date for the job to be 1/1/2010, and now the recurrence will start one month from that date (e.g. next time will be 2/1/2010). The idea behind this is that the app is targeting businesses like lawn care, plumbers, carpet cleaners and the like where the exact date isn't as important (because it can and will change as people are busy), the key thing is to give the business an indicator that Mr. Smith's monthly service is coming up, and someone should give him a call to determine when exactly it can be scheduled for. In effect give these businesses a way to track repeat business and know when it's time to followup with a customer.

Answer 1

storing the schedule in a database table or in an xml file makes sense. primarily because the job can only be scheduled by the asp.net application. The event will have to be triggered by another application - most likely a windows service that checks the configuration that is stored by the asp.net application.

as for the structure of the tables that really depends on what exactly you need to schedule - i imagine all you really need is user id, job id, and date to execute in a single table.

Answer 2

How complex are your rules likely to get? If you can persuade the Powers that Be to keep things simple, your life will be a lot more pleasant :) You should also get them to sign off on various test situations - if something is scheduled to occur once a month on the 30th, should it occur in February or not? At least by using dates (without times) you should get away without worrying about daylight saving time...

You might want to look at DDay.iCal or look for other iCal (RFC 2445) implementations for .NET - but judging the quality of the library could well be tricky, as it's a complex area with loads of corner cases.

In terms of the database, I would calculate it on the fly unless that proves too painful. You'd obviously have to still have the same "work out the next occurrence" code somewhere, and it removes the complexity of the periodic update.

Answer 3

I've seen this done soley on the database side, and let me tell you that it gets really ugly.

I would suggest having a business model of some sort that handles the idea of recurring events (i.e. calculating on the fly from the business model).

As far as the database goes, you'll definitely need to store the data involved with your business model, but on top of that you should consider forecasting your recurring events (perhaps up to a year or so), which will allow uses external to your application to be able to use your events. Example, if you have a reporting system that needs to know what people are scheduled for, etc.

Answer 4

This is one I've thought about a bit before - though I haven't put it into practice in the real world, the elements you're working with are as follows:

• Job Description
• Recurence Pattern
• Next Scheduled Occurence
• Job/Occurence History

So far, so easy. Those are the things that get stored in the database, the means to caclulate a "next" date based on a given date and a recurrence pattern live in your business logic.

The key piece of information for your clients in general is the list of next scheduled occurences so as long as those are maintained correctly (you create a new one when you mark the current one as done) that will work. Unfortunately for scheduling purposes you probably need to be able to show the next several occurences and this is the point at which things get interesting (-: Two choices: Calculate on the fly (I think this is probably better - especially with some smart "cacheing") or precalculate which is less overall load but more data to manage and keeping it up to date is more complex (quite apart from considerations of how far ahead to create schedule entries).

So - to answer your question - why do I think on the fly is better? Well basically because the only "fixed" point is the next scheduled event, you're going to want to calculate your diary forward from there based on its current value (ish) and if it changes then you need to recalculate. You can get creative in terms of preserving a list of next dates cached for a particular job to aid performance as needed, but in terms of genuinely persistent storage you're only interested in the next date.

A couple of final thoughts - firstly if the date for an occurence is changed (as in your question) there are two options for the next date, either a) continue with the original schedule regardless of the change to (or even cancellation of - yet another challenge!) the current event or b) schedule from actual completion i.e. for some things we will want them to happen every n weeks on a given day but might need to wiggle the dates back and forth from time to time whereas for other things we want them to happen 4 weeks after the previous completion. Secondly - as prompted by Jon - you ought to look at iCal, not necessarily for implementations but certainly to help understand the elements of what might make up a calendar entry and a repeat pattern.

Answer 5

Here's a complete solution to the problem: http://www.codeproject.com/KB/cs/RecurringDateGenerator.aspx