How to create a many-to-many relationship in a relational database (e.g., authors and books)?

Many-to-many relationships in a relational database are implemented through an intermediate table (join table), which contains foreign keys to both related tables.

For example, there are tables authors and books. To link authors and books, we create a table author_book with two columns: author_id and book_id. Each record in this table indicates that a specific author wrote a specific book.

Example structure:

CREATE TABLE authors (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(200)
);

CREATE TABLE author_book (
  author_id INT,
  book_id INT,
  PRIMARY KEY (author_id, book_id),
  FOREIGN KEY (author_id) REFERENCES authors(id),
  FOREIGN KEY (book_id) REFERENCES books(id)
);

This approach allows one author to have many books, and one book to have many authors.