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:
Aucun commentaire:
Enregistrer un commentaire