Skip to content
Tutorials Center
My learning

Topics

Lesson 108 of 125 ยท Projects

Project: Design a Library Database

Design a library database from scratch in SQL. Model books, copies, members and loans, enforce rules with constraints and triggers, seed data and answer real questions.

In the other projects you query a database someone else built. In this one you're the designer. A small neighborhood library wants to stop tracking loans in a spreadsheet, and you'll turn their rules into tables, constraints, indexes, triggers and a view, load sample data, and then prove the design works by answering the questions the librarians actually ask.

Good database design is mostly about one idea: make wrong data impossible to store. Every rule the database enforces is a bug your application can't have. You'll see that idea at every stage.

This project draws on CREATE TABLE, keys, constraints, foreign keys, many-to-many relationships, indexes, triggers, views and transactions. There's no sample database this time: every example builds the library from nothing, so each one is complete on its own.

The requirements

Here is what the head librarian told you, in her words:

  • "We have books. Each book has an ISBN, a title, a genre and a publication year. Some books have several authors."
  • "We often own more than one copy of a popular book. Each copy has a barcode sticker. Copies wear out, and sometimes they get lost."
  • "Members sign up with a name and an email. Most can borrow 3 books at a time, some (teachers) more."
  • "A loan lasts 21 days. We need to know who has what, what's overdue, and the history of every copy."
  • "A copy can't be lent to two people at once, obviously, and we can't lend a lost copy."

Requirements like these are full of nouns and verbs. The nouns usually become tables, and the rules ("can't", "at most", "each") become constraints.

Stage 1: find the entities

Reading the requirements carefully gives six tables. The interesting decisions are the ones a quick reading would miss:

Table One row isโ€ฆ Why it exists
authors a person who writes books An author writes many books, so the name isn't repeated on every book.
books a title in the catalog (one ISBN) The facts that are the same for every copy.
book_authors "this author worked on this book" Books have several authors and authors have several books: a many-to-many relationship needs a junction table.
copies one physical item with a barcode You lend copies, not titles. Two copies of one book can be in different states.
members a library card holder Each member has their own loan limit.
loans one copy lent to one member once A loan is an event with dates, so it gets its own table and the history is kept forever.

The split between books and copies is the decision that matters most. If you put a barcode column in books, a second copy of the same book would need a second row with the same ISBN, title and genre: duplicated facts that can drift apart. That's the kind of problem normalization is about.

loans deliberately keeps returned loans. The current state ("who has copy C0001?") is just the loans with no return date, and the history comes for free. Updating a borrowed_by column on copies instead would throw that history away.

Stage 2: create the tables

Now translate each table into SQL. Read the notes after the example for the reasoning behind each constraint.

SQL The library schema

Press Run (or Ctrl + Enter) to see the result here.

The final query reads the catalog of the database itself and lists six tables and three indexes. Here's why the schema looks the way it does:

  • PRAGMA foreign_keys = ON comes first. SQLite only enforces REFERENCES constraints when this is switched on for the connection, as explained in Foreign Keys. PostgreSQL, MySQL (InnoDB) and SQL Server always enforce them.
  • STRICT makes SQLite reject values of the wrong type, such as the text 'soon' in an INTEGER column. Without it, SQLite would store the text. See Data Types.
  • isbn ... UNIQUE CHECK (length(isbn) = 13): the ISBN is a natural key, so it must be unique, but the table still uses a small integer id as its primary key. ISBNs are long, and older books have 10-digit ones. The keys lesson discusses this surrogate-versus-natural choice.
  • PRIMARY KEY (book_id, author_id) in the junction table is a composite key. It also stops the same author from being linked to the same book twice.
  • ON DELETE CASCADE on book_authors.book_id: if a book is removed from the catalog, its author links go too. The other foreign keys use the default (NO ACTION), so you can't delete a member who has loans, which is what a library wants.
  • email ... UNIQUE COLLATE NOCASE: [email protected] and [email protected] are the same address, so uniqueness ignores letter case.
  • CHECK (date(joined_on) IS joined_on) is a compact SQLite idiom for "this is a real date in YYYY-MM-DD form". date() returns NULL for text that isn't a date at all ('soon', '2025-13-01') and rolls impossible days over into the next month, so date('2025-02-30') is '2025-03-02'. Either way the result differs from the stored text and the check fails. For a NULL column, date(NULL) IS NULL is true, so missing return dates are still allowed.
  • CHECK (due_on > loaned_on) and CHECK (returned_on >= loaned_on) compare ISO date strings, which sort in date order. When returned_on is NULL, the comparison gives NULL, and a CHECK only rejects a row when the result is false, so open loans pass.
  • one_open_loan_per_copy is a partial unique index: it only contains loans with no return date, so each copy can appear in it at most once. A copy can have many past loans but only one open loan. This single line enforces "a copy can't be lent to two people at once".
  • loans_by_member and copies_by_book index the foreign keys that queries will join and filter on. SQLite doesn't index foreign keys automatically.

