Results 1 to 4 of 4
Jan 24, 2013, 12:36 #1
- Join Date
- Jan 2008
- Hessle, East Yorkshire UK
- 0 Post(s)
- 0 Thread(s)
Database design for Daily, Weekly, Monthly and adhoc tasks
#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:
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.Code:
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