4

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?

7

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.

  • In my opinion, this is the best answer and should be selected as an accepted answer. – Alan Sereb Aug 16 at 19:39
3

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.

0

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).

  • thaks for your valuable answer – Prisoner Raju Jun 25 '16 at 4:22

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.