CODEWITHSUNDEEP
sqlarraysdatabaselistdatabase-design
Elia Perantoni
Elia Perantoni

Reputation: 589

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

Upvotes: 9

Views: 36024

Answers (1)

Stefano Zanini
Stefano Zanini

Reputation: 5916

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'

Upvotes: 20

Related Questions