Not every rule fits in a CHECK. "A member can borrow at most max_loans books" depends on other rows, and "you can't lend a lost copy" depends on another table. CHECK constraints can only see the row being written. You'll enforce those two rules with triggers in Stage 6.

Stage 3: prove the constraints work

A constraint you haven't tested is a constraint you hope works. Try to break each rule on purpose. The three examples below fail on purpose: each one ends with a statement that violates a rule, and the run stops with the database's error message. They only create the tables they need.

First, a duplicate email that differs only in letter case:

SQL Duplicate email (fails on purpose)

Press Run (or Ctrl + Enter) to see the result here.

The first insert succeeds and the second fails with UNIQUE constraint failed: members.email. Remove COLLATE NOCASE and run it again: both rows go in, and the library has two accounts for one person.

Next, an impossible date. February 2025 has 28 days:

SQL Invalid date (fails on purpose)

Press Run (or Ctrl + Enter) to see the result here.

The SELECT shows why the check works: SQLite quietly turns '2025-02-30' into '2025-03-02'. Because that isn't the text being stored, the insert fails with CHECK constraint failed: date(joined_on) IS joined_on. Without the check, the library would store a date that doesn't exist. Try '2025-2-3' too: date() only accepts two-digit months and days, so it returns NULL and the row is rejected.

Finally, the most important rule: one copy, one open loan. To keep the example short it creates a cut-down loans table with only the columns the rule needs:

SQL Lending a copy twice (fails on purpose)

Press Run (or Ctrl + Enter) to see the result here.

The first two inserts report "OK, 2 rows affected." and "OK, 1 row affected.", and the last one fails with UNIQUE constraint failed: loans.copy_id. A plain UNIQUE (copy_id) would have rejected the second finished loan. The WHERE clause on the index is what makes the rule exactly right.

Stage 4: seed the data

With the rules in place, load a small but realistic data set: 7 books, 9 copies (one lost), 5 members and 11 loans, some returned on time, some late, some still out. Then run sanity checks before trusting it. This example holds the complete schema and data that every later stage starts from:

SQL Seed data and sanity checks

Press Run (or Ctrl + Enter) to see the result here.

The counts come back as expected, with 6 open loans. The second query is a check the schema can't enforce (a book with zero authors is a valid row), so you test it with an anti-join. It returns 0 rows, which is the result you want: a data check that finds nothing is a passing test.

The loan dates were chosen so that every kind of situation exists. A test data set with only "happy" rows would never reveal a bug in your overdue report.

Throughout this project, "today" is 2025-11-20, written as a literal. In a real system you'd use date('now') (or CURRENT_DATE), but a fixed date makes the examples give the same answers every time you run them. A good habit for any report you want to test.

Stage 5: answer the librarians' questions

A design is only good if it makes the everyday questions easy. Here are four of them.

"What do we have, and what's on the shelf?"

The catalog view joins books to their authors through the junction table. A copy is available when it isn't lost and has no open loan:

