Crowsfoot

Library database schema: ER diagram and SQL

A library tracks what it owns, who wrote it, who is allowed to borrow, and what is currently out. The book-to-author link is many-to-many, since books can have several authors.

Open this schema in the editor Jump to the SQL
authors PK id bigint name varchar books PK id bigint title varchar isbn varchar published_year int members PK id bigint name varchar email varchar joined_on date book_authors PK book_id bigint PK author_id bigint loans PK id bigint FK book_id bigint FK member_id bigint loaned_on date due_on date returned_on date

Tables in this schema

authors

People who wrote books.

books

Titles in the catalogue.

members

Library cardholders.

book_authors

A junction table linking books and authors. A book can have several authors, and an author several books.

loans

Each time a member borrows a book. returned_on stays empty until it comes back.

How the tables relate

  • book_authors is a junction table between books and authors: many-to-many.
  • loans.book_id references books.id: one row in books can have many rows in loans.
  • loans.member_id references members.id: one row in members can have many rows in loans.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE authors (
  id bigint PRIMARY KEY,
  name varchar(160) NOT NULL
);

CREATE TABLE books (
  id bigint PRIMARY KEY,
  title varchar(200) NOT NULL,
  isbn varchar(20),
  published_year int
);

CREATE TABLE members (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL,
  email varchar(255) NOT NULL,
  joined_on date NOT NULL
);

CREATE TABLE book_authors (
  book_id bigint NOT NULL REFERENCES books (id),
  author_id bigint NOT NULL REFERENCES authors (id),
  PRIMARY KEY (book_id, author_id)
);

CREATE TABLE loans (
  id bigint PRIMARY KEY,
  book_id bigint NOT NULL REFERENCES books (id),
  member_id bigint NOT NULL REFERENCES members (id),
  loaned_on date NOT NULL,
  due_on date NOT NULL,
  returned_on date
);

Design notes

  • If the library owns several copies of a title, add a copies table between books and loans.
  • returned_on is empty while a book is out. An index on (member_id, returned_on) makes “what does this member still have?” fast.
  • isbn deserves a unique index.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.