Library management system data model

This example models a public library: the catalog of books, their authors and publishers, the physical copies held at each branch, library members, loans, holds and fines. It's a classic teaching example for entity-relationship modeling because it has a clear many-to-many relationship and the book-versus-copy distinction.

#Design notes

• A book is not a copy. book is the title in the catalog (ISBN, title, publisher); book_copy is a physical item with a barcode at one branch. Loans are of copies; holds are on books, because a member wants any available copy.
• Books and authors are many-to-many. book_author resolves it and keeps the order authors are credited in.
• Loans keep their history. returned_on is empty while a copy is out, so overdue loans are those past due_on with no return date.
• Membership type is a reference list with its loan limit, so rules live in data rather than code.
• Fines belong to loans, which ties every charge to the item and member involved.

To extend it: add reservation_queue positions, digital_item for e-books, event for library programs, or inter_library_loan.

#Diagram

Library Management System: 10 entities, 3 reference lists and 11 relationships. Each box is an entity with its attributes (PK marks the primary key); solid lines are relationships in crow's foot notation, dashed lines point to reference lists.

Open this model in EntityModel.com to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or download model.json.

#Entities

#Attributes

#Author

A person who wrote or edited books.

#Book

A title in the catalog. Physical copies are separate.

#Book Author

Links books to their authors, in credit order.

#Branch

A library location that holds copies.

#Copy

A physical copy of a book, with its own barcode, kept at one branch.

#Fine

A charge for a late or lost item.

#Hold

A member's request to borrow a book when a copy becomes available.

#Loan

A copy checked out to a member.

#Member

A person with a library card.

#Publisher

A publishing house.

#Genre

Subject classification for books.

#Language

Languages, by ISO 639-1 code.

#Membership Type

Kinds of library card, with their loan limits.

#Relationships

#Reference data

#Genre

6 values · Source: Example values

#Language

5 values · Source: ISO 639-1 (excerpt)

#Membership Type

4 values · Source: Example values

#SQL DDL (PostgreSQL)

Generated from the model. In the app, Export → Code also writes MySQL, Snowflake and Databricks DDL, DBML, dbt schema.yml and data contracts.

-- Library Management System — data model
-- PostgreSQL DDL, generated 2026-11-03
-- Made with EntityModel.com

-- Reference list: Genre
CREATE TABLE genre (
  genre_code TEXT NOT NULL,
  genre_name TEXT NOT NULL,
  PRIMARY KEY (genre_code)
);
COMMENT ON TABLE genre IS 'Subject classification for books.';

-- Reference list: Language
CREATE TABLE language (
  language_code TEXT NOT NULL,
  language_name TEXT NOT NULL,
  PRIMARY KEY (language_code)
);
COMMENT ON TABLE language IS 'Languages, by ISO 639-1 code.';

-- Reference list: Membership Type
CREATE TABLE membership_type (
  type_code TEXT NOT NULL,
  type_name TEXT NOT NULL,
  max_loans INTEGER NOT NULL,
  PRIMARY KEY (type_code)
);
COMMENT ON TABLE membership_type IS 'Kinds of library card, with their loan limits.';

CREATE TABLE book (
  book_id INTEGER NOT NULL,
  isbn TEXT NOT NULL,
  title TEXT NOT NULL,
  publisher_id INTEGER,
  publication_year INTEGER,
  language_code TEXT NOT NULL,
  genre_code TEXT,
  PRIMARY KEY (book_id)
);
COMMENT ON TABLE book IS 'A title in the catalog. Physical copies are separate.';
COMMENT ON COLUMN book.isbn IS 'ISBN-13.';

CREATE TABLE author (
  author_id INTEGER NOT NULL,
  author_name TEXT NOT NULL,
  birth_year INTEGER,
  PRIMARY KEY (author_id)
);
COMMENT ON TABLE author IS 'A person who wrote or edited books.';

CREATE TABLE publisher (
  publisher_id INTEGER NOT NULL,
  publisher_name TEXT NOT NULL,
  PRIMARY KEY (publisher_id)
);
COMMENT ON TABLE publisher IS 'A publishing house.';

CREATE TABLE library_branch (
  branch_id INTEGER NOT NULL,
  branch_name TEXT NOT NULL,
  address TEXT NOT NULL,
  phone TEXT,
  PRIMARY KEY (branch_id)
);
COMMENT ON TABLE library_branch IS 'A library location that holds copies.';

