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, Dec 15 '15 at 17:07
A quick google for "database normalization" would be beneficial — Steve, Dec 15 '15 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, Dec 15 '15 at 17:10
@uruloke small projects get bigger, the opposite is rarely true — Steve, Dec 15 '15 at 17:10
@Steve, But the size of database is become large due to this right ? — Jishad P, Dec 15 '15 at 17:11
@Steve, some small project are just small projects. Sometimes you need a small project for like a month only, really depends on the use of the project — Oliver Nybroe, Dec 15 '15 at 17:12
It will have negligible effect on db size, and disk space is dirt cheap, never consider that as a factor. If you envisage the db getting so large disk space is an issue, then yes you certainly need a properly normalized database. Again, option 1 is lazy and will bite you in the a**e later — Steve, Dec 15 '15 at 17:15
@Steve, Sorry my think is not correctly. As you said in 1st comment, the query can take with LIKE statement of writter right. ? so the results are both in same. — Jishad P, Dec 15 '15 at 17:18

score 1 · Answer 1 · edited May 23 '17 at 12:31

1

The reasons you want the second choice include, mainly

See Junction Tables or associations tables (more simplified)

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

edited May 23 '17 at 12:31

Community

answered Dec 15 '15 at 17:26

Drew

score 0 · Answer 2 · edited May 23 '17 at 12:15

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 '17 at 12:15

Community

answered Dec 15 '15 at 17:17

Abdulla Nilam

I want the reason for choosing the one to many relation other than first one. can you please tell me that..:) – Jishad P Dec 15 '15 at 17:20
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" – Drew Dec 15 '15 at 17:33
@Drew sorry i didnt get you – Abdulla Nilam Dec 15 '15 at 17:34
@JishadP did my answer helpfull?? if helpful accept + vote me – Abdulla Nilam Dec 27 '15 at 17:09

2 Answers2