1

Lets say I have a table to hold Tasks and then I have a table to hold Sub Tasks, is it better to break Tasks and Sub Tasks into separate tables or keep them both in the same table with a possible ParentId column. What if a Parent Task could also be a Sub Task, does this change the design?

  • Are the columns the same for Tasks and SubTasks? If so, then store them in one table with an ParentID / ChildID relationship. – Max Vernon Jul 28 '14 at 15:00
  • My answer dba.stackexchange.com/a/51901/10832 may help you with design. – Max Vernon Jul 28 '14 at 15:02
  • @MaxVernon - Yes the columns are the same for both – xaisoft Jul 28 '14 at 15:19
3

If a Parent Task and Subtask are essentially identical, save their rank in a hierarchy, then yes, a single table is appropriate. You can use a nullable foreign key to the primary key in the same table like this:

create table tasks (
  task_id bigserial primary key,
  subject text,
  ...,
  parent_task_id bigint null references tasks(task_id)
);

If your database server does Recursive Common Table Expressions (example PostgreSQL), then use those for querying. Here are some examples.

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.