CODEWITHSUNDEEP
database-designnormalizationdenormalization
cathat
cathat

Reputation: 399

Design User/Group ACL data model

My question may probably be an echo of a question previously asked here: 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.

Upvotes: 3

Views: 1157

Answers (2)

jamieb
jamieb

Reputation: 10033

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

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132570

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.

Upvotes: 2

Related Questions