SQL Catalog and availability

Press Run (or Ctrl + Enter) to see the result here.

Practical Databases and Kitchen Chemistry list two authors each, joined into one string. group_concat(x, ', ' ORDER BY x) with an ORDER BY inside the call needs SQLite 3.44 or newer; PostgreSQL and SQL Server spell it string_agg(name, ', ' ORDER BY name) and STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name).

The availability report shows that only The Salt Orchard and Practical Databases have a copy on the shelf right now. The Quiet Engine has one copy, but it's lost. In SQLite a condition like c.condition <> 'lost' AND NOT EXISTS (...) evaluates to 1 or 0, so SUM counts the available copies. In other databases, write SUM(CASE WHEN ... THEN 1 ELSE 0 END).

"What's overdue, and what do they owe?"

The fine is 0.25 per day late. Date arithmetic in SQLite uses julianday(), which turns a date into a day number, so subtracting two of them gives days:

SQL Overdue loans and fines

Press Run (or Ctrl + Enter) to see the result here.

Two loans are overdue, both Chiara Rossi's: Night Trains by 20 days (5.00) and The Salt Orchard by 5 days (1.25). Bruno's loan due on 2025-11-22 isn't overdue yet, which is why you test with a data set that includes loans on both sides of "today". The WHERE clause compares the raw due_on column with a constant, so an index on due_on could be used; wrapping the column in a function would prevent that, as Writing Fast Queries explains.

Popularity counts loans per book, keeping books that were never borrowed. The member summary uses COUNT(column), which skips NULLs, to separate open loans from returned ones:

SQL Popularity and member history

Press Run (or Ctrl + Enter) to see the result here.

