CODEWITHSUNDEEP
phpmysql
Neon Physics
Neon Physics

Reputation: 3477

Method for storing a user's website privileges and querying in mySQL?

What I have:

I have a column in my table called privileges that stores a string of privileges for a user. (-1 = owner, 0 = admin, 1 = moderate comments, etc.) So I would have string like this in sql: 1,2,3.

The problem:

If I wanted to select all users that are admins AND moderators, it becomes a little tricky with LIKE conditions. Right now, If I want an admin, I just cycle through all the users search for 1 (using PHP - inefficient).

I am looking for a solution that is easily 'plugged' by plugin developers and is easily query-ed. My solution works, but if the users were to grow to hundreds, it could take awhile cycling through all them repeatedly just to find a certain privilege.

Does anyone have a better method or thoughts on this?

Upvotes: 3

Views: 408

Answers (3)

cachesking
cachesking

Reputation: 23

Your table should be normalized as suggested by guiman. Per Wikipedia: Normalized tables are suitable for general-purpose querying. This means any queries against these tables, including future queries whose details cannot be anticipated, are supported. In contrast, tables that are not normalized lend themselves to some types of queries, but not others.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425331

SELECT  *
FROM    privileges
WHERE   FIND_IN_SET(0, privilege)
        AND FIND_IN_SET(1, privilege)

You will have a faster query if you normalize your table (create an additional table user_privileges with separate record per privilege). This way you could run:

SELECT  user
FROM    user_privileges
WHERE   privilege IN (0, 1)
GROUP BY
        user
HAVING  COUNT(*) = 2

and this could use an index on privilege.

You may also consider storing the privileges in a native SET datatype.

Upvotes: 1

guiman
guiman

Reputation: 1334

Why dont you usea a Table like Priviledge , that holds an id and a name. Then add a many to many UserPriviledge table that related used_id and priviledge_id, so then looking for a users its as simple as making an INNER_JOIN

Upvotes: 3

Related Questions