Design option for 'recurring tasks'

Question

I am writing a small application which handles Tasks for people. Very simple, but the area I am stuck on, as far as table design goes, is the case of a recurring task, which can be either once off, daily, weekly or monthly. If weekly, it's on a specific day, weekly. Monthly is a specific day.

I have a tasks table, and a recurring_type_id, and was going to handle the recurring tasks in code, but is the the ideal way? The other way is to insert all the tasks when the task is created - for each event time. But that doesn't seem right either.

Can anyone advice on a design and how to handle this in a maintainable and efficient way?

I'm using SQL Server 2008 R2

Answer 1

I would create a task table to insert my tasks into.

taskTable
|taskID  |Freq   |runAt       |
-------------------------------
|1       |daily  |1           |
|2       |daily  |0           |
|3       |weekly |5           |
|4       |weekly |1           |
|5       |monthly|15          |
|6       |monthly|1           |
|7       |once   |2013-7-4    |

runAt for dailies is not ever considered so it doesn't matter what value is entered.

runAt for weekly items is the day of the week that the task is to run.

runAt for mothly is the day of the month that the task is to run (month end tasks I usually run on the first since is saves the hassle of dealing with which day the month ends on although you could use this to figure that out

lastDayOfMonth = datePart(d,dateadd(s,-1,dateadd(mm, datediff(m,0,getdate())+1,0)))

runAt for once is the actual day the task is to run.

Then I'd create a task to run daily to see what needed to be run.

select  taskID
from    taskTable
where   (freq = 'once' and runAt = convert(varchar(10),getDate(),21))
  or    freq = 'daily'
  or    (freq = 'weekly' and runAt = datePart(dw,getDate()))
  or    (freq = 'monthly' and runAt = datePart(d,getDate())

This query gives me all the taskID for any tasks that I need to run.

Not sure if this is what you were looking for but hopefully you'll find something useful in it.