Practical Databases and The Salt Orchard were each borrowed 3 times, and The Quiet Engine never was (it's lost). In the member summary, Chiara has 3 open loans, Ada and Deniz each returned one book late, and Emeka hasn't borrowed anything yet. COUNT(*) FILTER (WHERE ...) counts only the rows that match. It's standard SQL and works in SQLite and PostgreSQL; elsewhere, use SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Notice the two LEFT JOINs in the popularity query. With inner joins, The Quiet Engine and Emeka would disappear, and "never borrowed" is often the most useful answer. Chaining joins like this is safe here because each loan belongs to exactly one copy, so no row is counted twice; Joining Many Tables covers the cases where it isn't.

Stage 6: rules that need triggers

Two rules are still unenforced: the loan limit and "no lending lost copies". Both depend on rows other than the one being inserted, so they need triggers. A BEFORE INSERT trigger runs before the row is written and can cancel the statement with RAISE(ABORT, 'message').

This stage also adds a view called copy_status, so the front desk can ask "where is every copy?" without rewriting the join. Then it runs the two everyday operations: checking out a book and returning one.

SQL Triggers, a view, check-out and return

Press Run (or Ctrl + Enter) to see the result here.

The new loan gets id 12 and is due on 2025-12-11, computed by date('2025-11-20', '+21 days'). The return closes loan 7. In the final table, C0006 is now on loan, both copies of The Salt Orchard are available, and C0009 is lost.

A few details worth noticing:

  • The view uses a LEFT JOIN with l.returned_on IS NULL in the ON clause, not in WHERE. In ON, it means "attach the open loan if there is one"; in WHERE, it would remove every copy without an open loan. The LEFT JOIN lesson explains this difference in detail.
  • The UPDATE includes AND returned_on IS NULL, so returning a book twice doesn't overwrite the original return date.
  • The check-out is wrapped in BEGIN / COMMIT. On its own the insert is already atomic, but a real check-out often does more (record a fee, write an audit row), and a transaction makes all of it succeed or fail together.
  • RETURNING hands back the generated id and computed due date, so an application doesn't need a second query.

Now break the loan limit on purpose. Emeka may borrow one book; the second check-out must fail:

SQL Loan limit (fails on purpose)

Press Run (or Ctrl + Enter) to see the result here.

The first insert succeeds; the second stops with member has reached their loan limit. Change the second insert's copy_id to 9 and member_id to 4 and you'll get this copy is marked as lost instead. Change it to copy 1 for member 4 and the partial unique index answers with UNIQUE constraint failed: loans.copy_id, because Chiara still has that copy.

Triggers enforce rules for every program that writes to the database, which is their strength. But they're invisible when you read an INSERT, so document them, keep them small, and test them like this. Under heavy concurrent writes in server databases, a count-then-insert check like loans_check_limit also needs the right isolation level or locking to be airtight. SQLite allows only one writer at a time, so it's safe here.

What you built

Step back and compare the design with the librarian's rules:

Rule Enforced by
ISBNs are unique and 13 characters UNIQUE + CHECK on books.isbn
One account per email, ignoring case UNIQUE COLLATE NOCASE
Dates are real dates; due after loaned CHECK constraints on loans
A copy is lent to one person at a time partial unique index one_open_loan_per_copy
No lending lost copies trigger loans_check_copy
At most max_loans open loans trigger loans_check_limit
Loans point to real copies and members foreign keys + PRAGMA foreign_keys = ON

Every rule lives in the database, so a bug in the website, a hurried script or a typo in a manual INSERT can't create a lent-out lost book or a double loan.

Ideas to extend it

  • Holds: add a holds table (member, book, placed_on) so members can reserve a title that's out, and a query that lists who's next for each book.
  • Real ISBN validation: replace the length check with the ISBN-13 checksum. Hint: a recursive CTE or a generated column can walk the 13 digits.
  • Fines table: when a late loan is returned, a trigger inserts a row into fines; add a paid_on column and a report of unpaid fines per member.
  • Renewals: allow extending due_on once, unless someone has a hold on the book. Where would you store the renewal count?
  • Audit log: record every change to loans in a loan_history table with an AFTER UPDATE trigger.
  • Soft deletes: instead of deleting members, add left_on and make the view and reports ignore former members.
  • Port it: rewrite the schema for PostgreSQL (GENERATED ALWAYS AS IDENTITY, DATE columns, a partial unique index works the same way) and compare it with the PostgreSQL lesson.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does the design have separate books and copies tables?

    Title facts (ISBN, title, genre) are the same for every copy. Keeping them in books avoids repeating them, while copies holds what differs per item.
  2. 2. What does CREATE UNIQUE INDEX ... ON loans (copy_id) WHERE returned_on IS NULL allow?

    The partial index only contains rows with no return date, so uniqueness applies to open loans only. Finished loans are not in the index.
  3. 3. A loan row has returned_on = NULL. Does CHECK (returned_on >= loaned_on) reject it?

    Comparing with NULL gives NULL (unknown). A CHECK constraint fails only on false, so open loans are accepted.
  4. 4. Why is the loan limit enforced with a trigger instead of a CHECK constraint?

    A CHECK expression is evaluated on the new row alone. Counting other rows needs a subquery, which CHECK does not allow, so a BEFORE INSERT trigger does it.
  5. 5. In the copy_status view, what would happen if l.returned_on IS NULL moved from the ON clause to a WHERE clause?

    In WHERE, the filter runs after the join. A copy whose loans all have a return date produces only rows that fail the test, so the copy vanishes. A copy with no loans at all survives, because its NULL-filled row passes.

Summary

  • Turn requirements into tables by finding the nouns; turn "each", "at most" and "can't" into constraints.
  • Separate the catalog (books) from physical items (copies), and record events (loans) as rows so history is kept.
  • Many-to-many relationships need a junction table with a composite primary key.
  • CHECK, UNIQUE, NOT NULL, foreign keys and partial unique indexes catch most bad data; triggers handle rules that look at other rows.
  • Test every constraint by trying to break it, and seed data that covers awkward cases.
  • Views and RETURNING keep everyday queries and operations short.

Next: Project: HR Analytics, where you'll analyze a company's headcount, salaries and org chart.