Library system. How to design a database to store many instances of books and functionality to order these books?

Question

Database design

I am designing the library system .
The task is to store information about library users, books, and book reservations by users.
There are 2 types of roles: Administrator and Registered User .

The administrator can issue books on hand. The way is this:
The user selects a book, clicks book, if the book is available, then the book goes into the booked status. Further, the administrator can transfer the book to the "on hand" status for such and such a user, and then the book is returned and goes into the "in the library" status. If there are 2 copies of such books in the library, then another user comes and does the same with this book. Such a chain.

The administrator can also add books to the database (indicate their number, title, author, genre).

So far I have designed the database by highlighting 5 main entities:
1. Users (if, firstName, lastName, email, password, isBanned, idRole)
2. Roles (id, name)
3. Books (id, title, description, year, cover , idAuthor, idGenre)
4. Authors (id, firstName, lastName)
5. Genres (id, name)

What entities need to be added to implement the booking process? I was thinking about the Orders table, which will contain idUser, idBook, and the status that the book has moved to. But if you specify the idBook of the book, and there are 10 such books, then the user ordered them all)) I don’t understand what to do here. If we consider the book as the only copy in the library, then it seems easy to create this table with orders. But I want to have 10 copies of each book in the library and the information about their booking could be saved and nothing would be lost. So that I can see who booked the book in the last year, who has it now, and so on.

Assumptions:

Answer 1

In addition to the book entity:
Books (id, title, description, year, cover, idAuthor, idGenre)
Add a book instance table:
BookEntities (id, -- book instance
id idBook, -- book
id add_date, -- date of entry into the catalog
exc_date, -- date of exclusion from the catalog deterioration ,
-- deterioration
wieght -- weight PS: Not always in the librarian system such detailing is required, it may turn out that it is enough to keep track of the balance between max. number of books, and how many issued, using triggers in the database.

Answer 2

For each copy of the book, make a separate entry (that is, you enter another entity), since they give out a specific copy, and, most likely, each copy has a unique number.

Answer 3

The book has an inventory number (id) and you forgot the ISBN.
Orders then looks like this
id, startDate, dueDate
Books (id, title, description, year, cover, idAuthor, idGenre, status, userid (null) , orderId (null))
However, this is a matter of taste and depends more on which query plan will be larger , some fields can be moved to orders