I am building a database that includes the following tables:

  • pages
  • users

A particular user can access (or is associated with) a page with one of the following roles (permissions):

  • Owner
  • Administrator
  • Editor

What will be the best approach in this kind of situation? Either to setup join tables like this:

  • owners (page_id, user_id)
  • administrators (page_id, user_id)
  • editors (page_id, user_id)

Or using a single join table:

  • pages_users (page_id, user_id, role)

Also, if the second approach is preferable, should role attribute be a string (such as 'owner', 'administrator', or 'editor') or something like role_id and the roles defined in a separate table as follows:

  • roles (id, name)

If there's a completely different approach which fits the need in a more appropriate manner, please let me know.

Also, is there any special name for this type of relationship(s) in database jargon? I am unable to craft the question's title right, edits are welcome.

Thank you for your answers

  • Do you have a "super" user - one who can edit/admin everything? Is a page owned by a singler 'user'? Etc. I don't yet see the full model that you want. – Rick James Aug 22 '16 at 21:24
  • There could be multiple super users. A page could be owned by multiple users (owners) as like multiple administrators and editors. – Forthaction Aug 22 '16 at 21:29

Since the primary key (page_id, user_id) is same for all roles it would be better to put all roles in a single table, the one you named pages_users.

The question on using role_id and a separate roles table is a matter of personal preference. I would use a separate table, at least if I'm going to populate an UI component list based on the choices.


If there will be no additions to the type of roles, go with option 1. If you expect to add other roles later, go with 2. And indeed make a FK referencing a table Roles (and my preference would be just by name, not id, saves a join). Remember that you have to build a user interface for maintaining roles in option 2, and give someone permission to use that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.