Milestone: design your own database

What are you building?

A working database for a small community library. Members borrow books, and the library wants to know who borrows what. By the end you will have three linked tables with rules that keep bad data out, a set of sample rows that test those rules, and a query that answers a real question: how many loans each member has taken out, including members who have never borrowed anything.

Everything here comes from earlier lessons. The project is in putting the pieces together yourself: choosing columns, deciding which rules each column needs, and checking the result. There is more than one correct design. The checks look for the table and column names in the task and the rules it lists, and they accept extra columns, extra tables and different sample data.

Give yourself time. Write one table, run, look at Tables, then write the next. A project built in small steps is far easier to fix than one typed in a single go.

How should you plan before typing?

Sketch the tables on paper or in comments first. For each one, write its name, then a line per column with its type and any rule. Ask three questions per column. Can this be missing? If not, it needs NOT NULL. Must it be different on every row? Then UNIQUE. Does it point at a row in another table? Then it needs REFERENCES.

The library has two things (members and books) and one event that connects them (a loan). Things get their own tables. The event gets a table of its own too, with one row per loan holding the ids of the member and the book. Putting a book_id column on members would only allow one book per member, and a list of titles in one text column can't be joined or counted. Those are the two classic design mistakes from 'Designing good tables'.

Decide on formats now as well: dates as YYYY-MM-DD text, returned_on left NULL while a book is out, ids as INTEGER PRIMARY KEY so SQLite numbers new rows for you.

-- not runnable: a sketch, written as comments
-- members: id (key), name (required), email (required, unique), joined_on
-- books:   id (key), title (required), author (required), published_year
-- loans:   id (key), member_id -> members, book_id -> books,
--          loaned_on (required), due_on (required), returned_on (NULL while out)

Which earlier lessons does each part use?

When you get stuck, go back to the lesson that covers that step. Each one has a demo you can reread in a minute.

• Table design and splitting things from events: 'Designing good tables'.
• CREATE TABLE, column types and INTEGER PRIMARY KEY: 'Creating tables'.
• NOT NULL, UNIQUE, CHECK and DEFAULT: 'Rules for your data'.
• Primary keys, foreign keys and REFERENCES: 'Keys and relationships'.
• INSERT with a column list and several rows at once: 'Adding rows'.
• Keeping members with no loans: 'Keeping unmatched rows'.
• Counting per member: 'Grouping rows' and 'Counting and totals'.
• Finding mistakes calmly: 'Debugging queries'.

How do you build the tables step by step?

Create the parent tables first. loans refers to members and books, and while SQLite allows a REFERENCES clause to name a table that doesn't exist yet, inserting into it then fails. Creating members and books first keeps the order easy to follow.

Run after each CREATE TABLE and open the Tables view: the new table should appear with its columns and no rows. If you make a mistake in a column, edit the CREATE statement and run again. Every run starts from a fresh, empty database, so there is no old version to drop.

Notice the small choices in the example. email is both NOT NULL and UNIQUE. published_year has a CHECK so a typo like -2004 is rejected. returned_on has no NOT NULL, because a missing return date carries meaning here: the book is still out.

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  joined_on TEXT NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  published_year INTEGER CHECK (published_year > 0)
);
CREATE TABLE loans (
  id INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id),
  book_id INTEGER NOT NULL REFERENCES books(id),
  loaned_on TEXT NOT NULL,
  due_on TEXT NOT NULL,
  returned_on TEXT
);

How do you add sample rows that test the design?

Sample data has two jobs: fill the tables, and prove the design handles awkward cases. Include a member with no loans, a book borrowed twice, a member with several loans, and loans both returned and still out. Those are the rows that make the final query interesting, and the ones a careless query gets wrong.

Insert parents first: members, then books, then loans. Always write the column list in an INSERT. It protects you when the table's column order isn't the order you remember, which is one of the bugs in Break It. Then open Changes: every new row should be green, in the table you meant.

