Storing list of strings in MySql column

Question

I have a users table which contains data for registered users, each row represents a user. One column in particular should contain a list of groups the user is part of, at the moment that column is of TEXT type and I'm storing that list as a string where groups are separated with a semicolon, something like:

admin;moderators;devteam

And I was wondering: "Is this a good idea?", is there a better/safer way to do this that doesn't require a lot of effort to implement or is this "ok"?
Here is a pic of the table as of now:

It's fine assuming you never want to do any type of ordering, filtering, sql side value validation, or joining on those values. If you ever want any of those things then it would be recommended to refactor this to a set of tables in at least 3nf. — Igor
– Igor, Commented Mar 23, 2017 at 15:48
This is a many-to-many relation Users >--< Groups. MySQL does not store lists (or arrays or other data structures (non-primitive types) and hacking your own is a pain -- You will want a separate table for users — fiacre
– fiacre, Commented Mar 23, 2017 at 15:50

Stefano Zanini · Accepted Answer · 2017-03-23 16:12:11Z

20

And I was wondering: "Is this a good idea?"

Short answer: probably not.

Why

If you will ever need to do any manipulation on that column, you will find yourself in big trouble. Simply selecting all users in a group will require some operations on a string (usually not performance-friendly). Same will hold true for sorting, joining and all the other operations SQL is great for.

Solution

What you describe is a typical example of N:N relationship, where each user can belong to multiple groups and each group can have multiple users in it.

The 'standard' way of modeling this relationship is creating a new table, where each row will represent a user belonging to a group. The columns will be group and userID.

With data from your example

userID  | group
--------|----------
     1  | admin
     1  | moderator
     1  | test

This allows to have one row for each user in the users table, and getting the groups of a specific user is as simple as

select  group
from    user_groups
where   userID = '1'

answered Mar 23, 2017 at 16:12

Stefano Zanini

5,9362 gold badges16 silver badges33 bronze badges

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

2 Comments

Elia Perantoni Over a year ago

Do I need junction tables for this? Could you please provide an example with some code?

divyanshu gupta Over a year ago

this solution will work fine but in case of large amount of data,the size of the table will be more as we are having duplicate rows for userIds. the answer should be in a way in which we can store all related values in a single row. like: using list or json array

Collectives™ on Stack Overflow

Storing list of strings in MySql column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related