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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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



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


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. – A-K Jul 2 '13 at 18:38
You could check out Spring Security's schema here:… – Neil McGuigan Jul 19 '13 at 19:23

Your Answer


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

Browse other questions tagged or ask your own question.