Try breaking a rule on purpose once. Add a loan with member_id 99, or a second member with Ada's email, and read the message. A rule you have watched fire is a rule you can trust. Then remove the bad row before moving on.

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  joined_on TEXT NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  published_year INTEGER CHECK (published_year > 0)
);
CREATE TABLE loans (
  id INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id),
  book_id INTEGER NOT NULL REFERENCES books(id),
  loaned_on TEXT NOT NULL,
  due_on TEXT NOT NULL,
  returned_on TEXT
);

INSERT INTO members (id, name, email, joined_on) VALUES
  (1, 'Ada Brooks', '[email protected]', '2025-02-10'),
  (2, 'Joel Mensah', '[email protected]', '2025-06-03'),
  (3, 'Ines Ferreira', '[email protected]', '2026-01-21'),
  (4, 'Tariq Aziz', '[email protected]', '2026-09-28');
INSERT INTO books (id, title, author, published_year) VALUES
  (1, 'Small Island', 'Andrea Levy', 2004),
  (2, 'The Overstory', 'Richard Powers', 2018),
  (3, 'Persuasion', 'Jane Austen', 1817),
  (4, 'H Is for Hawk', 'Helen Macdonald', 2014);
INSERT INTO loans (member_id, book_id, loaned_on, due_on, returned_on) VALUES
  (1, 1, '2026-08-01', '2026-08-22', '2026-08-20'),
  (1, 2, '2026-09-02', '2026-09-23', NULL),
  (2, 3, '2026-09-05', '2026-09-26', '2026-09-25'),
  (3, 1, '2026-09-10', '2026-10-01', NULL),
  (1, 4, '2026-09-15', '2026-10-06', NULL);

SELECT * FROM loans;

How do you answer the question?

The library wants loans per member, including members with none. 'Including members with none' points straight to LEFT JOIN: start from members so every member is kept, then attach their loans where they exist. Group by the member's id, since two members could share a name, and count l.id, which is NULL for a member with no loans, so they count as zero.

Sort by the count from highest to lowest, with the name as a tie-breaker so the order is the same on every run. Use the Rows view to watch the steps: after the LEFT JOIN, Tariq has one row full of NULLs; after GROUP BY, each member is one row; COUNT turns Tariq's NULL into 0.

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  joined_on TEXT NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  published_year INTEGER CHECK (published_year > 0)
);
CREATE TABLE loans (
  id INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id),
  book_id INTEGER NOT NULL REFERENCES books(id),
  loaned_on TEXT NOT NULL,
  due_on TEXT NOT NULL,
  returned_on TEXT
);

INSERT INTO members (id, name, email, joined_on) VALUES
  (1, 'Ada Brooks', '[email protected]', '2025-02-10'),
  (2, 'Joel Mensah', '[email protected]', '2025-06-03'),
  (3, 'Ines Ferreira', '[email protected]', '2026-01-21'),
  (4, 'Tariq Aziz', '[email protected]', '2026-09-28');
INSERT INTO books (id, title, author, published_year) VALUES
  (1, 'Small Island', 'Andrea Levy', 2004),
  (2, 'The Overstory', 'Richard Powers', 2018),
  (3, 'Persuasion', 'Jane Austen', 1817),
  (4, 'H Is for Hawk', 'Helen Macdonald', 2014);
INSERT INTO loans (member_id, book_id, loaned_on, due_on, returned_on) VALUES
  (1, 1, '2026-08-01', '2026-08-22', '2026-08-20'),
  (1, 2, '2026-09-02', '2026-09-23', NULL),
  (2, 3, '2026-09-05', '2026-09-26', '2026-09-25'),
  (3, 1, '2026-09-10', '2026-10-01', NULL),
  (1, 4, '2026-09-15', '2026-10-06', NULL);

SELECT m.name, COUNT(l.id) AS loan_count
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
GROUP BY m.id
ORDER BY loan_count DESC, m.name;

How do you check your own work?

