How are multiple roles assigned to a single user in a database?

In a database, a many-to-many relationship between user and role tables is typically used to assign multiple roles to a single user.

Example structure:

• users table — stores user data.
• roles table — stores possible roles.
• user_roles table — links users and roles, containing pairs of user_id and role_id.

This approach allows a user to have multiple roles, and a role to belong to multiple users.

Example SQL for creating the linking table:

CREATE TABLE user_roles (
  user_id INT,
  role_id INT,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);