How should repetitive calendar tasks be stored in the database

database-design

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?

Best Answer

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
Related Topic