How is a many-to-many (m2m) relationship implemented at the database level?

Many-to-many (m2m) relationship is implemented through an intermediate (linking) table. This table contains foreign keys referencing the primary keys of the two tables between which the relationship is established. Each record in the intermediate table represents one connection between one record from the first table and one record from the second.

For example, for the relationship "Books" and "Authors":

Table Books:

• book_id (Primary key)
• title

Table Authors:

• author_id (Primary key)
• name

Table Book_Authors (Intermediate):

• book_author_id (Primary key - optional, can use a composite key)
• book_id (Foreign key referencing Books.book_id)
• author_id (Foreign key referencing Authors.author_id)

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255)
);

CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE Book_Authors (
    -- book_author_id INT PRIMARY KEY, -- optional primary key
    book_id INT,
    author_id INT,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id),
    PRIMARY KEY (book_id, author_id) -- composite primary key
);

Queries to retrieve data:

To find all authors for a specific book:

SELECT A.name
FROM Authors A
JOIN Book_Authors BA ON A.author_id = BA.author_id
WHERE BA.book_id = <Book ID>;

To find all books by a specific author:

SELECT B.title
FROM Books B
JOIN Book_Authors BA ON B.book_id = BA.book_id
WHERE BA.author_id = <Author ID>;