Design User/Group ACL data model

Question

My question may probably be an echo of a question previously asked here: http://stackoverflow.com/questions/452295/how-to-design-a-user-object-model-using-ms-roles-membership, but I'm asking again since there is no good answer yet and my question is geared toward the data model design anyway:

So anyway, in my application, there are users and groups. A user can belong to many groups and of course a group can contain many users. The issue is right now, we only know of a few basic groups: user, guest and admin. In the future, there might be can-do-X, can-do-Y groups and potentially many more or no more than just admin, user and guest. So I'm just tempted to take the easy approach. Here are my thoughts about the approaches:

If I was going to have a bare-bone User table and a Role table and another one that simulates the many-to-many relationship, it seems a little bit over-engineered. If I understand correctly, this would be considered the normalized approach. Conversely, if I throw in the User table the following columns like IsAdmin, IsGuest, CanDoX, CanDoY, etc, then this would be denormalized.

I am looking to balance between complexity and flexibility, time and space, etc. Please suggest.

Answer 1

I would strongly recommend the normalised USER_ROLE (or ROLE_USER) table. It isn't "over-engineered", it correctly and accurately reflects your requirements.

I work on a database system that uses exactly this model; it currently has 250 different roles, and works fine. Having 250 "CanDoX" columns on the users table would be a nightmare, and would not be an option anyway as some super-users are allowed to define new roles.

Answer 2

Rather than multiple columns of "CanDoX," "CanDoY," use a bitmask to represent permissions.