EDIT - Sorry about the formatting not sure how to create a neat table in the post...
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:
- Task1 to be completed every Day by 18:00
- Task2 to be completed every Monday by 15:00
- Task3 to be completed on the 1st of Each Month by 20:00
- Task4 ad hoc one off task to be completed on the 18th Feb 2013 by 23:00
- Task5 Weekly Task to be completed every Saturday by 12:00
- 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_id | task_name | + other fields to describe the task
1 | task1
2 | task2
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 | |
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
This is an interesting idea. Quick google brought up this guy who had the same idea:
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/spring-batch/reference/html/metaDataSchema.html
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/14412527/design-option-for-recurring-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
I don't think its messy at all as long as you have the proper handling on the application layer for it.