Best user role permissions database design practice? [closed]

As krokodilko wrote in his comment, it depends on the level of flexibility you need.
I have implemented role based permissions for one of my clients as follows:

  1. User (user id (PK), user name (unique), password (salted and hashed!), first name, last name, phone etc’)
  2. Role (role id (PK), role name (unique), role description)
  3. Permission (permission id (PK), permission name (unique)) – the tabs / screens / actions goes here
  4. User To Role (user id, role id) – PK is both columns combined
  5. Role to Permission (role id, permission id) – PK is both columns combined

But my requirement was to be as flexible as possible, and it is a system that is still growing (6 years and counting).

I guess a lot of applications can have the user to role as a one to many relationship, instead of a many to many like in my case, but I wouldn’t go hard coding permissions or role to permissions in any application.

Further explanation: Role based security database design on What the # do I know?

Leave a Comment