Mysql – Preferred approach to implement multiple many-to-many relationships

database-designMySQLschema

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

Best Answer

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.

Related Question