How to store multiple values of user in mysql database?

Question

I have table like below.

I want to store user roles like admin,reader,writer

How can I store the values in my table.

I searched in google and many results such as 2 types.

Storing values to a single column name as user_roles and store values with pipe (|) separated (same as above).
Storing values on another table like user_roles and store with foreign key of users.

Which of the above two method is better for development ?

Tell me the advantage and dis-advantage of both please...

Thanks & Regards

The second option - the 1st makes it much harder to query on the individual fields, for example if you wanted to show a list of all users with the role writer — Steve
– Steve, Commented Dec 15, 2015 at 17:07
A quick google for "database normalization" would be beneficial — Steve
– Steve, Commented Dec 15, 2015 at 17:09
It really depends on how big a system you are going to make, the smartest way would be to have a sperate table for permissions, but if your system is just a small one and not going to be expanded that much, then just save it in the same table. — Oliver Nybroe
– Oliver Nybroe, Commented Dec 15, 2015 at 17:10
@uruloke small projects get bigger, the opposite is rarely true — Steve
– Steve, Commented Dec 15, 2015 at 17:10
@Steve, But the size of database is become large due to this right ? — Jishad P
– Jishad P, Commented Dec 15, 2015 at 17:11

Community · Accepted Answer · 2017-05-23 12:31:23Z

1

The reasons you want the second choice include, mainly

Data Normalization Sanity
Speedy use of indexes and not table scans
Avoiding coding nightmares like find_in_set()

See Junction Tables or associations tables (more simplified)

See Nightmare Coding without it (and slow performance as all get up).

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Dec 15, 2015 at 17:26

Drew

25k10 gold badges47 silver badges81 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:15:19Z

0

If you user is having more than one user roles, so definitely it will define as one to many relationship.

So adding table with permutations alone with user Id is best option. Cz one user can have many user roles.

this kind of combination never happens (one-to-one)

user A - admin
User B - writer
User C - reader

Possible is (one-to-many) or (many-to-one)

User A- Admin + reader
User B - reader + writer
user C - Admin + reader + writer
user D - Admin + writer
.....

as well as read these too

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Dec 15, 2015 at 17:17

Abdulla Nilam

39.6k18 gold badges85 silver badges115 bronze badges

4 Comments

Jishad P Over a year ago

I want the reason for choosing the one to many relation other than first one. can you please tell me that..:)

Drew Over a year ago

Abdulla, you are not giving sound technical reasons. You can save data for one-to-many in a text file if you want. He is not asking how you can save data. He is asking what are the best practices and why. You are merely saying: "Data can be saved"

Abdulla Nilam Over a year ago

@Drew sorry i didnt get you

Abdulla Nilam Over a year ago

@JishadP did my answer helpfull?? if helpful accept + vote me

Collectives™ on Stack Overflow

How to store multiple values of user in mysql database?

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related