CODEWITHSUNDEEP
phpmysqllaravel
Packy
Packy

Reputation: 3593

Store multiple values in one db column

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?

Upvotes: 0

Views: 75

Answers (3)

Uueerdo
Uueerdo

Reputation: 15961

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)

Upvotes: 1

Mr Pro Pop
Mr Pro Pop

Reputation: 676

I wouldn't recommend you inserting multiple values in one database column.

Note: This is my opinion. Just sharing the way I use it.

A table called tasks_settings which has the task settings.

Example 1

I find this way flexible because I can always edit the title, description, and reward easily. I can also add 2 more fields here which are valid_till and valid_for. So you can make it expire after a period of time and only for a special rank like staff or all users.

Another table called users_tasks

Example 2

This one controls the users. Whether they have completed the task or not. This could also achieve what you are looking for. 

id | challenge_id | task_id | username | user_completed

I hope this has helped you!

Upvotes: 0

Kornél Takó
Kornél Takó

Reputation: 337

I dont recommend Json if you want to index your data, because Json can not be indexed. I think you should make a pivot table between the users and the tasks too, and create the neccesary relations.

Upvotes: 0

Related Questions