[RESOLVED] Best way to handle user permissions

phpSimon

Hi all

If I have a table of users and need to control their permissions to, say, a number of features, I usually include a permissions column. I give each feature a number and manage the user permissions with a hash separated list.

A user with permissions to 1,5,10,18 and 20 would therefore have this in their permissions column:

#1#5#10#18#20#

This works fine I think I'd prefer to start using a separate permissions table containing 2 columns; userID and permissionID, with the primary key a combination of the two.

What do the experts generally choose?

Thank you

laserlight

phpSimon wrote:
This works fine I think I'd prefer to start using a separate permissions table containing 2 columns; userID and permissionID, with the primary key a combination of the two.

What do the experts generally choose?

Generally, I would treat this as I would any other database design problem: see if normalisation is applicable, and it usually is. So, I would not use a multi-valued field, but I would use a separate permissions table instead. Furthermore, a user may have many permissions, and a permission may be assigned to many users. Hence, I would use a join table for this many to many relationship.

phpSimon

Thank you laserlight.

I think then, that my suggested new permissions table is correct in terms of design (two columns, combined primary key) but I hadn't quite realised that what I was describing is in fact a join table (having done some reading). Thanks for the mention of database normalisation too, which got me reading up on that.

For others learning about this, a Wikipedia article says a join table can also be known as:

Junction table, cross-reference table, bridge table, map table, intersection table, linking table, many-to-many resolver, link table, pairing table, pivot table, association table.

I show this only as reference, not as accurate advice because I don't know if these terms are common, used when working with MySQL specifically or even correct! Perhaps laserlight or other experts would like to clarify.

I'll set this thread as resolved since the actual question I asked has been answered.

Thanks again.