CODEWITHSUNDEEP
mysqldatabase-design
Jdizzle
Jdizzle

Reputation: 11

Best practice to track progress of tasks for user in MYSQL database

So I am creating a small web app to track users as they complete tasks. And I am struggling to identify the best way to layout my database.

Some rules and logic first to explain things. There are numerous tasks that are identified by colors. Greed, Red, Blue, Yellow etc. Each task has numerous exercises, 1-40. So green1, green2, green3 etc. All the exercises have a difficulty rating. I want to track each users progress for all tasks/exercises by marking it as completed or not. This means I can count all the completed ones and give them a completed percentage for a task.

Currently my DB tables look like this:

Each task (color) has a table called (color)taskinfo eg 'greentaskinfo':

+----------------------------+
| colorid  difficulty active |
| 1             H       Y    |
| 2             H       y    |
| 3             L       Y    |
| 4             L       y    |
+----------------------------+

And then for ever color/task there is a progress table eg greenprogressinfo. This stores each users progress. 

+-------------------------------------+
| progressid  userid colorid complete |
| 1             1       1       Y     |
| 2             1       2       Y     |
| 3             1       3       N     |
| 4             1       4       N     |
| 5             2       1       N     |
| 6             2       2       Y     |
| 7             2       3       N     |
| 8             2       4       Y     |
+-------------------------------------+

Then I can count how many are complete where userid = x and complete = Y and work that out as a percentage from the total number.

This means however that I need to auto create 40 entries for the 9 task tables when a user registers. It doesn't seem very efficient. There are probably more problems than I can see. Any expert advice how to set it up better would be appreciated.

Upvotes: 1

Views: 1930

Answers (1)

TAM
TAM

Reputation: 1741

The tables you show are not quite consistent with your verbal requirements, where you talk about tasks and exercises. A design reflecting this distinction would look like:

Task
id
color

Exercise
id
task_id
difficulty
position    // if exercises should be ordered within tasks

User_Action
id
user_id
exercise_id

Whenever a user has completed an exercise (and not before that moment), create a UserAction record. So you can ask whether a user has completed all exercises of a task by the query

select *
from Exercise e
where task_id = givenTaskId
and not exists (
   select *
   from User_Action a
   where a.user_id = givenUser
   and a.exercise_id = e.id
)

That way you can also get a more detailed progress information, e.g. counting the number of completed exercises of a task.

Upvotes: 2

Related Questions