Mysql storing webs ite admin permissions

Question

I have a project using C# and MySQL. I need to store permissions for a admin portal setting. There is currently 6 main permissions( read, write, view, update create...) but each of these permissions can be applied to n entities. so a user can have create, read to site a, d , f but have no permissions to the other 100+ sites. I thought of using bits added together with each site: so perm table would have perm table userid int(11) perm int(4)
siteid int() and the data would be userid = 10, perm=5 siteid=6; userid=10, perm=5, siteid=212 ...

a developer wants us to xml stored into the database so that it could look like: a 1 1 something liek the above. I need help, what do most people do? which is better for performance, we have asite that needs to be really fast. the permission structure gets checked on most saves. Thak for your help in advance.

Answer 1

Dont pack bits into a field. That makes it unsearchable, and you cant create indexes on the bits to speed searching. Also, sticking XML into the database is pointless - better to just store the information in the columns. I would just create a table that lists, for each permision entry, the site id, user id, and the permission value. Then you can just query if that value exists for that user and site combination. That would be very fast to query if properly indexed.

So, a row might look like

id site_id user_id permission_id
1    1       1        1 (read)
2    1       1        2 (write)

Then you query it like

select id from permissions where site_id = X and user_id = X and permission_id = X

If it returns id > 0, its there.

Answer 2

Frankly you could also just do a table wi user id, site id, read, write, view, update, create as booleans and index all the columns. I think it would be less confusing than having a single column with an integer...

As for performance. If there is a user session just store their credentials into their session rather than querying the database constantly. I'm not too familiar with C# and its techniques as i'm a LAMP guy but between query caching options and session handlers I doubt speed would be an issue.