SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Location
    Hessle, East Yorkshire UK
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    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...

    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:

    Code:
    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

    Matt Houldsworth

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,075
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Location
    Hessle, East Yorkshire UK
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,075
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    I don't think its messy at all as long as you have the proper handling on the application layer for it.

    Quote Originally Posted by digitalquill View Post
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •