You have the brief (12-01) and the contract (12-02). What's left is to build it, and building it has an order: if you write the queries before you have data, you can't check them; if you create the indexes before you have the queries, you're inventing them; and if you start with CREATE TABLE without having drawn anything, the third table will force you to redo the first two.

This lesson is the build guide in seven steps: the complete model, the whole DDL, the technique for generating coherent data, the method for writing queries without going wrong and the criteria for deciding indexes and encapsulation. What it doesn't give you is the fifteen queries from the brief, already written: that's 12-04, and getting there without having tried is throwing the project away.

1. Step 1 — From the brief to the model
2. Step 2 — The DDL: 01-schema.sql
3. Step 3 — Coherent test data: 02-data.sql
4. Step 4 — The queries: a working method
5. Step 5 — The indexes, after the queries
6. Step 6 — Views, procedures and triggers
7. Step 7 — Security and delivery
8. Indicative schedule
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

Twelve tables and sixteen relationships: fourteen 1:N, one N:M resolved with the bridge table works_authors, one self-referencing on librarians and one 1-to-0..1 between loans and fines. The shape is GreenStore's; the content isn't. The columns are in step 2's DDL.

1. Why copies is a table and not a counter. Argued in 12-01: the copy has its own branch, its own status and its own history. The technical consequence is more forceful than the conceptual argument: without that table, the FK of loans would point at works and it would be impossible to know which of the three came back. It isn't a matter of style: the model simply can't represent the fact.

2. Why loans stores due_date instead of computing it. It could be deduced (loan_date + term(member type)), and it isn't: the argument is identical to the one for order_lines.unit_price (01-06, 11-02), it's a historical fact. If Marta moves from general to senior, her old loans would be recomputed at 30 days and one that came back late would become on time. Besides, it moves with renewals (BR-03), so it isn't even a function of the initial date. Rule: if the value depends on a past condition that can change, store it.

3. Why fines are a table and not a column. They have a life of their own —they're issued, they're paid, they can be waived—, and that's three more columns, null for 83 % of loans; a table lets you count and sum without walking through every loan; and tomorrow there could be fines that don't come from a delay (a damaged book), and it would be enough to make the FK nullable. A 1-to-0..1 relationship, enforced with UNIQUE (loan_id).

4. How the reservation queue is modelled. With reservation_date and nothing else. The three options:

5. Why the loan's state isn't stored and the copy's is. They look symmetrical and they aren't. The loan's (active, overdue, returned) is a function of two dates and the clock: storing it would force a nightly job to mark the overdue ones. The copy's (available, repair, lost, withdrawn) is a physical fact that somebody decides and that isn't deduced from anything. That's why copies.status doesn't include on_loan: that one is deduced, and having it would mean storing the same thing twice with two ways of contradicting itself.

The order is 05-01's: DROP from children to parents, CREATE from parents to children.

Eight of the twelve tables hold no surprises: they're the exact pattern of categories, suppliers and returns in GreenStore, so they're summarised in this table —columns, nullability and named constraints— and writing their CREATE TABLE will take you five minutes:

And these are the four that do have decisions inside the CREATE TABLE itself:

It's the technical centrepiece of the project. A partial index (08-02) is built only over the rows that meet a condition; when it's also UNIQUE, uniqueness is only required among those rows. It reads literally: "among loans not yet returned, copy_id is unique". Its two tests:

Three properties make it the right solution and not a trick. It's declarative: the engine enforces it, not the application, so it survives scripts, another application and a console left open at three in the morning. It's tiny: with 300,000 historical loans and 400 active ones, the index has 400 entries, not 300,000. And it speeds things up too, because any query with WHERE return_date IS NULL can use it: a constraint that throws in a useful index for free.

And its limit, which has to be stated in the report: it prevents two active loans, not two loans that overlap in the past. If somebody registers today a January loan, already returned, that overlaps with another January one, the index doesn't see it. The complete solution is in 12-04.

Test data has two goals that get in each other's way: being enough for the queries to mean something and small enough to read with your own eyes. The split: by hand, with explicit ids, everything that appears in the queries —that way "loan 29" always means the same thing—; with generate_series, the volume for measuring performance.

The detail that decides whether this works: every generated row carries a return_date. If you left nulls at random, the partial unique index would abort the load as soon as two loans of the same copy were left open. Far from being a nuisance, it's proof that the constraint works.

GreenStore had deliberate gaps —customers with no orders, products never sold, orders with no employee— and the lessons needed them. Now it's your turn to put them in: without them, half a badly written query returns the same as a well written one and you never find out.

As in 01-06, check before you write a single query. The reference data set has 3 branches, 6 subjects, 5 publishers, 10 authors, 8 librarians, 12 works, 15 rows of works_authors, 20 copies, 15 members, 36 loans, 5 reservations and 6 fines. And the gaps, in a single query:

1. Start with the FROM, not the SELECT. Decide first which table one row of the result comes from: one per work? Then FROM is works. One per loan? Then it's loans. Everything else joins onto it.
2. Check the row count after every JOIN. If joining copies with loans takes you from 20 rows to 36, you've changed granularity: it may be correct, but you have to know it, because from there on COUNT(*) counts loans, not copies.
3. Decide JOIN or LEFT JOIN by asking about the zeros. Do I want to see the work with no copies, the branch with no loans, the empty month? Then LEFT JOIN — and remember that COUNT(*) counts the phantom row and COUNT(right_hand_column) doesn't (04-04). And validate the total by two routes: QR-15's pivot must add up to the same as SELECT COUNT(*) FROM loans; if it doesn't match, don't argue with the query, it's wrong (11-04).

"Tell me where the three copies of The Garden of Hours are and who has them." It's the question Helena's email opened with, and it sums up the entire project.

Three decisions to justify. FROM copies, because I want one row per copy, whether it's out or not. The condition l.return_date IS NULL goes in the ON, not the WHERE: there, copies with no active loan would disappear and the LEFT JOIN would be a JOIN in disguise (03-03). And status still says available on all three, and that's right: it's the physical condition of the volume; the fact that it's out shows up in held_by. It's step 1's separation, turned into a column.

Helena asked for "the members who haven't been in for a long time". Before writing anything you have to define it (11-04): here, a member with status active whose last loan is older than 90 days or who has never taken one out. Closed accounts are deliberately left out.

Four members and three lessons. The LEFT JOIN is mandatory: with an ordinary JOIN, the three who have never borrowed anything —the most inactive of all— would disappear. The COALESCE in the HAVING is what lets them through, because NULL < date gives UNKNOWN and HAVING discards anything that isn't true (04-03). And COUNT(l.id), not COUNT(*): with COUNT(*) the three of them would show 1 loan instead of 0. It's the single most repeated mistake in the whole project. And one reading that isn't about SQL: the three with no loans aren't the same problem —Carla signed up in February, Irene has had her card for a year without using it, and Marta has four loans and hasn't been in for three months—; putting them in the same figure is what 11-04 called mixing two questions into one number.

The order is non-negotiable: the fifteen queries first, the indexes afterwards. An index exists to serve a specific query; if you can't name it, the index is surplus and only slows writes down (08-02). The method is mechanical:

1. List the WHERE, the JOIN and the ORDER BY of every query. That list is your candidate list, and only that (11-05).
2. Add the foreign keys you navigate: PostgreSQL indexes the PK, not the FK (08-01). It's the number one cause of sequential scans in a twelve-table schema.
3. Discard what adds nothing —a three-value column like members.type or a six-row table like subjects don't get indexed: the planner will ignore them, and rightly so— and measure with EXPLAIN (08-05), not with intuition.

Without an index on loans(member_id) and with volume, the plan is a Seq Scan that reads the whole table; with it, an Index Scan that goes straight there. The important thing isn't that it improves, it's that you check it and write it down (PR-06). And with the 36 rows of the test set you'll see Seq Scan everywhere, because reading 36 rows is cheaper than opening an index — which is a lesson in itself: to talk about performance you need volume, and that's what step 3's generate_series is for. The reference indexes, with their plans, are in 12-04.

The rule that avoids disaster: encapsulate what repeats and what is delicate; don't encapsulate for the fun of it. Three pieces are enough and a fourth would already be suspicious. The code is in 12-04; what follows is the criterion, which is what you have to be able to decide.

Two details of the view matter more than they look: its column is called estimated_fine, not fine, because it doesn't exist yet (BR-10) —calling it fine would be the first stone of a report that adds up money nobody owes—; and it uses CURRENT_DATE, so it changes by itself every night, which is exactly what was wanted and the reason for not storing "overdue" in a column.

And it stops here. The temptation to add a trigger for the maximum simultaneous loans, another to validate renewals and another to recompute suspension is enormous, and it's a mistake: triggers are invisible logic —whoever reads the INSERT doesn't see what happens— and debugging them is awkward. 10-05's criterion: a trigger for what must hold no matter what and can't be declared; a procedure for the business operation with several steps; the application for everything else.

The three roles from SR-01 to SR-03, following 11-03's pattern: privileges to the group, never to the user.

Two falses, which is what was asked for. That two-line check is the proof of SR-01 and SR-04, and it goes in the report.

In sessions of about two hours. Overrunning badly in session 3 is usually a sign that the model has a problem, not that you're slow:

• Creating the tables in whatever order occurs to you. Referential integrity imposes the order: parents before children when creating, the other way round when dropping (05-01). If your script only works the first time, it isn't idempotent and DL-02 isn't met.
• Putting the LEFT JOIN condition in the WHERE. The most expensive and most silent mistake in the project: it turns the LEFT JOIN into a JOIN, the zeros disappear and the result still looks reasonable. A condition on the right-hand table ⇒ it goes in the ON.
• COUNT(*) after a LEFT JOIN. It counts the phantom row and members with no loans come out with 1. Use COUNT(right_hand_column).
• Adding the value on_loan to copies.status, or generating random data without looking at the constraints. The first duplicates information that will end up contradicting loans; the second (returns before the loan, two active loans of the same copy, fines with no delay) aborts the script halfway and leaves the database half loaded.
• Tip: once the schema is created, try to violate it, and write the DDL with the -- IR-nn next to each constraint. An INSERT that should fail and does fail is worth more than three paragraphs of report, and going through the fourteen requirements with the rubric will take you a minute instead of half an hour.
• Tip: keep a 99-checks.sql with the counts, the eight gaps, the INSERTs that must fail and the two privilege queries. It's your test suite: running it after each change will tell you instantly whether you've broken something.
• Tip: write the report as you decide. Every hard decision, its paragraph, at the time. Reconstructing it on the last night is impossible and it shows.

Implement BR-04 in full: a loan can't be renewed if the maximum has been reached, if the loan is overdue or if the work has waiting reservations. (1) Which of the three conditions can be declared with a CHECK and which can't, and why? (2) Write an UPDATE that renews loan 32 only if all three allow it. (3) What does it return with the project's data?

Loan 29 (Lena Fuentes, copy ALV-0003) is 36 days late and is returned today, 2026-06-30. (1) List all the rows that change, in which tables and with what values. (2) Why do the three operations have to go in the same transaction, and what would happen if the third failed after the first two? (3) What effect does the return have on the partial unique index?

A colleague proposes adding a num_copies INTEGER column to works "so we don't have to count every time". (1) Give three arguments against. (2) Give a scenario where it would be defensible. (3) If it had to be done, how would you maintain it and what would it cost you?

Solution 1 — (1) None of them, and for different reasons. The maximum number of renewals depends on the member's type, which is in another table, and a CHECK can't query other tables (05-01): the only declarable thing is the absolute range BETWEEN 0 AND 2. Whether the loan is overdue depends on CURRENT_DATE, and a CHECK with a non-deterministic function is discouraged —a row that's valid today would stop being valid tomorrow and a backup restore would fail for no reason—. Reservations are in another table. All three are operational logic. (2) Everything in the WHERE, which is where it's checked without reading first:

(3) It returns UPDATE 0. Loan 32 is Sofía Terán's with copy ALV-0001, of The Garden of Hours — the work that has three members in the queue. The third condition blocks it, and rightly so: renewing it would leave Nuno Barros waiting another month for a book whose three copies are all out. And UPDATE 0 isn't an error: you have to check for it in the application and turn it into a message.

Solution 2 — (1) Three rows in three tables change:

(2) Because the three are a single business operation: a returned copy that generates its fine and activates its reservation. If the third failed without a transaction, you'd be left with a closed loan with its correct fine and a queue nobody has been notified in: Nuno would still be waiting for a book that's already on the desk, and with no trace of the failure. It's ACID in its simplest form (09-01, 09-02): all three, or none. In PL/pgSQL the procedure block is already an implicit transaction, so an exception in step 3 undoes the first two. (3) By ceasing to be active, loan 29 drops out of the partial unique index —its row no longer satisfies WHERE return_date IS NULL— and copy ALV-0003 can be lent again. The constraint releases itself: that's the elegance of the partial index compared with an active column somebody would have to remember to change.

Solution 3 — (1) First, it's pure redundancy: the data is already in copies and a COUNT(*) gets it in microseconds with the copies(work_id) index. Second, it has to be maintained on every copy added, withdrawn or moved; the day somebody inserts from a script, the figure is wrong forever and nobody notices, because a plausible number doesn't jump out at you. Third, it doesn't answer the real question: nobody asks how many copies there are, they ask how many are available right now, which depends on loans and changes by the minute.

(2) It would be defensible with millions of works, on a catalogue screen that shows the count on every search result and is read thousands of times a second, if the aggregate had been measured and were the bottleneck. In other words: when there's an EXPLAIN that justifies it, not before (08-04). And even then, the first option would be a materialized view (10-01) refreshed every night, which isolates the redundancy in an object marked as derived instead of hiding it in a column that looks like a fact.

(3) With an AFTER INSERT OR UPDATE OR DELETE trigger on copies that adds or subtracts. The cost: every write to copies also writes to works, which creates contention on the work's row —two simultaneous additions of copies of the same title get serialised (09-05)— and adds invisible logic; plus a recompute-from-scratch query, to fix it when (not if) it drifts out of sync. All that, to save a COUNT.

You now know how to build it:

• The model: twelve tables and sixteen relationships, with one N:M with a composite primary key and one self-referencing relationship. And the five hard decisions argued: copies is a table because the object has its own branch, status and history; due_date is stored because it's a historical fact and it also moves with renewals, just like unit_price; fines are a table because they have a life cycle; the queue is computed from reservation_date instead of storing a position; and the loan's state is derived while the copy's is stored, because one is a function of the clock and the other a physical fact.
• The DDL, with named constraints annotated with their IR-nn, and the partial unique index WHERE return_date IS NULL: declarative, tiny and useful as an index too, with its limit acknowledged —it doesn't detect historical overlaps—. The data: by hand for whatever appears in the queries, generate_series for volume (with every row returned, or IR-03 aborts the load) and the edge cases without which a badly written query passes for a good one, with the counts and the eight gaps verified before the first query is written.
• The method: start with the FROM by deciding what one row of the result is, check the row count after every JOIN, choose LEFT JOIN by asking about the zeros and validate the total by two routes. The two worked examples teach the two traps: the LEFT JOIN condition goes in the ON, and COUNT(*) counts the phantom row.
• The indexes after the queries, not the other way round; three encapsulations and not one more —the overdue view with its honest estimated_fine, the atomic return procedure and the only trigger that's needed—; and three roles of least privilege verified with has_table_privilege.

You've built it; now it's time to compare it, which is where you really learn. In the next lesson, Annotated Project Solutions, you'll find the solution set: the schema decisions justified one by one with the alternatives that would also be correct —including the EXCLUDE constraint and the debate between derived and stored state—; the 15 solved queries with their result, their key decision and the typical mistake for each one; the reference indexes with the plan that takes advantage of them; the code for the view, the procedure and the trigger; and the catalogue of what goes wrong most often in this project in particular.