CREATE TABLE book_copy (
  copy_id BIGINT NOT NULL,
  barcode TEXT NOT NULL,
  book_id INTEGER NOT NULL,
  branch_id INTEGER NOT NULL,
  condition TEXT,
  is_available BOOLEAN NOT NULL,
  PRIMARY KEY (copy_id)
);
COMMENT ON TABLE book_copy IS 'A physical copy of a book, with its own barcode, kept at one branch.';
COMMENT ON COLUMN book_copy.is_available IS 'Not on loan or held.';

CREATE TABLE member (
  member_id INTEGER NOT NULL,
  card_number TEXT NOT NULL,
  full_name TEXT NOT NULL,
  email TEXT,
  membership_type_code TEXT NOT NULL,
  joined_on DATE NOT NULL,
  expires_on DATE NOT NULL,
  PRIMARY KEY (member_id)
);
COMMENT ON TABLE member IS 'A person with a library card.';

CREATE TABLE loan (
  loan_id BIGINT NOT NULL,
  copy_id BIGINT NOT NULL,
  member_id INTEGER NOT NULL,
  loaned_on DATE NOT NULL,
  due_on DATE NOT NULL,
  returned_on DATE,
  renewal_count INTEGER NOT NULL,
  PRIMARY KEY (loan_id)
);
COMMENT ON TABLE loan IS 'A copy checked out to a member.';
COMMENT ON COLUMN loan.returned_on IS 'Empty while on loan.';

CREATE TABLE hold (
  hold_id BIGINT NOT NULL,
  book_id INTEGER NOT NULL,
  member_id INTEGER NOT NULL,
  pickup_branch_id INTEGER NOT NULL,
  placed_at TIMESTAMP NOT NULL,
  status TEXT NOT NULL,
  PRIMARY KEY (hold_id)
);
COMMENT ON TABLE hold IS 'A member''s request to borrow a book when a copy becomes available.';
COMMENT ON COLUMN hold.status IS 'Waiting, ready, fulfilled or cancelled.';

CREATE TABLE fine (
  fine_id BIGINT NOT NULL,
  loan_id BIGINT NOT NULL,
  amount NUMERIC(12,2) NOT NULL,
  reason TEXT NOT NULL,
  paid_on DATE,
  PRIMARY KEY (fine_id)
);
COMMENT ON TABLE fine IS 'A charge for a late or lost item.';

CREATE TABLE book_author (
  book_id INTEGER NOT NULL,
  author_id INTEGER NOT NULL,
  author_order INTEGER NOT NULL,
  PRIMARY KEY (book_id, author_id)
);
COMMENT ON TABLE book_author IS 'Links books to their authors, in credit order.';
COMMENT ON COLUMN book_author.author_order IS 'Position in the author list.';

-- Keys between tables
ALTER TABLE book ADD CONSTRAINT uq_book_isbn UNIQUE (isbn);
ALTER TABLE book_copy ADD CONSTRAINT uq_book_copy_barcode UNIQUE (barcode);
ALTER TABLE member ADD CONSTRAINT uq_member_card_number UNIQUE (card_number);
ALTER TABLE book ADD CONSTRAINT fk_book_language_code FOREIGN KEY (language_code) REFERENCES language (language_code);
ALTER TABLE book ADD CONSTRAINT fk_book_genre_code FOREIGN KEY (genre_code) REFERENCES genre (genre_code);
ALTER TABLE member ADD CONSTRAINT fk_member_membership_type_code FOREIGN KEY (membership_type_code) REFERENCES membership_type (type_code);
ALTER TABLE book ADD CONSTRAINT fk_published_by FOREIGN KEY (publisher_id) REFERENCES publisher (publisher_id);
ALTER TABLE book_author ADD CONSTRAINT fk_credits_book FOREIGN KEY (book_id) REFERENCES book (book_id);
ALTER TABLE book_author ADD CONSTRAINT fk_credits_author FOREIGN KEY (author_id) REFERENCES author (author_id);
ALTER TABLE book_copy ADD CONSTRAINT fk_copy_of FOREIGN KEY (book_id) REFERENCES book (book_id);
ALTER TABLE book_copy ADD CONSTRAINT fk_kept_at FOREIGN KEY (branch_id) REFERENCES library_branch (branch_id);
ALTER TABLE loan ADD CONSTRAINT fk_loan_of FOREIGN KEY (copy_id) REFERENCES book_copy (copy_id);
ALTER TABLE loan ADD CONSTRAINT fk_borrowed_by FOREIGN KEY (member_id) REFERENCES member (member_id);
ALTER TABLE hold ADD CONSTRAINT fk_hold_on FOREIGN KEY (book_id) REFERENCES book (book_id);
ALTER TABLE hold ADD CONSTRAINT fk_placed_by FOREIGN KEY (member_id) REFERENCES member (member_id);
ALTER TABLE hold ADD CONSTRAINT fk_pickup_at FOREIGN KEY (pickup_branch_id) REFERENCES library_branch (branch_id);
ALTER TABLE fine ADD CONSTRAINT fk_charged_for FOREIGN KEY (loan_id) REFERENCES loan (loan_id);

