Design of Databases for storing the details of the recurrent occurrence of an event

Question

I need to implement a feature similar to the one provided by Microsoft Outlook to make your meeting appointment recurrent. I am trying to figure out the optimized Database design that I will be requiring for implementing this feature.

The requirement is something like that each run or task entered by the user will also be applicable for scheduling like a recurrent event - weekly, monthly or yearly. Could you please suggest me the Database model - table structure (with constraints) for storing these details in the DB which can be afterwards accessed by the program to do the appropriate task. Screenshots for some of the possible scheduler details can be found at the following link.

We have a mysql DB running at the backend for storing these details. As soon as the user submits a request, a request id with the details of the request is stored in the table and then a action corresponding to it is taken by the program. More clarification would be like that the users intent is to run a sql script,getting the values and then performing statistical analysis to it. But as the oracle reference DB is dynamically updated by many users, he wants to run it in a recurrent manner and get the analysis done. Note that the mysql db and the ref DB are different.

Please let me know if you require any other details.!

Answer 1

I would suggest storing the details of the first occurence in one table (scheduled tasks) and then the recurance (recurring tasks) details in another.

I might also then be tempted to update the scheduled task table with the next occurance as each task is completed.

As for the Table layout, a rough sketch would be as follows:

[ScehduledTasks]
TaskId (Primary Key)
Description and Details etc...
Start Datetime
End Datetime

[RecurringTasks]
TaskId (Foreign Key)
Frequency : Daily, Weekly, Monthly or Yearly.
DayNo : What Day to run on (1-7 for weekly, 1-31 for monthly, 1-365 for yearly)
Interval : Every x weeks, months etc.
WeekOfMonth : first, second, third... etc If populated then DayNo specifies the day of the week.
MonthOfYear : 1-12.
EndDatetime : The last date to perform
Occurences : The number of times to perform. If this and the previous value are null then perform for ever.

Obvious certain fields would be blank depending on how the task was set up, but I think the above covers all you would need to emulate the tasks in Outlook.