Defining tables that related many-to-many using MySQL

Question

I have a database with 3 tables, Roles, Departments and Users. I want a ManyToMany relationship between Users and Departments, and I want a ManyToMany relationship between Users and Roles. What techniques are used to define a many-to-many relationships using MySQL?

Answer 1

For many to many connections you'll need a lookup table for each pair of tables.

users (user_id, username, password)
roles (role_id, role_name)
department (department_id, name, location)
users_roles (user_id, role_id)
  - user_id is a foreign key to users.user_id
  - role_id is a foreign key to role.role_id
  - user_id,role_id should be a unique key
users_departments (user_id, department_id)
  - user_id is a foreign key to users.user_id
  - department_id is a foreign key to department.department_id
  - user_id,department_id should be a unique key

update

Answering your question in the comments, you can use JOIN to combine the tables and use them to get data. For example to get users from given department (eg. department_id=1) and role (eg. role_id=2) you can do this:

SELECT `u`.*
FROM `users` AS `u`
    JOIN `users_roles` AS `ur` USING (`users_id`)
    JOIN `users_departments` AS `ud` USING (`users_id`)
WHERE `ur`.`role_id` = 2
    AND `ud`.`department_id` = 1

You can read more about how to use joins in the JOIN article on the MySQL developer site