CODEWITHSUNDEEP
phpmysqldatabasedatabase-design
Jishad P
Jishad P

Reputation: 703

How to store multiple values of user in mysql database?

I have table like below.

enter image description here

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.

  1. Storing values to a single column name as user_roles and store values with pipe (|) separated (same as above).
  2. 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

Upvotes: 0

Views: 1439

Answers (2)

Abdulla Nilam
Abdulla Nilam

Reputation: 38672

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

01

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
.....

02

as well as read these too

  1. One to Many and Many to One Relationships - code.tutsplus.com
  2. One-to-many relationship - www.databaseprimer.com/
  3. Database Normalization

Upvotes: 0

Drew
Drew

Reputation: 24960

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).

Upvotes: 1

Related Questions