Library System

Track books, members, and borrowing history with proper relations.

Foreign KeysJoinsViews

Overview

A library needs to answer two very different questions about the same event: "is this book currently out?" and "who has borrowed this book, ever?" A naive design might add an `is_borrowed` flag directly to `books` and a `borrowed_by` column pointing at the current borrower — but that only ever remembers the *most recent* borrow, overwriting history every time a book comes back and goes out again. The fix is a `borrow_records` table where every borrow gets its own row, and "currently checked out" becomes a query (does this row have a `NULL` return date?) instead of a column that has to be kept in sync by hand.

By the end of this tutorial you will have a three-table schema — `books`, `members`, and `borrow_records` — where a nullable `return_date` column doubles as the "is this book still out" flag, plus a `CREATE VIEW` that packages the "find every overdue book" query into something that can be queried on its own, as if it were a table.

What You'll Build

A `books` table tracking title, author, ISBN, and total/available copy counts.
A `members` table with a unique email constraint and a join date.
A `borrow_records` table with a due date and a nullable return date for books still checked out.
Foreign keys tying every borrow record back to a real book and a real member.
A `CREATE VIEW` named `overdue_books` that surfaces every currently-overdue loan.
Join queries reconstructing one member's full borrowing history.

Prerequisites

CREATE TABLE basics — columns, data types, `NOT NULL`, and nullable columns.
Primary keys and foreign keys — how `FOREIGN KEY ... REFERENCES` links two tables.
Basic SELECT — `WHERE`, `ORDER BY`, and simple `JOIN`.
Date comparisons — comparing a `DATE` column against `CURDATE()`.
The idea that `NULL` means "no value," and how `IS NULL`/`IS NOT NULL` test for it.

Project Structure

`books` and `members` are independent entities. `borrow_records` is the table that connects them, and — just like `enrollments` in the student database — it is the only table with foreign keys, so `books` and `members` never need to change when a new borrow happens.

Table	Purpose	Key Columns
books	One row per book title in the catalog	book_id (PK), isbn (UNIQUE), available_copies
members	One row per library member	member_id (PK), email (UNIQUE)
borrow_records	One row per borrow event	record_id (PK), book_id (FK), member_id (FK), due_date, return_date (nullable)

Step 1: Design the Books Table

`total_copies` and `available_copies` are tracked separately because a library can own more than one physical copy of the same title. `available_copies` is what gets checked before allowing a new borrow, and decremented/incremented as copies go out and come back — the `CHECK` constraint stops it from ever being pushed below zero by a bug.

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,     -- Surrogate key referenced by borrow_records.book_id
    isbn VARCHAR(20) NOT NULL UNIQUE,           -- Publisher-assigned identifier, must be unique per title
    title VARCHAR(150) NOT NULL,
    author VARCHAR(100) NOT NULL,
    total_copies INT NOT NULL DEFAULT 1,        -- How many physical copies the library owns
    available_copies INT NOT NULL DEFAULT 1,    -- How many of those copies are currently on the shelf
    CHECK (available_copies >= 0),              -- Can never go negative, no matter what decremented it
    CHECK (available_copies <= total_copies)    -- Can never exceed the total the library actually owns
);

Step 2: Design the Members Table

Same surrogate-key-plus-unique-email pattern used in the previous two projects — it shows up this often because it is genuinely the right default for "a person who has an account."

CREATE TABLE members (
    member_id INT AUTO_INCREMENT PRIMARY KEY,   -- Surrogate key referenced by borrow_records.member_id
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,         -- No two members may share an account email
    join_date DATE NOT NULL
);

Step 3: Design the Borrow Records Table

This is the table the whole design hinges on. `due_date` is always set the moment a book goes out, but `return_date` starts out `NULL` and only gets a value once the book actually comes back — which means `return_date IS NULL` is, by itself, a complete definition of "this book is still checked out." Combine that with `due_date < CURDATE()` and you have "this book is overdue" as a single, always-correct expression, computed on the fly instead of stored (and risking going stale) anywhere else.

CREATE TABLE borrow_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,   -- Own surrogate key for this specific borrow event
    book_id INT NOT NULL,                       -- FK: which book was borrowed
    member_id INT NOT NULL,                     -- FK: who borrowed it
    borrow_date DATE NOT NULL,                  -- The date the book was checked out
    due_date DATE NOT NULL,                     -- The date the book is expected back
    return_date DATE DEFAULT NULL,              -- NULL = still checked out; a real date = returned on that date
    FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE,      -- Deleting a book title also drops its history
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE -- Deleting a member also drops their history
);

Why not a boolean `is_returned` column instead?

A boolean would answer "returned or not," but a nullable `return_date` answers that *and* records exactly when it happened, in one column — no second column to keep in sync, and no risk of the two disagreeing with each other.

Step 4: Insert Sample Data

Two of the sample borrow records below are left with `return_date = NULL` on purpose — one still within its due date, and one already overdue — so the view built in the next step has something real to find.

INSERT INTO books (isbn, title, author, total_copies, available_copies) VALUES
('978-0132350884', 'Clean Code', 'Robert C. Martin', 2, 1),
('978-0201633610', 'Design Patterns', 'Erich Gamma et al.', 1, 0),
('978-0596007126', 'Head First Design Patterns', 'Eric Freeman', 2, 2);

