What types of relationships do you know and how to implement a many-to-many relationship?

Main types of relationships between entities in databases:

• One-to-one (1:1): each record in the first table corresponds to one record in the second.
• One-to-many (1:N): one record in the first table corresponds to multiple records in the second.
• Many-to-many (M:N): records in the first table can correspond to many records in the second and vice versa.

To implement a many-to-many relationship, an intermediate table (join table) is usually created, which contains foreign keys to both related tables. For example:

-- Users and roles tables
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE roles (
  id INT PRIMARY KEY,
  role_name VARCHAR(100)
);

-- Intermediate table for many-to-many relationship
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)
);

Thus, one user can have multiple roles, and one role can belong to multiple users.