mysql - Complex database queries vs scheduled storing of data and stored procedures -

i'm building system using mysql allows tasks managed , created. tasks can either

• a one-off task completed fixed date
• repeating tasks, starting on fixed date , repeating every n days

a simplified schema looks like

 tasks ========================  | id |   name     | date     | repeats | |  1 |  backup x  | 2011-... | 0       | |  2 |  backup y  | 2011-... | 1       |  

now, let's want run query on data set:

select $n - mod(datediff(now(), task.date), $n) datediff, task.name    tasks task   order datediff, task.date; 

where n repeat interval task.

as can see, have sort either task.date or datediff. need able sort on 1 field , datediff dependent on whether or not task repeats.

in psuedocode, following query more appropriate:

select   if(task.repeats = 1)     // number of days until next repeat on repeating event     $n - mod(datediff(now(), task.date), $n) datediff   else     // number of days until non-repeating task expires     datediff(now(), task.date) datediff 

• stored procedures 1 option, more difficult manage in application code.
• views option. mysql views not efficient.

so, i thinking of setting cron job populates table updated data every 5 minutes or so. able query table on relevant fields.

i think method scale better when tasks become more complicated:

e.g. users give each task precise repeat interval using crontab syntax require quite lot of processing in application code work out datediff on tasks.

i appreciate thoughts on since i've had tendency lately avoid complex queries , stored procedures. perhaps overkill?

sounds me should store/run these mysql events

http://dev.mysql.com/tech-resources/articles/mysql-events.html