将多个值存储在一个db列中

Question

I am trying to create a pivot table to help keep track of "challenges" in my applications. Basically I have a challenge_task pivot table that creates a relationship between a challenge and a task. When a user that is in a challenge completes a task I want to be able to tell so I can track a user's progress. How is the best way to store multiple users completing a task on a challenge?

I was thinking in the pivot table adding a json column called user_completed to handle this and store the user_id for every user that completes the task for a challenge.

So challenge_task would look like

challenge_id | task_id | user_completed

Is this a good way? Or is there anything that fits this better?

Answer 1

I'd recommend a database structure something like this:

challenge: challenge_id | other data
task: task_id | other data
user: user_id | other  data

challenge_task: challenge_task_id | challenge_id | task_id 
               | possibly more data (such as deadline for completion)

challenge_task_users: challenge_task_id | user_id
                     | possibly more data (such as status: accepted, in progress, completed)