Before calling it done, ask the database what it thinks you built. pragma_table_info lists each column with its type, whether it is NOT NULL, and whether it is part of the primary key. pragma_foreign_key_list lists the links from loans to its parents. If a rule you meant to add is missing here, it isn't in the table.

Then check the answer by hand. Count Ada's rows in the loans table in Tables view and compare with her loan_count. Add up all the counts: the total should equal the number of rows in loans. If it is higher, a join is repeating rows; if it is lower, something is being dropped.

Finally, run the rules on purpose, as in the previous section. A design is only checked when you have seen it refuse bad data.

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  joined_on TEXT NOT NULL
);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  published_year INTEGER CHECK (published_year > 0)
);
CREATE TABLE loans (
  id INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id),
  book_id INTEGER NOT NULL REFERENCES books(id),
  loaned_on TEXT NOT NULL,
  due_on TEXT NOT NULL,
  returned_on TEXT
);

SELECT name, type, "notnull", pk
FROM pragma_table_info('loans');

SELECT "from", "table", "to"
FROM pragma_foreign_key_list('loans');

What commonly goes wrong?

Each of these produces either a clear message in the Messages panel or a count you can check by hand. The routine from 'Debugging queries' finds all of them.

• A typo in a REFERENCES table name. SQLite accepts the CREATE and only fails at the first INSERT, with a message about a missing table.
• Inserting loans before members or books, which gives 'FOREIGN KEY constraint failed'.
• INSERT without a column list, so values land in the wrong columns without any error.
• Using JOIN instead of LEFT JOIN in the final query, so the member with no loans disappears.
• Counting with COUNT(*) after a LEFT JOIN, so that member shows 1 instead of 0.
• Putting NOT NULL on returned_on, which makes it impossible to record a book that is still out.
• Grouping by name, which merges two members who share a name.

Where can you take it next?

Once the checks pass, the database is yours to extend. Each idea below uses a lesson you have already done.

• Add a view called books_out listing every loan with no returned_on, with title and member name.
• Add a CHECK so due_on must be later than loaned_on.
• Add an index on loans(member_id) and look at how the query plan changes.
• Write a CASE that labels each loan returned, overdue or on loan, using a fixed 'today'.
• Rank books by how often they are borrowed with a window function.
• Add an authors table and move the author name out of books, then update the queries to match.
• Wrap a book return (an UPDATE plus a log row) in a transaction.

Field notes

• Sketch each table and its columns, types and rules before you type any SQL.
• Create the parent tables members and books before loans, which points at them.
• Add test rows that cover awkward cases, like a member with no loans.
• Start from members with a LEFT JOIN and count l.id so members with no loans show 0.

Easy to get wrong

Without REFERENCES, nothing stops a loan for a member or book that doesn't exist. Without NOT NULL, a loan can belong to nobody.

Without a column list the values fill columns in table order, which is easy to misremember. The column list makes the intent clear and survives table changes.

The inner join drops members with no loans, and COUNT(*) would count their NULL-padded row as 1.

A list of titles in one column can't be joined, counted or kept consistent. Each loan belongs on its own row in loans.

Questions people ask

Do I have to use exactly these columns?

Use the table and column names the task lists, with the rules it lists. Beyond that the design is yours: add columns like phone or isbn, extra tables, or extra CHECK rules, and the checks still pass.

Why does my work disappear between runs?

Each run starts from a fresh copy of the starting database, which is nearly empty here. Your CREATE and INSERT statements run again every time, which is why the whole script lives in query.sql.

Should the members table have a loans column?

No. A loan is its own thing with its own dates, so it gets its own table pointing at the member and the book. That is how one member can have any number of loans.

How would this look in PostgreSQL or MySQL?

Almost identical. PostgreSQL would use GENERATED ALWAYS AS IDENTITY or SERIAL for ids and a real DATE type; MySQL would use AUTO_INCREMENT. The tables, keys and final query stay the same.

What if my design differs from the solution?

That is expected. If the checks pass and you can explain each rule you added, the design is sound. Compare with the solution for ideas, not for a single right answer.