A question of principle

[#1]

I have 2 tables:

• Users


• User Roles

Each user can have many roles.
I have 2 solutions to this relationship:

• Use a third table to hold the relationship, this table will simply consist of three columns, a Primary Key, then the User table Primary Key and the Role table Primary Key as Foreign Keys.


• Use a CSV field in the User table to hold the Primary Keys of the Roles which that user has access to.

Can anyone provide any insight into the pros and cons of either solution, or perhaps another solution altogether?
Thanks
jabb0

[#2]

In an effort to keep things simple, i probably over simplified things.
U are indeed right, there are permissions - each role can have many permissions - each user can have many roles.
However these three tables would be linked using the same method, whichever one gets chosen after this discussion. (ie seperate table for each relationship or id's stored in CSV field.

Quote

I'm a bit confused as to your duplication of roles assigned to a user back into the user table

The roles are not being duplicated back into the user table, it is only their ID being placed in a CSV field. There is no user information in the roles table.
The permissions table will hold alot more information than is stated here, but it would not help things to discuss that right now.  Suffice to say that the permissions and users table will hold the bulk of the info and the roles table is like a grouping mechanism.
But the core of my problem is how to link these tables, a separate relationship table, for each relationship, or a CSV field holding the many foreign keys of a one to many relationship.
TBH I really like the CSV method, but its so neat and tidy that i cannot help but think there is something fundamentally flawed in my thinking and there is a huge drawback that i am missing.
Thanks

[#3]

The one (user) to many (role) relationship is always tricky to sort out.
I would use option 1 for the following reasons:
a) It gives you the ability to look at this from other angles like finding all users with the same role.
b) Updating / adding roles would be much easier
c) managing / maintaining a CSV field involves a lot of extraction / combining of info and that code would have to change should you add additional roles.
d) Doing searches / lookups on a CSV field is a real headache and not something I would want to do. Using a separate table makes life so much easier.
While it does not necessary simplify the situation, James's idea of group & permissions is worth consideration, but then you may have the problem of having a user in multiple groups and thus compounding the issue further with having an additional user <-> group table as well as group <-> permissions table.

[#4]

a) select * from users where find_in_set(role_id, cvs_roles_field)
b) yes this is a pain
c) true due to b
d) pretty much the same as a)
Sorry for the repetition, but ya know wot its like, writing it out can sometimes make things so much clearer.
I knew there had to be a flaw somewhere in this beautiful idea.
Yes I am now leaning towards the seperate tables to hold the relationships method.
Thanks for that.
Jabb0

[#5]

Well then,
James got in just before me there, looks like that puts the final nail in the coffin.
Cheers Guys
jabb0