Database design for Daily, Weekly, Monthly and adhoc tasks

[digitalquill]

#EDIT - Sorry about the formatting not sure how to create a neat table in the post...

All

I am struggling to come up with an efficient database design to hold information about schedules of tasks to be performed. Note: these are not CRON tasks but I will produce automated reminders for users to complete tasks.

Examples of What I need to store:

1. Task1 to be completed every Day by 18:00
2. Task2 to be completed every Monday by 15:00
3. Task3 to be completed on the 1st of Each Month by 20:00
4. Task4 ad hoc one off task to be completed on the 18th Feb 2013 by 23:00
5. Task5 Weekly Task to be completed every Saturday by 12:00
6. Task6 Monthly Task to be completed on the 20th of each month by 11:00

My Idea which I am sure is not the best so any thoughts would be appreciated:

Task_Table
task_id | task_name | + other fields to describe the task
1         | task1
2         | task2
etc.

Task_Schedule
task_fk | complete_by_time | frequency      | day_no  |    interval   |  date              |
1          |  18:00                  |    Daily          |      1      |         1      |                       |
2          |  15:00                  |    Week         |       1     |         1      |                       |
3          |  20:00                  |    Month        |       1     |         1      |                       |
4          |  23:00                  |    Adhoc        |              |                 |  18-02-2013    |
5          |  12:00                  |    Week         |       6     |        1       |                        |
6          |  23:00                  |    Month        |       20   |         1       |                       |

etc.

Logic would be:
Frequency = Daily, Week, Month
day_no = weekly - 1-7 or monthly 1-31 (probably limit to 28 so that tasks can not recur on days that don't exist in a month.)
interval = so that we can do every 2 days
adhoc = to schedule a one off task

Days of the week = Mon=1, Tue=2, Wed=3 etc
adhoc date would be in mysql format

I just feel a little uneasy about the design and i am sure there must be a better way.

I started by storing a number of seconds between occurrences, but then Months were thrown into the mix which blew that idea, otherwise it would be simple Start_TimeStamp | Interval_seconds where we would then have no need for complete by etc

Any thoughts would be gratefully accepted

Matt Houldsworth

[K. Wolfe]

This is an interesting idea. Quick google brought up this guy who had the same idea:

http://stackoverflow.com/questions/1...heduling-tasks

His checked answer is how I started to think through it... Multiple tables, 1 for each type of schedule. I find the metaData schema posted in there insteresting though, and I'm looking through it a bit more: http://static.springsource.org/sprin...ataSchema.html

[digitalquill]

Hi Kyle

Thanks, I have seen both of those, although I thought that started to get messy with the multiple tables but perhaps more manageable?

I am also monitoring this thread http://stackoverflow.com/questions/1...ecurring-tasks

which seems a nice solution although his query is MsSQL rather than MySQL so I would have to see if I could do a similar query in MySQL

Matt

[K. Wolfe]

I don't think its messy at all as long as you have the proper handling on the application layer for it.

Hi Kyle

Thanks, I have seen both of those, although I thought that started to get messy with the multiple tables but perhaps more manageable?

I am also monitoring this thread http://stackoverflow.com/questions/1...ecurring-tasks

which seems a nice solution although his query is MsSQL rather than MySQL so I would have to see if I could do a similar query in MySQL

Matt