I would like to express a task as either itself or a container for other tasks (recursively). The problem is that each task has to be one of two fundamental types: a goal or a routine. And, children tasks must be of their parent's type. So, each root level task has a type, but no child has a type (it uses its topmost parent's type). Additionally, each container task is either "ordered" or "unordered", referring to its contents.

I thought of using a recursive folder tree (similar to that of a file system), except, even if each folder has a type, it doesn't prevent its children from maintaining its type.

How would I represent this data structure?

  • 1
    This is simply a self-join in most database systems. The parent-child type relationship isn't really relevant, since you can always walk up the tree to a parent object. This is especially true of database systems, where pointers work in both directions; as opposed to a binary search tree, where pointers only work in one direction. – Robert Harvey Aug 15 '18 at 15:18

I see two choices to keep track of the types:

  • Store the type explicitly in each task and add database constraints to check that all child tasks have a compatible type. If available, enum types would be appropriate.

  • Let the type be implicit in the table/collection which holds these tasks. E.g. you might have a goal-table and a routine-table, not a single tasks-table.

To implement the tree structure, the pure relational approach would be to have in each child a reference to the parent task. For root tasks this reference will be null.

  parent REFERENCES tasks(id)

This works but is not very comfortable to work with – to fetch all subtasks you need a recursive query. Your DB may have extensions for hierarchical queries.

Ordering is also not trivial. The task could have an order column which supports decimal numbers. To insert a task between two existing tasks you average the order of the neighbors. After a number of insertions you might run into numeric accuracy issues and will have to recalculate the order. Again, the database may have extensions to simplify this.

Some databases (in particular document databases) allow you to simply keep a list of child task IDs, which simplifies ordering and child queries. However, this might make it impossible or at least more difficult to enforce foreign key constraints. E.g. PostgreSQL supports array columns, but AFAIK you have to check the foreign key constraint manually.

  • Currently I do have separate tables for goals and routines, but how would I store containers? Can you please elaborate? – clabe45 Aug 15 '18 at 13:02
  • @clabe45 sorry for missing that part of the question, see my edit – amon Aug 15 '18 at 13:38
  • Ordering is not an issue, as there will be a separate algorithm for determining its relevance! And the UI will be like a file system in that you only open one branch and level of recursion at a time, so that will be fine. However, my main problem is how to store the type in each task. Do I need a separate data structure for the children? – clabe45 Aug 15 '18 at 14:09
  • Actually, the order is user-defined, so I do need to store that – clabe45 Aug 15 '18 at 14:13
  • 1
    @clabe45 My answer suggests two alternatives to store the types. Either you store the type explicitly in each task (and add a consistency check) or the type is implicit by using separate tables/collections for goals and routines. In your folder analogy, there would be two top-level folders for goals and routines. The folder entries then cannot declare a type of their own. – amon Aug 15 '18 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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