Library Management System ER Diagram
The library management system is the classic DBMS lab project: members borrow books through loans, and the diagram maps all ten tables. Publishers publish books identified by ISBN; authors link to books through a book_authors join table; each title has many physical copies; members take out loans against copies, place reservations against titles, and accrue fines per loan. The modeling detail most student diagrams get wrong is the ISBN-versus-copy split: ISBN identifies a title, so a loan must reference a physical copy row - you cannot join a loan to an ISBN and know which book is actually on the shelf.
Last updated: 2026-09-27
Source: Original design by dbdiagramr, modeled on standard DBMS textbook schemas
Tables in the Library Management System schema
| Column | Type | Nullable | Key |
|---|---|---|---|
| publishers | |||
| id | uuid | No | PK |
| name | varchar | No | - |
| city | varchar | Yes | - |
| country | varchar | Yes | - |
| authors | |||
| id | uuid | No | PK |
| first_name | varchar | No | - |
| last_name | varchar | No | - |
| birth_year | integer | Yes | - |
| books | |||
| isbn | varchar(13) | No | PK |
| title | varchar | No | - |
| publisher_id | uuid | Yes | - |
| published_year | integer | Yes | - |
| edition | integer | No | - |
| category | varchar | Yes | - |
| price | numeric(10,2) | Yes | - |
| book_authors | |||
| isbn | varchar(13) | No | - |
| author_id | uuid | No | - |
| copies | |||
| id | uuid | No | PK |
| isbn | varchar(13) | No | - |
| barcode | varchar | Yes | - |
| shelf | varchar | Yes | - |
| status | varchar | No | - |
| members | |||
| id | uuid | No | PK |
| first_name | varchar | No | - |
| last_name | varchar | No | - |
| varchar | No | - | |
| phone | varchar | Yes | - |
| address | text | Yes | - |
| joined_at | timestamptz | No | - |
| librarians | |||
| id | uuid | No | PK |
| first_name | varchar | No | - |
| last_name | varchar | No | - |
| login_id | varchar | No | - |
| hired_at | timestamptz | No | - |
| loans | |||
| id | uuid | No | PK |
| copy_id | uuid | No | - |
| member_id | uuid | No | - |
| issued_by | uuid | Yes | - |
| issued_at | timestamptz | No | - |
| due_at | timestamptz | No | - |
| returned_at | timestamptz | Yes | - |
| reservations | |||
| id | uuid | No | PK |
| isbn | varchar(13) | No | - |
| member_id | uuid | No | - |
| reserved_at | timestamptz | No | - |
| fulfilled_at | timestamptz | Yes | - |
| fines | |||
| id | uuid | No | PK |
| loan_id | uuid | No | - |
| amount | numeric(10,2) | No | - |
| paid_at | timestamptz | Yes | - |
Frequently asked questions
What tables does a library management system database need?
A standard library schema needs publishers, authors, books, book_authors, copies, members, librarians, loans, reservations, and fines - 10 tables covering catalog, circulation, and overdue handling.
Why do loans reference copies instead of books?
Because ISBN identifies a title, not a physical item. When a member borrows a book, the loan must point at the exact copy on the shelf (its barcode and status). Reservations, by contrast, reference the title (ISBN) since the member is waiting for any available copy.
How are books with multiple authors modeled?
Through the book_authors join table - a many-to-many link between books (by ISBN) and authors. Each row pairs one title with one author, so a book can have many authors and an author can write many books.
Explore more schemas
Visualize your own database
Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.
Try it free →