How to make such a database in Mysql?

Question

MySQL

You need to make a database that stores books and authors and make a query to display authors who have written more than 3 books. I managed to make 2 tables.
authors table

----+--------------+
| id | AuthorName   |
+----+--------------+
|  1 | John Smith   |
|  2 | Jane Doe     |
|  3 | Someone Else |
+----+--------------+

books table

+--------+-----------------------+----------+
| bookId | title                 | authorId |
+--------+-----------------------+----------+
|      1 | Secret of Everything  |        1 |
|      2 | Secret of Nothing     |        1 |
|      3 | Secret of Some Things |        1 |
|      4 | No More Secrets       |        1 |
|      5 | How to Tame Cats      |        2 |
|      6 | How to Tame Dogs      |        2 |
|      7 | Just a Book           |        3 |
+--------+-----------------------+----------+

my request is to show authors

mysql> SELECT  AuthorName, COUNT(authorId) as authorId
FROM books LEFT JOIN authors ON authorId = id
GROUP BY AuthorName
HAVING authorId >= 3;

But as it turned out, I have to do this task not with 2 but with 3 tables (3rd perhaps book_author_id), how can I do this, please help. You need to use foreign/primary key.

Answer 1

That's right: the relationship between the tables should be M:M (many to many), since one author can have many books, and one book can have many authors. You have designed a database in which a book can have only one author.
The db will look like this:

CREATE TABLE authors
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    author_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE books
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    book_title VARCHAR(100) NOT NULL
);

CREATE TABLE book_author_id
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    author_id INT NOT NULL,
    book_id INT NOT NULL,

    FOREIGN KEY (author_id) REFERENCES authors (id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books (id) ON DELETE CASCADE
);

And then the data fetch according to your condition will be:

SELECT authors.author_name, COUNT(book_author_id.author_id) as count
FROM authors
LEFT JOIN book_author_id
    ON book_author_id.author_id= authors.id
LEFT JOIN books
    ON book_author_id.book_id= books.id
GROUP BY authors.author_name
HAVING COUNT(DISTINCT book_author_id.author_id) >= 3;