Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing list of strings in MySql column

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: users table

like image 597
Elia Perantoni Avatar asked Mar 23 '17 15:03

Elia Perantoni


People also ask

Can we store list in MySQL column?

Short answer: probably not. 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).

Can a list be stored in database?

So, per Mehrdad's answer to a related question, I get it that a "proper" database table column doesn't store a list. Rather, you should create another table that effectively holds the elements of said list and then link to it directly or through a junction table.

How do I add a list to a MySQL database?

Inserting a Complete RowThe INSERT command first takes a comma separated list of column names enclosed in parentheses. This is followed by the VALUES keyword and then a comma separated list of values for each column. Note that the values must be listed in the same order as the column names.


1 Answers

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'
like image 57
Stefano Zanini Avatar answered Sep 21 '22 14:09

Stefano Zanini