Preferred approach to implement multiple many-to-many relationships?

Question

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

Answer 1

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.

Answer 2

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.