Store multiple values in one db column

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?

"multiple values in one db column" is almost always a bad idea. — Uueerdo
– Uueerdo, Commented Sep 25, 2017 at 19:17
@Uueerdo if a challenge only will have two users then should I do like a user_a_completed and user_b_completed column on the pivot table and add the user_id for when a user completes them — Packy
– Packy, Commented Sep 25, 2017 at 19:19
Just make a new row each time. No need to store multiple user id's in one row. — Brian Glaz
– Brian Glaz, Commented Sep 25, 2017 at 19:19
You can use json columns in MySQL past a certain version (don't remember the number) but from my experience, this was more of a hinderance than a help. Querying json columns is a bit messy and not really supported yet by Laravel, any benefits added by them were quickly replaced by other issues, etc. The eloquent model/relationship system also works wonders for decreasing complexity when querying/return results with one-to-many or many-to-many relationships. In the end, up to you. — Tim Lewis
– Tim Lewis, Commented Sep 25, 2017 at 19:20

Uueerdo · Accepted Answer · 2017-09-25 20:32:29Z

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)

answered Sep 25, 2017 at 20:32

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kornél Takó · Accepted Answer · 2017-09-25 19:21:01Z

0

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.

answered Sep 25, 2017 at 19:21

Kornél Takó

3373 silver badges7 bronze badges

Comments

Mr Pro Pop · Accepted Answer · 2017-09-25 19:36:11Z

0

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.

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

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!

edited Sep 25, 2017 at 19:36

answered Sep 25, 2017 at 19:30

Mr Pro Pop

6765 silver badges19 bronze badges

Collectives™ on Stack Overflow

Store multiple values in one db column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related