mysql - How to store multiple values in single column where use less memory? -

i have table of users 1 column stores user's "roles". can assign multiple roles particular user.

then want store role ids in "roles" column.

but how can store multiple values single column save memory in way easy use? example, storing using comma-delimited field not easy , uses memory.

any ideas?

if user can have multiple roles, better have user_role table stores information. normalised, , easier query.

a table like:

user_id | role --------+-----------------       1 | admin       2 | user       2 | admin       3 | user       3 | author 

will allow query users particular role, such select user_id, user.name user_role join user role='admin' rather having use string parsing details out of column.

amongst other things faster, can index columns , take marginally more space solution puts multiple values single column - antithetical relational databases designed for.

the reason shouldn't stored is inefficient, reason dcoder states on comment answer. check if user has role, every row of user table need scanned, , "roles" column have scanned using string matching - regardless of how action exposed, rmdbs need perform string operations parse content. these expensive operations, , not @ database design.

if need have single column, suggest you no longer have technical problem, people management one. adding additional tables existing database under development, should not difficult. if isn't authorised do, explain why table needed right person - because munging multiple values single column bad, bad idea.