Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

This is for a small personal project for micro-management. Basically, I store tasks in a SQLite3 database that looks like this:

    id INTEGER PRIMARY KEY AUTOINCREMENT
    label TEXT
    deadline INTEGER

So each task has a due date (deadline) that is stored as a Unix Time Stamp. So far so good, I can do entries such as "tomorrow: visit grandma" and a new row gets created with "visit grandma" as the label and tomorrow transformed as Unix time for deadline.

Now I would like to enter new type of tasks: routines - tasks repeated on a time pattern, like "everyday: clean kitchen". How can such tasks be stored or modeled?

For the moment, I am thinking that, in the case of a task that needs to be done everyday, to generate new rows in my table that would have the same label, and the deadline field incremented by one day. In this case, I need to fix a limit in the future. For example, if I create a routine for every day, it creates a new row for everyday of the remaining year.

Is there a simpler way to do this? Am I missing some obvious database design principles?

share|improve this question
3  
Yes. Use a proper scheduler tool instead of inventing yet another job scheduler. After the SOPA protest ends, read en.wikipedia.org/wiki/Open_Source_Job_Scheduler. This has been solved, nicely, lots of times already. –  S.Lott Jan 18 '12 at 11:02
    
thanks Lott! yes i suspected that there was an elegant solution for this! I'll wait for the SOPA end or check if there is a translation in the other Wikipedia countries Edit: actually ive just noticed that HTML source is still available in Wikipedia US, the black out screen is like a CSS trick, i am on my way for a small greasemonkey script :) –  François ッ Vespa ت Jan 18 '12 at 11:11
1  
General Note: Most of the posts below don't take in account the question how the data is actually stored. I mean given a task that repeats every Monday for 2 years, how many rows will need to be written to disk? –  Emmad Kareem Jan 18 '12 at 14:49
    
Or see this answer. –  Kris Harper Jan 18 '12 at 15:24
    
@root45 nice, I actually use lynx from command line, even easier :) –  François ッ Vespa ت Jan 18 '12 at 16:36
show 1 more comment

8 Answers

You could make a Separate table for reoccurring. But honestly I would juts put it into the same table with a Type Field.

Something like this:

ID - Int Pk

TaskDescription - TEXT

Type - Text - (Re-Occurring, or Single Occurrence) 

Due- TimeStamp - for Single Occurrence is the Date time

LastTimeCompleted - Time Stamp

ReoccurringUnit - Text - "Days", Weeks, Month, Ext

ReoccurringEveryX - Int - Reoccurring interval 
share|improve this answer
    
interesting, I am actually exploring a possible solution similar to this one, I am still working my SQL functions, I'll post if successful –  François ッ Vespa ت Jan 18 '12 at 16:35
    
Interesting, I am trying to come up with the query that can fetch both recurring and non-recurrsing tasks and then sort them by their Due date. Any clue? –  Harshal Patil Mar 16 at 16:47
add comment

In my view there are two options:

  • Store a large amount of equal rows for reoccurring items, however they have to end (either through a end date, or a finite number of items), and have to be marked as a reoccurring item. When you change the event, you have to update them all, but when you want to deviate once, you can simply 'break' the connection between one event, and make it a normal event.
  • Store the event as a reoccurring item, with a certain repetition scheme, and compute for a given date which reoccurring items are due on the given date. This gives the possibility for infinite repetition.
share|improve this answer
    
that's how i see it as well –  François ッ Vespa ت Jan 18 '12 at 17:08
add comment

If this is a personal project and you just want a way to store your tasks, I recommend TaskCoach. It's a desktop application, multi-platform, open source, easy to get started and have really good features.

In case you are developing a task application, then the most probable way is to add a new row for each recurring task. The logic is that each task is a separate entity in itself and it must be completed before the same task is to be started the next day. If you just increment it, you just can't capture the history of the task.

In case you feel, that it would just give you a big list if a few tasks weren't completed then you could trigger an event once the recurring task is completed so that the new task gets generated as a new row only when the task is marked as completed. As suggested by Morons you could use a separate table with a flag for recurring tasks in the original table together with data for recurrence (days,weeks,recur time) so that you could just have a simple script that could generate the recurring tasks based on date or a condition or by label.

But if you are sure that the task is a surely repetitive one without any change(such as brush daily) and doesn't need extensive tracking, then you could just try the following structure

  • Recurrence flag - to indicate that the task is recurring
  • Recur period - day, week, month
  • Number of tasks created - This would increment the task based on the recur period. So if the task starts today and has a recur period of one day, it would just increment by one tomorrow
  • Number of tasks completed - This would increment if the task gets completed

The logic is the difference between tasks completed and tasks created should always be the recur period if the task is always completed. So dividing the difference of days by recur period would give you an indication of how long has the task been pending.

Thanks for Kareem for pointing this out

IMHO, task applications are difficult to build for people at large.

share|improve this answer
    
thanks for the constructive answer! this is a project for fun and I would like to make it as flexible as possible, with queries such as ' to be repeated every 4 days except if it's a wednesday ' –  François ッ Vespa ت Jan 18 '12 at 17:06
add comment

Repeated tasks should have a start date and an end date. For a single date task they would be the same date.

Create some sort of "Dates" table that has one record for every day you think is relevant from the start of your needs as far into the future as you want: 12/31/2100 for example and convert to your format.

A query might look like:

Select 
  t.id
  , t.label
  , d.UnixDate
from Tasks as t
inner join Dates as d
on d.UnixDate >= t.StartDate
  and d.UnixDate <= t.EndDate
where t.id = [ID Param]
share|improve this answer
add comment

In addition to the comment from S.Lott, Martin Fowler - Recurring Events for Calendars PDF may help you (I found it a bit difficult).

Also note that several UI tools offer the function you are describing out of the box (with simple task model). I would consider this problem as a hard to solve database design problem without such tools.

share|improve this answer
    
thanks Emmad, i will take that in consideration –  François ッ Vespa ت Jan 18 '12 at 13:11
add comment

I have done something similar years ago implementing an interface like Windows Task Scheduler and basically for each task you have StartDate, EndDate (can be null), StartTime and RecurringDays that contains days of week when the task must be scheduled.

share|improve this answer
    
That's interesting. were there some design limits, ie queries that could not be done ( like 'to be repeated every week day')? –  François ッ Vespa ت Jan 18 '12 at 17:02
add comment

By far the most frequent operation will be to list all events occurring in a time period. So optimize your data so that question can be answered by a simple SQL query. I would create two tables:

CREATE TABLE events(start TIMESTAMP, end TIMESTAMP, name TEXT, user_id LONG,
                    recurrence_id LONG, ...);
CREATE TABLE recurrences(id LONG, start TIMESTAMP, end TIMESTAMP, name TEXT, 
                         frequency ...);

Index the event table by start and end times. Then all queries can be answered from the events table very quickly. When a recurrence is edited, simply delete and recreate all the corresponding events.

This advice is shamelessly repeated from a book by Tom Kite.

share|improve this answer
add comment

You can use two tables: one for the description of the tasks, the other for their status (done/not done, and other information: time spent, exit status, location of the log file, etc.) The description table would contain the name of the task, and the date or frequency at which it should be run: there would be only one row per task. Every day, a process would populate the status table for the tasks to do today, from the description table (you could populate one week or one month in advance).

Generating the status table programmatically gives you all the flexibility you want for the frequency (e.g., "every weekday except bank holidays for country X" -- it could even be stored as a string). Having a status table allows you to check if or how often the tasks fail (for instance: "I was supposed to run every day: how often did I have time to do it?").

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.