Library Management System ER diagram and database schema

What this schema models

A complete library management system database design, modeled on how real integrated library systems work rather than how textbook diagrams draw them. 24 tables, 13 enums and 45 relationships covering the catalog (books, authors, subjects, series), physical holdings across branches, members and borrowing rules, circulation (loans, renewals, holds and transfers), fines and payments, and acquisitions. THE FOUR DESIGN RULES 1. A BOOK is not a COPY. books is the bibliographic record, one row per title and edition; copies are the physical items with barcodes. Holds are placed on the book, so any copy can fill them; loans are made on a copy. 2. Loans are history, not state. A loan row is never deleted. An open loan is simply returned_at IS NULL, and a partial unique index allows only one open loan per copy. 3. Rules are snapshotted. A loan copies the fine rate and due date it was made under, so changing a member type never rewrites past loans. 4. Fines are a ledger. Charges, payments and the allocations between them are separate rows, so partial payments and waivers are exact and auditable. Money is stored in integer cents. DETAILS WORTH STEALING - The hold queue is ordered by placed_at, with no stored position column to keep renumbering. - copies.status is a cached convenience for the public catalog; the authoritative answer to "is it out?" is an open row in loans. - Every renewal is its own row, so "why is this due on the 14th?" always has an answer. - book_contributors carries a role, so translators, editors and narrators are credited correctly. - Transfers track copies travelling between branches to fill holds. - Notifications record what was actually sent, for the member who says nobody told them. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: catalog, titles and acquisitions, holdings, circulation, members, and fines, payments and staff. Every table carries a note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building an ILS or library app, students who need a realistic library management system ER diagram for a DBMS project, and anyone looking for a reference model of circulation and holds. This is a schema design reference, not a certified library system.

Tables (24)

Relationships (45)

• book_contributors.author_id references authors.id many to one
• book_contributors.book_id references books.id many to one
• book_subjects.book_id references books.id many to one
• book_subjects.subject_id references subjects.id many to one
• books.publisher_id references publishers.id many to one
• books.series_id references series.id many to one
• copies.book_id references books.id many to one
• copies.branch_id references branches.id many to one
• copies.shelf_location_id references shelf_locations.id many to one
• fines.loan_id references loans.id many to one
• fines.member_id references members.id many to one
• fines.waived_by references staff.id many to one
• holds.book_id references books.id many to one
• holds.fulfilled_loan_id references loans.id many to one
• holds.member_id references members.id many to one
• holds.pickup_branch_id references branches.id many to one
• holds.ready_copy_id references copies.id many to one
• loan_renewals.loan_id references loans.id many to one
• loans.checked_in_by references staff.id many to one
• loans.checked_out_by references staff.id many to one
• loans.checkout_branch_id references branches.id many to one
• loans.copy_id references copies.id many to one
• loans.member_id references members.id many to one
• loans.return_branch_id references branches.id many to one
• members.home_branch_id references branches.id many to one
• members.member_type_id references member_types.id many to one
• notifications.hold_id references holds.id many to one
• notifications.loan_id references loans.id many to one
• notifications.member_id references members.id many to one
• payment_allocations.fine_id references fines.id many to one
• payment_allocations.payment_id references payments.id many to one
• payments.member_id references members.id many to one
• payments.received_by references staff.id many to one
• purchase_order_lines.book_id references books.id many to one
• purchase_order_lines.purchase_order_id references purchase_orders.id many to one
• purchase_orders.branch_id references branches.id many to one
• purchase_orders.ordered_by references staff.id many to one
• purchase_orders.vendor_id references vendors.id many to one
• shelf_locations.branch_id references branches.id many to one
• staff.branch_id references branches.id many to one
• subjects.parent_id references subjects.id many to one
• transfers.copy_id references copies.id many to one
• transfers.from_branch_id references branches.id many to one
• transfers.hold_id references holds.id many to one
• transfers.to_branch_id references branches.id many to one

How to use this template

1. Open the ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the Library Management System database schema?

24 tables and 45 relationships, across 164 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, as PostgreSQL, MySQL, or SQL Server DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.