I am writing a utility to configure when certain reports are going to be automatically generated.
The next piece will be some background task (probably a Windows Service) that will poll the database to know when to execute these recurring operations. I came up with this initial table design:
ReportScheduler
---------------
AutoID int
MemberID VarChar(6)
ReportID int
RunWhen DateTime
...but soon realized that "RunWhen" isn't going to cut the catsup, because there are going to be multiple times a given report needs to be run for a given Member (once it runs, the value is no longer valid/useful).
Before showing the next candidate table design, though, a word about when these reports can be done (generated and emailed): the configurer can set them up to be run on a particular day of each month (the 1st, the 10th, the 17th, whatever) OR according to a pattern, i.e. they can be set to something like "the first Tuesday of each month" or "the last Friday of each month" or "the first Monday of each week" (IOW, every Monday). So I came up with this:
ReportScheduler
---------------
AutoID int
MemberID VarChar(6)
ReportID int
DayOfMonth int
PatternOrdinal VarChar(6) // First, Second, Third, Fourth, or Last
PatternDOW VarChar(9) // Monday, ... Sunday
PatternInterval VarChar(5) // Week, Month
...but then realized that that doesn't make too much sense, either, because code would have to run to calculate whether any date that equates to the DayOfMonth or Pattern combination of fields has been reached or surpassed every time the db is polled. And then what would prevent that from triggering forever after that, too?
SO, I think I need to combine these ideas, but instead of RunWhen, call the DateTime field NextExecution, and update it each time the report is generated for a given Member:
ReportScheduler
---------------
AutoID int
MemberID VarChar(6)
ReportID int
NextExecution DateTime
DayOfMonth int
PatternOrdinal VarChar(6) // First, Second, Third, Fourth, or Last
PatternDOW VarChar(9) // Monday, ... Sunday
PatternInterval VarChar(5) // Week, Month
When the NextExecution time is reached or surpassed, the Service generates/emails the report. It then calculates when the next execution should take place (based on the DayOfMonth or Pattern* fields) and updates the NextExecution field with that value.
This seems logical to me, but I'm sure this is a challenge that has been met before, and wonder if there is a standard way of accomplishing this that may be cleaner than what I've proposed.
Assuming that you're keeping a log of when these reports have been run, you could use that with your second method to prevent a report from running multiple times. You might want to incorporate some sort of rule that you will only try to run reports that have passed their due date within "X" days also.
Another potential design would be to keep a table similar to your first method, but fill it in when a person creates a new schedule out through the next 10 years (or some other sufficiently large time span). Keep a table that lists out the schedules that have been created by users and link the scheduled dates back to those schedules so that you can delete them if the user cancels a schedule. You'll need to decide what you want to do about schedules that overlap (both schedules happen to land on the same day this month and call for the same report to be run for example). Do you run the report once or twice? How you handle that determines how you might have to code around maintaining the calendar when a user creates/deletes schedules.