Best practice to track progress of tasks for user in MYSQL database
<p>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. </p>

<p>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. </p>

<p>Currently my DB tables look like this:</p>

<p>Each task (color) has a table called (color)taskinfo eg 'greentaskinfo':</p>

<pre><code>+----------------------------+
| colorid  difficulty active |
| 1             H       Y    |
| 2             H       y    |
| 3             L       Y    |
| 4             L       y    |
+----------------------------+
</code></pre>

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

<pre><code>+-------------------------------------+
| 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     |
+-------------------------------------+
</code></pre>

<p>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. </p>

<p>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.</p>