jeudi 31 décembre 2020

Modeling relational database tables for tracking status of stages and tasks within stages

I'm trying to model a relational database for a web-based, project tracking app. In the interface, I want to display all of the stages of the project and all the tasks within those stages - for example:

Stage 1: Planning do these things
  Task 1: do this planning activity first
  Task 2: do this planning activity second
Stage 2: Start coding based on requirements
  Task 1: do this coding requirement first
  Task 2: do this coding requirement second

At the end of each task I would have a drop-down for users to select the status of each task (Not-Started, In-Progress, Blocked, Completed)

For tables to model the stages and tasks that will populate the interface, I'm thinking something like these:

tbl_stages
  id
  stage

tbl_tasks
  id
  stage_id
  task

The tables for storing projects and statuses would look something like this:

    tbl_projects
      Project_ID
      Project_Name
      Status_ID

    tbl_statuses
      id:
      s1_t1
      s1_t2
      s1_t3
      s2_t1
      s2_t2 

So tbl_projects is the primary table and it would have a 1-to-1 relationship with tbl_statuses. The part I'm not sure of is how or do I even need to relate tbl_statuses to tbl_stages and tbl_tasks. I believe that the relationship between tbl_statuses and tbl_stages would be many-to-many. But maybe I just need to track tasks in tbl_statuses because the tbl_stages values can be calculated based on the state of their child tasks. I'm thinking I need some other relational tables just to track many-to-many relationships also.

Hope someone can provide guidance, I'm feeling out of my league at this point.

Here is visio diagram of what I'm thinking so far:

enter image description here




Aucun commentaire:

Enregistrer un commentaire