Best practice for parent child relation

Question

Suppose, there are some tasks and some sub-tasks under those tasks
For example:

Task 1  
   Sub task 1.1  
   Sub task 1.2  
   Sub task 1.3  
   Sub task 1.4  

Task 2  
   Sub task 2.1  
   Sub task 2.2  
   Sub task 2.3 

what is the best way to design the schema in this situation?

First way:

"Task" Table
====================================
task_id  |  task_name | description     
====================================

"Sub Task" Table
==========================================================================================================================
sub_task_id  |  task_id  |  sub_task_name | description  |   attachment_link   |   start_date   |    due_date   |   status  
==========================================================================================================================

Second Way:

======================================================================================================================================
id   |  name |  description  |  parent_id    |   attachment_link   |   start_date   |    due_date   |   status  |  type(task/subtask)   
======================================================================================================================================

Which one is preferable?

Answer 1

This is a bit late, but the previous answers don't provide the third alternative which is both flexible and normalized.

All tasks should be kept in a Task table, because subtasks after all are just tasks.

The relationship between tasks should be stored in a second table that has columns to identify the parent/sub task relationship based on a foreign-key relationship with the Tasks table, and which together would form a composite primary key.

Tasks

• id

• title

• description

• start_date

• end_date

TaskRelationships

• parent_task_id

• sub_task_id

In order to find the parent tasks of a given task you can query the sub_task_id column. To find the sub-tasks of a given task search the parent_task_id column.

This structure has the added benefit of having the flexibility to add as many parent tasks to a task as your wish.

Answer 2

It depends.

If your model is fixed as that which you state in the example data, and always will be, then the first way will work and will enforce that model.

If you might later want, or already want to support, a deeper model (i.e. splitting sub-tasks into smaller units of work) then you need the more generic tree structure permitted by the second option. This disadvantage of this is that while you have the single level model is that you need to make sure that you enforce it as the general parent/child relationship will allow data that is not valid in that model.

Answer 3

This parent child relationship has been thought out a long time ago by using a single table with parent_id concept. Don't need to reinvent the wheel.

Having two tables forces you to insert 2 records for each task. In a parent-child type relationship, only 1 record is added and 1 row shows if the task is a parent or a child and its relationship to its parent. ParentID Null is explicit, it's the parent.

With the 2 table approach you need to join 3 tables :

select * from Tasks  t
JOIN TaskRelationships tr on t.id = tr. parent_task_id

At this point I only have parent tasks. I still need to get the child tasks.

With a single parent -child relationship:

select 
case t1.parent_id when Null Then 'Parent'
else "Child"
end as relationship,
t1.id, 
t1.task_name
t1. etc ...
from task t1 LEFT JOIN task t2 on t1.id = t2.parent_id

-- Give me only Parent task

select 
t1.id, 
t1.task_name
t1. etc ...
from task t1 where parent_id is Null

-- Give me only child task

select 
t1.id, 
t1.task_name
t1. etc ...
from task t1 where parent_id is not null 

Much more flexible. One table to deal with. You can event add a "task_type_id" in that table that qualify each row such as :

1- main task
2- sub task 

What if you have a sub-sub task? Very easy, all you need is add another id in your task_type table

1- main task
2- sub task 
3- sub sub task 

Check out The Data Model Resource Book. Great way to improve your modeling skills.

Answer 4

If you want a relational model you must follow normalization rules.

Then the best way is the first option: two tables and "task_id" will be a foreign key in the second table.

If not you have duplicate data: id, name and description will be repeated (second example).

Answer 5

The answers given are pretty classic / normalization / parent-child / intersection tables. But I sense that materialized paths (among others) are now the rising star:

https://stackoverflow.com/search?q=materialized+path

Among some other newer patterns. They add a column where the PK path is added, so that it is faster and easier to select on this versus looking up parent-child relationships.

Full list is here: https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database