INSERT INTO members (full_name, email, join_date) VALUES
('Sanjay Kumar', 'sanjay.kumar@example.com', '2025-01-15'),
('Meera Iyer', 'meera.iyer@example.com', '2025-03-20');

-- Assume today's date is 2026-08-09 for the overdue example that follows
INSERT INTO borrow_records (book_id, member_id, borrow_date, due_date, return_date) VALUES
(1, 1, '2026-07-01', '2026-07-15', '2026-07-14'),  -- Returned on time
(2, 2, '2026-07-20', '2026-08-03', NULL),           -- Still out, and past its due date: OVERDUE
(1, 2, '2026-08-05', '2026-08-19', NULL);           -- Still out, but not yet due

Step 5: Create a View for Overdue Books

A `VIEW` stores the *query*, not the results — every time `overdue_books` is selected from, MySQL re-runs the underlying `SELECT` against the live tables. That means the view never needs updating by hand as records change; it is always as current as `borrow_records` itself. Packaging this logic into a view also means every part of the application can say `SELECT * FROM overdue_books` instead of re-writing the same three-table join and `WHERE` clause everywhere it is needed.

CREATE VIEW overdue_books AS
SELECT
    br.record_id,
    m.full_name AS member_name,
    b.title AS book_title,
    br.due_date,
    DATEDIFF(CURDATE(), br.due_date) AS days_overdue   -- How many days past due, computed fresh every time
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
JOIN members m ON br.member_id = m.member_id
WHERE br.return_date IS NULL          -- Still checked out...
  AND br.due_date < CURDATE();        -- ...and past its due date

-- The view can now be queried exactly like a table
SELECT * FROM overdue_books;

record_id	member_name	book_title	due_date	days_overdue
2	Meera Iyer	Design Patterns	2026-08-03	6

Step 6: Query Borrowing History

The same `borrow_records` table also answers the opposite question — not "what is overdue right now," but "what has this member ever borrowed," returned books included.

SELECT
    b.title,
    br.borrow_date,
    br.due_date,
    br.return_date
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
JOIN members m ON br.member_id = m.member_id
WHERE m.full_name = 'Meera Iyer'
ORDER BY br.borrow_date;

title	borrow_date	due_date	return_date
Design Patterns	2026-07-20	2026-08-03	NULL
Clean Code	2026-08-05	2026-08-19	NULL

Complete Schema

The full schema, including the view, ready to run top to bottom.

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    isbn VARCHAR(20) NOT NULL UNIQUE,
    title VARCHAR(150) NOT NULL,
    author VARCHAR(100) NOT NULL,
    total_copies INT NOT NULL DEFAULT 1,
    available_copies INT NOT NULL DEFAULT 1,
    CHECK (available_copies >= 0),
    CHECK (available_copies <= total_copies)
);

CREATE TABLE members (
    member_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    join_date DATE NOT NULL
);

CREATE TABLE borrow_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    member_id INT NOT NULL,
    borrow_date DATE NOT NULL,
    due_date DATE NOT NULL,
    return_date DATE DEFAULT NULL,
    FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE
);

CREATE VIEW overdue_books AS
SELECT
    br.record_id,
    m.full_name AS member_name,
    b.title AS book_title,
    br.due_date,
    DATEDIFF(CURDATE(), br.due_date) AS days_overdue
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
JOIN members m ON br.member_id = m.member_id
WHERE br.return_date IS NULL
  AND br.due_date < CURDATE();

Sample Queries

Two more queries a librarian would run day to day: which titles are on the shelf right now, and how many times each book has ever been borrowed.

-- Titles currently available to borrow
SELECT title, author, available_copies
FROM books
WHERE available_copies > 0
ORDER BY title;

title	author	available_copies
Clean Code	Robert C. Martin	1
Head First Design Patterns	Eric Freeman	2

-- How many times each book has ever been borrowed, most-borrowed first
SELECT b.title, COUNT(br.record_id) AS times_borrowed
FROM books b
LEFT JOIN borrow_records br ON b.book_id = br.book_id   -- LEFT JOIN keeps books that have never been borrowed
GROUP BY b.book_id, b.title
ORDER BY times_borrowed DESC;

title	times_borrowed
Clean Code	2
Design Patterns	1
Head First Design Patterns	0

Extend This Project

Add a `fines` table that computes a late fee from `overdue_books.days_overdue`.
Add a `reservations` table so members can reserve a book that is currently checked out.
Add a trigger that decrements `books.available_copies` automatically on borrow and increments it on return.
Add an index on `borrow_records(member_id, return_date)` to speed up per-member history lookups.
Create a second view, `active_members`, showing members with at least one currently-borrowed book.

Summary

You built a schema where a single nullable column, `return_date`, does double duty as both "is this book out right now" and "when did it come back" — no second flag column to keep synchronized, and no history lost when a book is returned and borrowed again. The `overdue_books` view packaged a three-table join with a date comparison into something that queries like a table, which is exactly what views are for: hiding query complexity behind a name, not storing a separate copy of the data.