Complex database queries vs scheduled storing of data and stored procedures

I'm building a system using MySQL that allows tasks to be managed and created. Tasks can be either

• a one-off task to be completed by a fixed date
• repeating tasks, starting on a fixed date and 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 say I want to run a query on this data set:

SELECT $N - mod(datediff(NOW(), Task.date), $N) AS datediff, Task.name 
  FROM tasks Task
  ORDER BY datediff, Task.date;

where N is the repeat interval for a task.

As you can see, I have to sort by either Ta开发者_JAVA技巧sk.date or datediff. What I really need is to be able to sort on one field and for datediff to be dependent on whether or not a task repeats.

In psuedocode, the following query would be more appropriate:

SELECT
  IF(Task.repeats = 1)
    // Get the number of days until next repeat on a repeating event
    $N - mod(datediff(NOW(), Task.date), $N) AS datediff
  ELSE
    // Get number of days until non-repeating task expires
    datediff(NOW(), Task.date) AS datediff

• Stored procedures are one option, but are more difficult to manage then in application code.
• Views are another option. But MySQL views are not very efficient.

So, I am thinking of setting up a CRON job that populates a table with updated data every 5 minutes or so. Then I will be able to query this table on the relevant fields.

I think this method would scale better when tasks become more complicated:

e.g. users could give each task a very precise repeat interval using crontab syntax which would require quite a lot of processing in application code to work out the datediff on all tasks.

I appreciate your thoughts on this since i've had a tendency lately to avoid complex queries and stored procedures. Perhaps this is overkill?

---

sounds to me like you should store/run these with MySQL Events

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

---

I probably over complicated the viable options in this case by not having looked into MySQL's control flow functions nearly enough.

One solution i'm goin with at the minute is

SELECT IF(Task.repeat, repeats_expression, non_repeats_expression) as date_diff;