#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:
- 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_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