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.
Paste this HTML into a README, docs page, or blog post. The image links back to the full schema page.
<a href="https://www.dbdiagramr.space/schema/library-management-system">
<img src="https://www.dbdiagramr.space/schemas/library-management-system-er-diagram.svg" alt="Library Management System ER Diagram (PostgreSQL)" width="1546" height="1156" />
</a>
<p><a href="https://www.dbdiagramr.space/schema/library-management-system">Library Management System ER Diagram (PostgreSQL)</a> by dbdiagramr</p>
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
FK → publishers.id
published_year
integer
Yes
-
edition
integer
No
-
category
varchar
Yes
-
price
numeric(10,2)
Yes
-
book_authors
isbn
varchar(13)
No
PK, FK → books.isbn
author_id
uuid
No
PK, FK → authors.id
copies
id
uuid
No
PK
isbn
varchar(13)
No
FK → books.isbn
barcode
varchar
Yes
-
shelf
varchar
Yes
-
status
varchar
No
-
members
id
uuid
No
PK
first_name
varchar
No
-
last_name
varchar
No
-
email
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
FK → copies.id
member_id
uuid
No
FK → members.id
issued_by
uuid
Yes
FK → librarians.id
issued_at
timestamptz
No
-
due_at
timestamptz
No
-
returned_at
timestamptz
Yes
-
reservations
id
uuid
No
PK
isbn
varchar(13)
No
FK → books.isbn
member_id
uuid
No
FK → members.id
reserved_at
timestamptz
No
-
fulfilled_at
timestamptz
Yes
-
fines
id
uuid
No
PK
loan_id
uuid
No
FK → loans.id
amount
numeric(10,2)
No
-
paid_at
timestamptz
Yes
-
Table relationships
9 relationships connect the tables in this schema. Each entry shows the child columns, the parent columns they reference, and the cardinality.
One-to-many
books.publisher_id → publishers.id
One publisher can have many books (books.publisher_id -> publishers.id, optional).
Many books can have many authors through book_authors (book_authors.isbn -> books.isbn, book_authors.author_id -> authors.id).
copies.isbn → books.isbn
One-to-many
One book can have many copies (copies.isbn -> books.isbn, required).
loans.copy_id → copies.id
One-to-many
One copy can have many loans (loans.copy_id -> copies.id, required).
loans.member_id → members.id
One-to-many
One member can have many loans (loans.member_id -> members.id, required).
loans.issued_by → librarians.id
One-to-many
One librarian can have many loans (loans.issued_by -> librarians.id, optional).
reservations.isbn → books.isbn
One-to-many
One book can have many reservations (reservations.isbn -> books.isbn, required).
reservations.member_id → members.id
One-to-many
One member can have many reservations (reservations.member_id -> members.id, required).
fines.loan_id → loans.id
One-to-many
One loan can have many fines (fines.loan_id -> loans.id, required).
Common queries
These PostgreSQL queries run against the schema shown on this page.
Overdue loans
Loans that are still out and past their due date.
select l.id, b.title, m.email, l.due_at
from loans l
join copies c on c.id = l.copy_id
join books b on b.isbn = c.isbn
join members m on m.id = l.member_id
where l.returned_at is null and l.due_at < now()
order by l.due_at;
Member loan history
Borrowing history for a member, newest first.
select m.email, b.title, l.issued_at, l.returned_at
from members m
join loans l on l.member_id = m.id
join copies c on c.id = l.copy_id
join books b on b.isbn = c.isbn
order by l.issued_at desc;
Open reservations
Titles waiting to be fulfilled, with the requesting member.
select b.title, m.email, r.reserved_at
from reservations r
join books b on b.isbn = r.isbn
join members m on m.id = r.member_id
where r.fulfilled_at is null
order by r.reserved_at;
Books per author
Count titles through the book_authors join table.
select a.first_name, a.last_name, count(ba.isbn) as books
from authors a
left join book_authors ba on ba.author_id = a.id
group by a.id, a.first_name, a.last_name
order by books desc, a.last_name;
Frequently asked questions
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.
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.
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.