How to create a many-to-many relationship in a relational database? (example: Author and Books)

In a relational database, a many-to-many relationship is implemented through an intermediate table (join table) that contains foreign keys to both related tables.

Example for the relationship between Author and Books:

Tables:

• authors (id, name)
• books (id, title)
• author_book (author_id, book_id)

The author_book table contains pairs of identifiers linking authors and books. Thus, one author can be associated with multiple books, and one book can have multiple authors.

Example SQL for creating tables:

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)
);

In Java, using JPA, it can be described as follows:

@Entity
public class Author {
    @Id
    private Long id;

    @ManyToMany
    @JoinTable(
        name = "author_book",
        joinColumns = @JoinColumn(name = "author_id"),
        inverseJoinColumns = @JoinColumn(name = "book_id")
    )
    private Set<Book> books;
}

@Entity
public class Book {
    @Id
    private Long id;

    @ManyToMany(mappedBy = "books")
    private Set<Author> authors;
}

This approach ensures correct storage and management of the many-to-many relationship.