# Library management system data model

> An example library management system data model with ER diagram — books, authors, publishers, physical copies at branches, members, loans, holds and fines — showing a many-to-many relationship, with SQL DDL.

Source: https://entitymodel.com/docs/examples/library-management/

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.

<!-- diagram -->

**[Open this model in EntityModel.com](https://entitymodel.com/#/share/v1.xVrrb-K4Fv9XrNwPd1c37SZQ-uB-uZTSll1Kq4HRaPZqhExiiLfBztpOO-xo_vcrPwJxEmhCmb3SSB1O4vPwefj4d_LNWVC2gsLpOogILNYrGqIYMcc1D2YviHFMidP1XYfTlAXI6TqREAnv_vJLbs1pQFeO6xC4ki_EeM4gW89WkMAlWiEiHNcRWMTy4Ug_BA-bh2Cy5gLJ9ZQtIcF_QaFkOgMl4CETANBXuEpixB3XCREPGE7Miz1gRHbBnNJnDiAJAUxFRBkHP60gWZ8IeiL__uyCJFpzHMAYBDTBiAMowJxBEkSIu2CFVnPEuAtiCgl3QUTjULNbYIL4qeM6mz1x_FPPcR0tp6yu4zpcQJFyp-vAJGH0BYWO6wQMQYHCmdr2ltc6P_H9E6899byu-nfqed7vjuukSVjrPeUFjLjT_e-3zAFyE-QmYZ7EcD0z1GtN1W6biXUiiVxAEkKmNKMxZbOAhpL-j_bgvHd5W7HVypEAEyAiBAIoYEyXp-CpuKsMAY4SyKBActegEAzPU1FWdIal8BjOUWyUBMMbGTBaweF46rgO5rOE4ZWMqme0drqCpch1GPozxQyF2W8TIE7XOTvzPee7uxGE-ZzkpAwn1-OtiMn0w3B855T5pQT_maLsl70RksWJ35a2bcVeXVx6rfOW125fnl3m5evo3yowNb-LGmA-M6-8ZeGQCEbDNJDqAEFBL15ShkW04nm5STqPMY8Qs3f5KSNXbPVWhH9RYhWo3JytEWRFdvoR-Kwf7WDZ8ryrPNMYkmUKl0iH3ZbjyNBBX9MNuw-D28GHwbg_mA3G0-H0s_LZAjFEZGX6ZmI7x1cWlVQkqZhtArAk9fu-bUYkr-4SEVbS9U4SD1VUcazWMifse16l_sT5_kUWF5YGQuWxzKkvrgMZgvK5zsm83qZGlUpCL6PXLAoXvU7_clBRFBLEOCXgNaLglVGBAGUAhVigUFfk3SVAq2aHp1brCGWgZVcBI0v9Kkkba-rhGTmN6ApycH8K-pSt7MCZYyaiYtpcS-IbCeNfdc4bu3uT9WWPP-Ue1XS6f3vePj-vcrrmhckSRDTle-r8AWWoqasripUSWHD2VuS7_f0wnIInhjhv7J-sQdJ9R8VJndFreuiqd9bzvd1tEYipqc4igsI0Nfqg3nM2KyUKp7OiHcFbbSs5tKSCp4ysd7upj4hgMAam8bTqQRgq_-VqwYbyVm-wFXDp-WDyCfieiEDvBVlhGFFiRZ_5XWS-ZfYvH3Q6HeD5F97-sBohEmJihZXqpgKarMsR1dfUmvF01ju76bSqMj7X5a0BXQCoyrsLXrGIABYc0FcC5pBJTi54RomQ_TUlyPTYu8NNcrSDTepshdr18O7A2uB5xYbQ6JiPtw2laazZbWIuyP1W-8zzqqTX7Xvrd7iN0rVmYgaUhFg5P--VLW13GN9RGtr99wy-QBzDudUGDzno5ciZnx8fR4PeuEJPOx7HVIaWuqrJfiNCcWi343JR4yzSV8ByCj1k9JpJ1Lrt3Fy09_RKMmXgpkQHkIW7s0MrZXtXK3SEYtw597yW5XfIwhlJjb0bz0MWgnFGbXZ3SjCu6M6uPM8vCV-kcVw8C27TOD70JKgUPUII9CO7R0MriOOc0IH5XRRYyTBG6DSIEPmPISkEoBRVPMKJCpziHeJh8xhM18nBt4mClOp7xVb-3ptP7-bjaJo34Q-KCQpnVjX4VdHAY64a3PSmg70lpuW1zk48iWdYu_81wQxxm_1AExvzv8j4N8x9WUrKmT_S1Jp53-lf3F5V3ZHUmRlEKHhGIaCpkLd2aBCn3ZkvVbLzXqpznHPxymu1bC80OYUbnbeNCliDUiW3pxiUI0WrCpr6XaNG3LyTlmVFmCJb1E2KGgenQvLs3WFIpKxkxwdDrZJgB9dglYg1eI1wjLIT8dTmT9ArjGcBTRUYu5Wg6KBv6DW90Lw1lVeOcmLda2rNxOr5rVv_siKxdGz9kwOpMeIqseaUMfpq-lPwGiECoM7AOQroSiKUWeOxO_ek1nbISo2Pk3vttudf_eCe8EclXYKD5zSZVbWcT-oReG_nmcQw0OB3jrWigV4uUKfDh8Fk2nt4Oiy3L6d-S6Ppv-eFG9h-K3mSEd5qeuzI_ASxwGTpAoZguHbBIo0XOI5l9WcggCRA8ofdsn7qDaeSd9MEk-OJcoLdaup7If8ggmyJwIIy2bBCDe_FlAuABVrtzh-plB0eUqHj5M_FhecXD4K652STExGuCjWzt7KL5c2gP3zojWRjyFCAzbys5To8gJJnq1FonnYKBwPkhTPBEOrjE48viIWpjUtAXDhrniCuOGcOQiB2gc2qnB0JcR5h8szNsE9QOYrCLJv4uXI4FTAJPwPKwn3N1d82f_o_oNzK9LK4R0MuStwboPbmP1GuEIBsCKgFghhzYRezNxrwCmQ0G4cUQufOkO2g2d6BSgDW4KJ_2S9FzSSd_4ECAYIYco4X2chK1rU3phS1Bz_5a2kjJ8qpTnHQVLgFa3nvB0TpKkkFYoAHWO1etZM2uzt7gXFqtiO3D13ndii3OKeqrO9qIKlMsd59-Dwpvvsgx_4GkLXenfSHxXcnmarFd_sltmXzimvuh5NpcdU95oJWKdO_H45uSjIiHIdMwgcVGzXbfCkx0HsOzAZWzD0rLpvbJ--M9owTd8F8DYaTR3DevjrxgVy2577ZbCJ7cLzb2MtGaiHkN1LfHfUDspSDnybBbm-FUtktaJrj61Ys4BULJgkkuxYsWMWCW6lY9fvGQ_b7d4itIKl8_6-o4v1-JD9tQW_E8jZ-fkJfA8QS8fMefGsXfLpBuN4f3r9hEnI5g8gjqGYSofsBBQzHeIXFnspehcZVQHAHx3kJRlMCC2GuBL47xHthGgvLKfDrTH3IlL__wa9gZGg1b2atXff9yqzZbmhmvJuzOdPSzSmnJbj20qzu5peqsltY6peXTqYfbwbjotyJSEP9KZoluVNePhgPHz8UVyOCVd9aVLvRAaBejlXXITOhckYezuZyVLfhYT7mEvIKJgrDfRnymMBYo7_jrq8_3XuV-H1x4h2C67Vys_yADZUey3iY4xAzpE5wGFf3fsU8-qIEIrwkOh3sUX8eWVQNOZ8ZcyzztleGjZXmtToG9jXnCtv0ExSCIaln3XXp60H5HVv-A8J_g5zCgCFO4xfEARZq1TNCCdcfxu27f5Q2LbuHVOzXZmve2rEN5W_eszeN2959ikAzXZTsMuPrg-JAonuPiwqb7iEHZgJ-DIMqvCUn3Rqt2m9O6cuPOob9JsfoCvYqGSa_5DiWVRs0r4iq2G4yMxLLQ5mddayp9JCB7I8Wc9kcw8ImJCJcKq9Fczbj3zq2XBue1aVVPz2Wf7ZgbgFX10DO9vNsDaofkkEK_6lKHgPU1zDjXn8wzdQcQhZC9eWrCwgVAAKeoEDeuxUSf1BZNMCw7cOiyY18aGDl6sNRPvuhDjRQul06ivYcVDUkEg8-JtWFw8D0TxQTAW7VsXGUw784GMhXfAUih7OFfZoZ4Hprq87HWgVfczT6F00ckiBlx_Jdhi7LDi7CiEEWRDgPZ5n_EmoYfP8f)** to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or [download model.json](/docs/examples/library-management/model.json).

## Entities

| Entity | Type | Description | Primary Key | Display Attribute | Attributes |
| --- | --- | --- | --- | --- | --- |
| Author | Entity | A person who wrote or edited books. | author_id | author_name | 3 |
| Book | Entity | A title in the catalog. Physical copies are separate. | book_id | title | 7 |
| Book Author | Entity | Links books to their authors, in credit order. | book_id | author_order | 3 |
| Branch | Entity | A library location that holds copies. | branch_id | branch_name | 4 |
| Copy | Entity | A physical copy of a book, with its own barcode, kept at one branch. | copy_id | barcode | 6 |
| Fine | Entity | A charge for a late or lost item. | fine_id | amount | 5 |
| Hold | Entity | A member's request to borrow a book when a copy becomes available. | hold_id | placed_at | 6 |
| Loan | Entity | A copy checked out to a member. | loan_id | loaned_on | 7 |
| Member | Entity | A person with a library card. | member_id | full_name | 7 |
| Publisher | Entity | A publishing house. | publisher_id | publisher_name | 2 |
| Genre | Reference | Subject classification for books. | genre_code | genre_name | 2 |
| Language | Reference | Languages, by ISO 639-1 code. | language_code | language_name | 2 |
| Membership Type | Reference | Kinds of library card, with their loan limits. | type_code | type_name | 3 |

## Attributes

### Author

A person who wrote or edited books.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| author_id | INT | PK, Required |  | 210 |  |
| author_name | STRING | Label, Required |  | Thomas H. Cormen |  |
| birth_year | INT |  |  | 1956 |  |

### Book

A title in the catalog. Physical copies are separate.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| book_id | INT | PK, Required |  | 4410 |  |
| isbn | STRING | Required |  | 9780262033848 | ISBN-13. |
| title | STRING | Label, Required |  | Introduction to Algorithms |  |
| publisher_id | INT |  |  | 17 |  |
| publication_year | INT |  |  | 2009 |  |
| language_code | REFERENCE | Required | → language.language_code | en |  |
| genre_code | REFERENCE |  | → genre.genre_code | CS |  |

### Book Author

Links books to their authors, in credit order.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| book_id | INT | PK, Required |  | 4410 |  |
| author_id | INT | PK, Required |  | 210 |  |
| author_order | INT | Label, Required |  | 1 | Position in the author list. |

### Branch

A library location that holds copies.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| branch_id | INT | PK, Required |  | 3 |  |
| branch_name | STRING | Label, Required |  | Central Library |  |
| address | STRING | Required |  | 801 SW 10th Ave |  |
| phone | STRING |  |  | +1 555 0170 |  |

### Copy

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| copy_id | BIGINT | PK, Required |  | 1004410 |  |
| barcode | STRING | Label, Required |  | 31234001004410 |  |
| book_id | INT | Required |  | 4410 |  |
| branch_id | INT | Required |  | 3 |  |
| condition | STRING |  |  | Good |  |
| is_available | BOOLEAN | Required |  | true | Not on loan or held. |

### Fine

A charge for a late or lost item.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| fine_id | BIGINT | PK, Required |  | 7701 |  |
| loan_id | BIGINT | Required |  | 902211 |  |
| amount | DECIMAL(12,2) | Label, Required |  | 2.50 |  |
| reason | STRING | Required |  | Overdue |  |
| paid_on | DATE |  |  |  |  |

### Hold

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| hold_id | BIGINT | PK, Required |  | 33019 |  |
| book_id | INT | Required |  | 4410 |  |
| member_id | INT | Required |  | 56002 |  |
| pickup_branch_id | INT | Required |  | 3 |  |
| placed_at | TIMESTAMP | Label, Required |  | 2026-10-28T12:00:00Z |  |
| status | STRING | Required |  | WAITING | Waiting, ready, fulfilled or cancelled. |

### Loan

A copy checked out to a member.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| loan_id | BIGINT | PK, Required |  | 902211 |  |
| copy_id | BIGINT | Required |  | 1004410 |  |
| member_id | INT | Required |  | 56002 |  |
| loaned_on | DATE | Label, Required |  | 2026-10-20 |  |
| due_on | DATE | Required |  | 2026-11-10 |  |
| returned_on | DATE |  |  |  | Empty while on loan. |
| renewal_count | INT | Required |  | 0 |  |

### Member

A person with a library card.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| member_id | INT | PK, Required |  | 56002 |  |
| card_number | STRING | Required, PII |  | 2900156002 |  |
| full_name | STRING | Label, Required, PII |  | Lee Chen |  |
| email | STRING | PII |  | lee.chen@example.com |  |
| membership_type_code | REFERENCE | Required | → membership_type.type_code | ADULT |  |
| joined_on | DATE | Required |  | 2024-06-11 |  |
| expires_on | DATE | Required |  | 2027-06-11 |  |

### Publisher

A publishing house.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| publisher_id | INT | PK, Required |  | 17 |  |
| publisher_name | STRING | Label, Required |  | MIT Press |  |

### Genre

Subject classification for books.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| genre_code | STRING | PK, Required |  | CS |  |
| genre_name | STRING | Label, Required |  | Computer science |  |

### Language

Languages, by ISO 639-1 code.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| language_code | STRING | PK, Required |  | en |  |
| language_name | STRING | Label, Required |  | English |  |

### Membership Type

Kinds of library card, with their loan limits.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| type_code | STRING | PK, Required |  | ADULT |  |
| type_name | STRING | Label, Required |  | Adult |  |
| max_loans | INT | Required |  | 20 |  |

## Relationships

| Relationship | Source | Target | Cardinality | Forward Label | Reverse Label | Key | Description |
| --- | --- | --- | --- | --- | --- | --- | --- |
| published_by | Book | Publisher | N:1 | Published By | Publishes | book.publisher_id |  |
| credits_book | Book Author | Book | N:1 | Credits | Credited In | book_author.book_id | Books and authors are many-to-many; book_author resolves it and keeps the credit order. |
| credits_author | Book Author | Author | N:1 | Credits | Credited In | book_author.author_id |  |
| copy_of | Copy | Book | N:1 | Copy Of | Has Copy | book_copy.book_id |  |
| kept_at | Copy | Branch | N:1 | Kept At | Holds | book_copy.branch_id |  |
| loan_of | Loan | Copy | N:1 | Of | Loaned In | loan.copy_id |  |
| borrowed_by | Loan | Member | N:1 | Borrowed By | Borrows | loan.member_id |  |
| hold_on | Hold | Book | N:1 | On | Has Hold | hold.book_id | Holds are on the title, not a specific copy. |
| placed_by | Hold | Member | N:1 | Placed By | Places | hold.member_id |  |
| pickup_at | Hold | Branch | N:1 | Pick Up At | Pickup Point For | hold.pickup_branch_id |  |
| charged_for | Fine | Loan | N:1 | Charged For | Incurs | fine.loan_id |  |

## Reference data

### Genre

6 values · Source: Example values

| genre_code | genre_name |
| --- | --- |
| FIC | Fiction |
| MYS | Mystery |
| SCI | Science |
| CS | Computer science |
| HIST | History |
| CHILD | Children |

### Language

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

| language_code | language_name |
| --- | --- |
| en | English |
| es | Spanish |
| fr | French |
| de | German |
| zh | Chinese |

### Membership Type

4 values · Source: Example values

| type_code | type_name | max_loans |
| --- | --- | --- |
| ADULT | Adult | 20 |
| CHILD | Child | 10 |
| STUDENT | Student | 25 |
| SENIOR | Senior | 20 |

## 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.

```sql
-- 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.

```mermaid
---
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.