Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to manage roles and permissions. Most of designs on web look like this

tables:

Users
Roles
UserRoles
Permissions
RolePermissions

Here, what is permissions? I am thinking for such a design instead:

Users
Roles
UserRoles
Permissions

In this design, Roles is supposed to be:

   id   |     name

while permissions is supposed to be:

id | role_id | section | action

permissions defines which role in which section has what action control. something like this:

id  |  role_id  |  section  |  action

1   |     2     |  posts    | edit
2   |     2     |  posts    | add
3   |     2     |  posts    | delete
4   |     3     |  users    | approve
5   |     3     |  users    | edit
6   |     4     |  articles | delete
7   |     2     |  users    | givepermission

It uses two strings instead of an extra table and numbers. Also checking it on PHP seems easier.

Does this design have problem? And is it recommended by you according to your experiences?

share|improve this question
    
In this form nothing will prevent you having an action of 'nunvbfb n! vnjkvfk@' for the section 'jiojh nfuhn y748jkl tj'. Or anything else. You should at least create a table for sections and an other for actions. (In the structure you mentioned first, a permission would be a section-action pair in your terminology, I think. Then it is coupled to the given roles.) –  dezso Jul 1 '13 at 14:28
    
is being coupled a wrong idea? –  sheno Jul 1 '13 at 14:46
    
No, not at all - but I see I wasn't completely clear. They should be coupled to the roles in a separate table to avoid redundancy (say (posts/edit) can belong to role 2 and 3, your table above would contain this section-action pair twice). –  dezso Jul 2 '13 at 10:40
    
All the RDBMS I have worked with have similar functionality built in. I have never needed to implement it myself. –  AlexKuznetsov Jul 2 '13 at 18:38
    
You could check out Spring Security's schema here: static.springsource.org/spring-security/site/docs/3.0.x/… –  Neil McGuigan Jul 19 '13 at 19:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.