← All schemas
Schema Library

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.

Written by Varun Krishnan · Founder, dbdiagramr

Published · Updated

Source: Original design by dbdiagramr, modeled on standard DBMS textbook schemas

Tables
10
Core flow
Title -> Copy -> Loan
Join tables
1
Library Management System database schema diagram showing every table, column, and foreign key

Scroll horizontally to see the full diagram. Click it to open at full size.

Open in DBdiagramr to edit →

Embed this diagram

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

ColumnTypeNullableKey
publishers
iduuidNoPK
namevarcharNo-
cityvarcharYes-
countryvarcharYes-
authors
iduuidNoPK
first_namevarcharNo-
last_namevarcharNo-
birth_yearintegerYes-
books
isbnvarchar(13)NoPK
titlevarcharNo-
publisher_iduuidYesFK → publishers.id
published_yearintegerYes-
editionintegerNo-
categoryvarcharYes-
pricenumeric(10,2)Yes-
book_authors
isbnvarchar(13)NoPK, FK → books.isbn
author_iduuidNoPK, FK → authors.id
copies
iduuidNoPK
isbnvarchar(13)NoFK → books.isbn
barcodevarcharYes-
shelfvarcharYes-
statusvarcharNo-
members
iduuidNoPK
first_namevarcharNo-
last_namevarcharNo-
emailvarcharNo-
phonevarcharYes-
addresstextYes-
joined_attimestamptzNo-
librarians
iduuidNoPK
first_namevarcharNo-
last_namevarcharNo-
login_idvarcharNo-
hired_attimestamptzNo-
loans
iduuidNoPK
copy_iduuidNoFK → copies.id
member_iduuidNoFK → members.id
issued_byuuidYesFK → librarians.id
issued_attimestamptzNo-
due_attimestamptzNo-
returned_attimestamptzYes-
reservations
iduuidNoPK
isbnvarchar(13)NoFK → books.isbn
member_iduuidNoFK → members.id
reserved_attimestamptzNo-
fulfilled_attimestamptzYes-
fines
iduuidNoPK
loan_iduuidNoFK → loans.id
amountnumeric(10,2)No-
paid_attimestamptzYes-

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-to-many

book_authors (isbn -> books.isbn, author_id -> authors.id)

Many books can have many authors through book_authors (book_authors.isbn -> books.isbn, book_authors.author_id -> authors.id).

One-to-many

copies.isbn → books.isbn

One book can have many copies (copies.isbn -> books.isbn, required).

One-to-many

loans.copy_id → copies.id

One copy can have many loans (loans.copy_id -> copies.id, required).

One-to-many

loans.member_id → members.id

One member can have many loans (loans.member_id -> members.id, required).

One-to-many

loans.issued_by → librarians.id

One librarian can have many loans (loans.issued_by -> librarians.id, optional).

One-to-many

reservations.isbn → books.isbn

One book can have many reservations (reservations.isbn -> books.isbn, required).

One-to-many

reservations.member_id → members.id

One member can have many reservations (reservations.member_id -> members.id, required).

One-to-many

fines.loan_id → loans.id

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

Related schema diagrams

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 →