what's the best way to represent a many-to-many relationship in a database?

Question

If I have a number of users and a number of pages, and each user can administrate any number of the pages, and each page can be administrated by any number of the administrators, how could I store all of these permissions in a database?

For example, I could have a field in the users table, which is just a comma-delimited list of page IDs, or a similar field in for each page (administrated by... etc). But that would require that I process the string after it comes out of the database.

I could give each user their own table, with a key=>value kind of layout, where I'd have user=>someuser, name=>some guy, permissions=>whatever, page_id=>4, page_id=>8, page_id=>12, etc. But that seems messy and would require extra permissions for the mysql user.

Assuming the number of pages will be relatively small (it will be), I could put a 'flags' sort of field into the users table, a 32 bit INT (there would not be more than 32 'pages' in this implementation). but that would only allow for an on/off sort of privilege.

I've also considered having a user table, and then each user entry point to a permissions table for that user, which would have a row for each page with the appropriate permissions for that user -- that's cleaner than the other "table-per-user" solution I listed but again also need extra privileges for the mysql account.

Any other ideas? What, in your experience, winds up being the most pain-free solution to this?

Answer 1

many <-> many relationships are usually modeled with an intermediate table. In your case you would have these tables:

User        UserPage       Page
------      -----------    -------
UserID      UserID         PageID
...         PageID         ...

The intermediate table (UserPage here) is where you store information that's particular to that set of ID's (your permissions, in this case).

Answer 2

the canonical way is to have an intermediate table, with just two fields: the primary key of each of the connected tables.

Answer 3

What you need is a mapping table.

USER-----< USER_PERMISSIONS >-----PAGES

The primary key for the new table is a composite key made up of the user_id and the page_id

This will give you the greatest flexibility and allow for new pages to be added without having to change your schema.