-- Reference values
INSERT INTO genre (genre_code, genre_name) VALUES
  ('FIC', 'Fiction'),
  ('MYS', 'Mystery'),
  ('SCI', 'Science'),
  ('CS', 'Computer science'),
  ('HIST', 'History'),
  ('CHILD', 'Children');

INSERT INTO language (language_code, language_name) VALUES
  ('en', 'English'),
  ('es', 'Spanish'),
  ('fr', 'French'),
  ('de', 'German'),
  ('zh', 'Chinese');

INSERT INTO membership_type (type_code, type_name, max_loans) VALUES
  ('ADULT', 'Adult', 20),
  ('CHILD', 'Child', 10),
  ('STUDENT', 'Student', 25),
  ('SENIOR', 'Senior', 20);

#Mermaid

Paste into a mermaid code block on GitHub, GitLab or Notion to show the diagram there.

---
title: Library Management System
---
%% Made with EntityModel.com
erDiagram
  book {
    int book_id PK "Book ID"
    string isbn "ISBN-13."
    string title "Title"
    int publisher_id FK "Publisher ID"
    int publication_year "Publication Year"
    string language_code FK "Language Code"
    string genre_code FK "Genre Code"
  }
  author {
    int author_id PK "Author ID"
    string author_name "Author Name"
    int birth_year "Birth Year"
  }
  publisher {
    int publisher_id PK "Publisher ID"
    string publisher_name "Publisher Name"
  }
  library_branch {
    int branch_id PK "Branch ID"
    string branch_name "Branch Name"
    string address "Address"
    string phone "Phone"
  }
  book_copy {
    bigint copy_id PK "Copy ID"
    string barcode "Barcode"
    int book_id FK "Book ID"
    int branch_id FK "Branch ID"
    string condition "Condition"
    boolean is_available "Not on loan or held."
  }
  member {
    int member_id PK "Member ID"
    string card_number "Card Number"
    string full_name "Full Name"
    string email "Email"
    string membership_type_code FK "Membership Type Code"
    date joined_on "Joined On"
    date expires_on "Expires On"
  }
  loan {
    bigint loan_id PK "Loan ID"
    bigint copy_id FK "Copy ID"
    int member_id FK "Member ID"
    date loaned_on "Loaned On"
    date due_on "Due On"
    date returned_on "Empty while on loan."
    int renewal_count "Renewal Count"
  }
  hold {
    bigint hold_id PK "Hold ID"
    int book_id FK "Book ID"
    int member_id FK "Member ID"
    int pickup_branch_id FK "Pickup Branch ID"
    timestamp placed_at "Placed At"
    string status "Waiting, ready, fulfilled or cancelled."
  }
  fine {
    bigint fine_id PK "Fine ID"
    bigint loan_id FK "Loan ID"
    decimal amount "Amount"
    string reason "Reason"
    date paid_on "Paid On"
  }
  book_author {
    int book_id PK, FK "Book ID"
    int author_id PK, FK "Author ID"
    int author_order "Position in the author list."
  }
  genre {
    string genre_code PK "Genre Code"
    string genre_name "Genre Name"
  }
  language {
    string language_code PK "Language Code"
    string language_name "Language Name"
  }
  membership_type {
    string type_code PK "Type Code"
    string type_name "Type Name"
    int max_loans "Max Loans"
  }
  book }o--|| publisher : "Published By"
  book_author }o--|| book : "Credits"
  book_author }o--|| author : "Credits"
  book_copy }o--|| book : "Copy Of"
  book_copy }o--|| library_branch : "Kept At"
  loan }o--|| book_copy : "Of"
  loan }o--|| member : "Borrowed By"
  hold }o--|| book : "On"
  hold }o--|| member : "Placed By"
  hold }o--|| library_branch : "Pick Up At"
  fine }o--|| loan : "Charged For"
  book }o..|| language : "Language Code"
  book }o..|| genre : "Genre Code"
  member }o..|| membership_type : "Membership Type Code"

#Frequently asked questions

#What are the entities in a library management system?

Book, author, publisher, book copy, branch, member, loan, hold and fine, with lookup tables for genre, language and membership type. Book and author are linked many-to-many through a book_author table.

#Why separate books from book copies?

A library owns several physical copies of the same title. Catalog details (title, ISBN, authors) belong to the book; barcode, location, condition and availability belong to each copy, and loans are recorded against copies.