ER diagram for library management system
This ER diagram models a simple library management system with five tables: members, authors, books, loans and fines. It shows how a library tracks who borrows which book, when it is due back, and what fines are owed. It suits students working on DBMS assignments and developers starting a small library app.
Example: ER diagram for library management system: click to open it in the editor and change it.
Entities and key attributes
| Entity | Primary key | Key attributes |
|---|---|---|
| members | id | name, email, phone, joined_at |
| authors | id | name |
| books | id | title, isbn, author_id (FK), copies_count |
| loans | id | book_id (FK), member_id (FK), issue_date, due_date, returned_at |
| fines | id | loan_id (FK), amount, is_paid |
Relationships and cardinality
- books → authors (N:1): many books can belong to one author through books.author_id. In this design each book has a single author.
- loans → books (N:1): one book can be loaned many times over its life, linked by loans.book_id.
- loans → members (N:1): one member can have many loans, linked by loans.member_id.
- fines → loans (N:1): a loan can collect one or more fines, linked by fines.loan_id.
loans is the central table. It connects members to books and records the borrowing dates. A NULL returned_at means the book is still out. Comparing returned_at or today's date with due_date tells you whether a fine should be created. copies_count on books stores how many physical copies the library owns.
How to adapt it for your project
- Need books with several authors? Replace books.author_id with a book_authors junction table that holds book_id and author_id.
- Want to track each physical copy? Add a copies table and point loans at copy_id instead of book_id.
- Add a categories or publishers table if you need to search or filter books by them.
- Add a reservations table that links members and books, so members can hold titles that are currently on loan.
Tip: Name foreign keys after the table they reference, as this diagram does (author_id, book_id, member_id, loan_id). It makes the relationships easy to read, both in the diagram and in your SQL.
Frequently asked questions
What are the entities in an ER diagram for a library management system?
This diagram has five entities: members, authors, books, loans and fines. Each one has an integer id as its primary key.
What is the relationship between members and books in a library ER diagram?
Members and books have a many-to-many relationship that runs through the loans table. A member can borrow many books, a book can be borrowed by many members, and each loan row links one member to one book.
How are fines calculated in this library database design?
Each fine is linked to a loan through loan_id. It stores an amount and an is_paid flag. You would usually create a fine when a loan's returned_at date is later than its due_date.
Make your own ER diagram with AI →