No matter what, you cannot get around needing to perform a computation to determine the list of recurrence dates for a given event. By "computation" I mean something like the following pseudocode:
foreach(event in activeEvents)
{
while(recurrenceDate <= currentDate && recurrenceDate <= event.EndDate)
{
event.RecurrenceDates.Add(recurrenceDate);
recurrenceDate.AddDays(event.IntervalLengthInDays);
}
}
I highly recommend using Douglas Day's excellent .NET iCal library (be sure to read the license terms to ensure you are permitted to use this in your app, though they are quite permissive) to calculate recurrence dates for you, because despite appearing straightforward, it's actually very difficult to correctly handle all the corner cases.
This will give you a list of dates that the event occurs on, up to the current date. There are two questions: when and where should you perform this calculation?
As for where, I recommend in the C# app, since you've already stated you have such a component involved. It is absolutely possible to do this in a performant, set-based way in your SQL database; however, the obvious, intuitive way to code this is using loops. C# is great at loops; SQL is not. And doing it the obvious way will make it much easier to debug and maintain when you (or someone else) looks at it six months down the line.
Regarding when, there are two possibilities:
Option 1: On demand: do it as part of the job that sends your daily reminder blast.
- Fetch the data for all active events
- Compute recurrence lists for them
- Examine the last recurrent event (this will be the one closest to the current date) to determine if that event should be sent out as a reminder
- Send eligible reminders
Option 2: Once: do it when the event is created and entered in the system.
- When an event is created, calculate all recurrence dates through its end date
- Store these dates in a table in the DB (event_id FK, event_date)
- Your reminder job fetches a list of eligible events (both active and has the appropriate date) from the precomputed table
- Send eligible reminders
Which option is better? My money is on #1. Why?
- With option 1, if I realize I accidentally entered "daily" instead of "weekly" for my recurrence period, changing the event is much easier, because I don't have recompute and re-store all the recurrence data. This is the reason to calculate recurrence on demand, and should trump all but the most dire of performance related concerns (most of which could likely be fixed by throwing more hardware at the problem, or by better load balancing).
- Option 2 is degenerate in the case of events without a defined end date. This is manageable by, say, calculating X years into the future, but what happens X years later?
- A daily reminder job probably runs overnight, and so doesn't need to execute super fast. This means cutting down on its execution time isn't a priority. And the recurrence calculation time is probably going to be negligible anyway; I expect the bottleneck to be the actual email sending. However, if you have a lot of active, old events to consider (by "a lot", I mean billions) and/or your app server is grinding to a halt under load, this may become important.
- Option 1 saves DB work. I don't mean storage space (although it does use less); that shouldn't matter unless you have a lot (many trillions) of events. I mean that it's less "chatter" back and forth between your app server and the DB, so there's less chance for a dropped connection, concurrency collision, etc. Please note this is incredibly trivial and really doesn't matter either way, unless your production environment has major problems.
