MySQL table structure for task manager app with task dependencies

Question

I need to design a 'task manager' table structure where task can be dependent on other tasks. For example I can have following tasks:

TASK A: independent
TASK B: independent
TASK C: can not start before TASK B is finished
TASK D: independenet
TESK E: can not start before TASK C and TASK E are finished

Each task has standard properties (started_by, assigned_to, due_date, description, status). I want to have a table structure that would allow me to do this query easily:

1. Select all user's open tasks, but select only those who can already be started (meaning in above scenario TASK C and E can not be selected here until dependency tasks are completed).

Currently my solution is to have 2 tables:

• tasks: table that hold tasks records
• task_dependencies: table that holds task to task dependencies (id, task_id, dependent_task_id)

My current query for above scenario and my current table structure goes like this:

SELECT description, from_unixtime( date_due )
FROM tasks
WHERE 
   assigned_user_id = 751
  AND status_id = 'Q'
  AND id NOT
  IN (
    SELECT TD.task_id
    FROM task_dependencies TD
    INNER JOIN tasks T ON TD.dependent_task_id = T.id
    AND T.status_id = 'Q') 
  ORDER BY date_due

  -- status 'Q' = new uncompleted task

This get's me the right result, but is this the right way to go or should I make better table structure and/or query?

Here is also SQL fiddle for above scenario.

Answer 1

Don't know why this has gone unanswered for so long. What you've suggested is absolutely the right way - tasks and task_dependencies. It's already normalized properly and allows you to pick the required info in one query, indexed on the right columns.

Minor suggestions:

• The query isn't wrong but perhaps better to avoid the all-dep-task-selecting subquery and put it as:

  SELECT T.description, from_unixtime( T.date_due )
  FROM tasks T
  LEFT JOIN task_dependencies TD
    ON TD.task_id = T.id
  LEFT JOIN tasks T2
    ON T2.task_id = TD.dependent_task_id
  WHERE
    T.assigned_user_id = 751
    AND T.status_id = 'Q'
    AND (T2.status_id != 'Q' OR T2.status_id IS NULL)
  ORDER BY T.date_due
  

  Should optimize better. (I assume there's some error in my query, couldn't sqlfiddle test it. But you get the idea.)

• The task_dependencies table doesn't need a separate PK. What you could instead do is have a complex PK for task_id and dep_task_id. Though it is considered good practice to have the separate PK, and put task_id+dep_task_id as a unique key.