Three whole lessons arguing that every fact must be stored exactly once, and now a lesson explaining when it pays to break that rule. It may look like a contradiction, and it is not: it is the difference between knowing a rule and knowing how to use it.

At the end of the previous lesson we ran into the same situation four times. duration_min in events, computed from start_time and end_time. occupied_seats in registrations, computed from companions. A fine's amount, which cannot be recomputed if the by-law changes. The hourly_rate of a room booking, which must stay frozen at the value it had on the day of the invoice. All four are redundancies. All four violate the letter of third normal form. And all four are correct.

Denormalizing means introducing redundancy into a schema on purpose, in order to get something specific that the normalized schema did not give you. The three words that matter are "on purpose", "specific" and "did not give". Without all three it is not denormalization: it is not having done the work.

This lesson closes the module with the opposite decision, taken with the same rigor as the previous ones. What exactly you gain and what you pay. When it is justified and when it is not. The techniques one by one, with SQL over BiblioRed. How you keep consistent what you have duplicated deliberately. And a decision guide with the questions you have to answer before touching anything —and the signs that it is time to undo it.

1. What denormalizing is and the golden rule
2. The first thing to try: indexes, not denormalization
3. The balance: what you gain and what you pay
4. When it is justified: the three cases
5. Technique 1: the redundant or computed column
6. Technique 2: generated columns versus hand-maintained columns
7. Technique 3: duplicating an attribute to avoid a JOIN
8. Technique 4: pre-aggregated summary tables
9. Technique 5: materialized views
10. Technique 6: flat history tables
11. Technique 7: the star schema of data warehouses
12. How you keep denormalized data consistent
13. The bridge to NoSQL: document modeling is denormalization raised to a method
14. Decision guide: the questions beforehand and the signs to undo it

Definition. Denormalizing means deliberately modifying a normalized schema to introduce redundancy —duplicated or derived data— with the aim of improving read performance or preserving a historical value, accepting in exchange the cost of keeping that redundancy consistent.

Note three things in that definition.

You start from a normalized schema. You cannot denormalize what was never normalized. A schema that was born redundant because nobody analyzed the dependencies is not denormalized: it is badly designed. The difference is not semantic: in a denormalized schema you know exactly which data is duplicated, why, and who is responsible for maintaining it; in a badly designed schema, you do not.

The aim is specific. "Just in case" and "to make it faster" are not aims. "The monthly management report takes forty seconds and it has to take less than two" is one. "The fine's amount must not change if the by-law goes up" is another.

A cost is accepted, and it has to be named. Every denormalization has a bill, and somebody pays it: whoever writes, whoever maintains the trigger, or the user who one day sees two different figures for the same thing.

Normalize first. Then denormalize on purpose, measuring. Never the other way round.

It is the rule that governs the whole lesson and it is worth breaking down:

"Normalize first" means that the starting point is always the schema in 3NF or BCNF. It is the one that expresses the domain correctly, the one that admits no contradictions, and the one any professional will understand. It is also, and this is forgotten, the only reference against which you can measure whether the denormalization achieved anything.

"On purpose" means documented. Every redundant column must have written next to it: what it duplicates, why, who maintains it and what happens if it goes out of sync. Without that, in two years' time somebody will see it, think it is a mistake, and "fix" it or —worse— let it rot.

"Measuring" means with numbers before and after. If you cannot say "this query used to take 4.2 seconds and now takes 80 milliseconds", you do not know whether the denormalization helped. And very often it does not: the problem was somewhere else.

"Never the other way round" is the important part. Starting with a redundant schema "because it will be faster that way" and normalizing it when it causes trouble is the wrong order, because premature denormalization makes decisions about a usage pattern you do not know yet, and undoing it afterwards is infinitely more expensive than doing it now: there is data, there is code and there are reports depending on it.

Before we go on, a warning that prevents most of the unnecessary denormalization you see in production.

When a query is slow, denormalization is not the first thing to try. It is the last.

The correct order of interventions, from least invasive to most, is this:

The first four do not touch the data, they do not introduce the possibility of the database contradicting itself, and they can be undone in a minute. The fifth is permanent in practice.

And experience is emphatic: the vast majority of slow queries in a normalized schema are fixed with an index. A five-table JOIN with the foreign keys indexed over a few hundred thousand rows is a millisecond operation in PostgreSQL. If it takes seconds, there is almost always a missing index, the query is asking for columns it does not need, or the planner is working with stale statistics.

Indexes, the execution plan, EXPLAIN ANALYZE and how to read it, and query optimization in general are the content of lesson 06-03. It is literally the lesson after this module, and the order is not accidental: first you learn to make the normalized schema fast, and only then do you consider changing it.

Operational rule: do not denormalize any query without first running its EXPLAIN ANALYZE and checking that there is no index that fixes it. If you cannot read an execution plan yet, you are not in a position to decide on a denormalization.

Every denormalization is a trade. These are the two pans of the scale, and it is worth having them written down so you can compare in each specific case.

The note on contention deserves a pause, because it is the cost that is least anticipated. If you add total_loans to the members table and update it on every loan, each front-desk operation has to lock the member's row. With one member borrowing one item at a time, nothing happens. But if tomorrow you add total_loans to branches, every operation at the North branch competes for the same row, and at peak time the front desk serializes. Locking mechanisms and isolation levels are lesson 06-02; for now just take away that a global counter is a denormalization with a concurrency cost that can be far worse than the JOIN it was avoiding.

Of all the reasons people give for denormalizing, only three survive scrutiny.

Denormalization trades write cost for read speed. It only pays off if you read far more than you write.

In BiblioRed, a material's public page —title, author, availability per branch— is consulted from the web catalog around 40,000 times a day. The data it shows changes, at most, when a new copy comes in: two or three times a week. The ratio is tens of thousands of reads per write, and there a well-maintained copy pays for itself.

At the other extreme, the loans table is written to constantly during front-desk hours and is read mostly by member. Denormalizing it to speed up a report that runs once a month is a bad deal.

The numeric criterion: if the read/write ratio does not reach 10:1, it almost never pays off. Above 100:1, it starts to be interesting. And that ratio has to be measured, not estimated.

This is the case where denormalization wins hands down and no other technique comes close.

Management wants a dashboard with loans by branch and month for the last eight years. On the normalized schema, that is a GROUP BY over 84,000 rows of loans with three JOINs. Today it takes a few seconds. When BiblioRed has been running twenty years and has millions of loans, it will take minutes, and the dashboard will be opened once every morning... by each of the fifteen managers.

The key point is that the data for closed months never changes. Recomputing the total for March 2019 on every query is throwing work away. Precomputing it once and storing it is the obvious decision. It is technique 4 in section 8.

And this is the most important of the three cases, because it is not a question of performance but of correctness. Here denormalization is not a compromise: it is the only correct answer.

Consider BiblioRed's fine 900: issued on 12 March 2026 to member 14 for a 35-day late return, amount €3.50, rate in force €0.10/day. In April, the city council raises the rate to €0.15/day.

How much does Marta Alsina owe for that fine? €3.50. She was notified in writing, it is on the receipt, and it cannot change. If fines did not store the amount and computed it with a JOIN to the rates table, in April that fine would retroactively become €5.25. That is not a design problem: it is a billing error.

The same reasoning applies to:

• The member's name at the moment of payment. If a receipt says "Received from Marta Alsina" and she changes her surname, the receipt already issued does not change. The name on the receipt is a historical value, not a live reference to members.
• A copy's purchase price. What was paid is what was paid.
• An order's shipping address, in e-commerce. The order was shipped there, even if the customer has since moved.

This is exactly the pattern we called frozen historical duplication in lesson 03-03 when talking about document modeling, and classified as "category B": a duplicated field that is correct by semantics, not a copy that has to be propagated. The discipline it demands is different from that of a live copy: it is never updated, and precisely for that reason it carries no risk of inconsistency.

The test for telling them apart: ask yourself "if the original value changes tomorrow, must this one change too?". If the answer is no, it is not a performance denormalization: it is a different piece of data that happened to coincide with the original at the moment of creation, and storing it is the right thing to do.

The most common one and the easiest to get wrong. It consists of storing in one table a value that can be obtained by counting or summing rows from another.

The case: the member's page in the front-desk application shows how many loans they have in total and how many are open. Normalized:

With an index on loans(member_id) this is instantaneous for one member. There is nothing to denormalize here, and it is important to say so: the temptation to add the counter shows up before you have checked whether it is needed.

Where the problem does appear is in the listing of the 12,000 members of the North branch with their number of loans, which the application pages 50 at a time. There the aggregate is computed over the whole table on every page.

The denormalization:

The query becomes a plain SELECT over members, with no JOIN and no aggregation.

What has to be documented and not forgotten:

That last row is the most important one and the most often forgotten: every denormalized column needs a documented procedure for recomputing it from scratch. It is the safety net, and one day it will be used.

The audit query:

If that query returns rows, the maintenance mechanism has a hole. Scheduling it as a nightly check takes five minutes and saves months of bewilderment.

Not every derived column carries the same risk. There is an enormous difference between the ones the DBMS maintains and the ones your code maintains, and choosing well is the most profitable decision in this lesson.

We already know them from module 4 and we examined them in 05-03:

The word ALWAYS is the guarantee: PostgreSQL recomputes the value on every INSERT and every UPDATE, and rejects any attempt to write it by hand:

It is a denormalization with no risk of inconsistency. It violates 3NF in the letter, and not in the spirit, because the danger 3NF prevents has been eliminated by another mechanism.

Its limitation is important: a generated column can only depend on columns of its own row and use deterministic functions. It cannot count rows in another table, or query branches, or use now(). That is why total_loans in members cannot be a generated column: it depends on another table.

When a generated column will not do, maintenance becomes somebody's responsibility, and that is where the problems in section 12 start.

And the detail we already flagged in 05-03 and that is worth nailing down: a historical amount cannot be a generated column, even though it looks like one. amount = rate × days is a formula, yes, but if rate changes, a generated column would recompute the amount of the old fines. It has to be a normal column, computed once when the fine is issued and never again. The difference between duration_min —derived from start_time and end_time, which belong to the row itself and do not change— and an amount derived from a mutable external value is exactly this.

The simplest technique: copy a column from table A into table B so that you do not have to join them.

The case: the list of active loans at the front desk shows the material's title. Normalized it takes three JOINs, because books is a view over materials + materials_book from the hierarchy in 04-03:

The denormalization:

The query comes down to a SELECT over loans with a single JOIN to copies for the branch filter.

And now the honest part: in this particular case it is almost certainly not worth it. With indexes on the foreign keys, three JOINs over a few tens of thousands of rows are milliseconds. A live copy has been introduced —if somebody corrects a badly cataloged title, it has to be propagated to every loan— in exchange for a benefit that will probably not be noticed. It is the perfect example of a denormalization that looks reasonable and is not.

When would it be worth it? When the duplicated attribute satisfies at least one of these two conditions:

• It is immutable. An edition's ISBN never changes. Copying it is free: there is nothing to propagate. It is "category A" from 03-03.
• It must be frozen. The material's title at the moment of the loan, for a receipt or a history. That is category B: copied once and never touched.

If the attribute is live —it can change and the copy must follow it— duplication demands propagation, and then you have to ask whether the JOIN you avoid is worth the mechanism you add. Most of the time, it is not.

This is where denormalization stops being a debatable compromise and becomes the obvious solution.

The case: management's dashboard with loans by branch and month since 2018.

The load, which is a single aggregate query:

The dashboard goes from aggregating 84,000 rows (and growing) to reading a table of some 400 records —8 years × 12 months × 4 branches—. The performance difference is not 20 %: it is three orders of magnitude, and it cannot be achieved any other way.

Three design details that make this technique work well:

The closed column. It tells finished months —which will never change— apart from the current month, which will. Closed ones are computed once and forgotten; only the current month needs refreshing. It is what makes the maintenance cost almost zero.

The updated_at column. Anyone looking at the table knows how old the data is. Without it, nobody can judge whether a figure is reliable.

The ON CONFLICT ... DO UPDATE. It lets you run the load as many times as necessary without duplicating anything. A process that can be repeated with no side effects is infinitely easier to operate than one that has to be run exactly once.

And the rule that is not negotiable: the summary table is derived, never the source of truth. If loans_monthly_summary and loans disagree, loans is right and the summary is regenerated. The day somebody starts correcting figures directly in the summary, the denormalization has become a second data system inconsistent with the first.

A materialized view is a summary table the DBMS manages for you: it is defined with a query, PostgreSQL stores the result on disk, and it is refreshed when you ask it to.

It is queried like any other table:

This is the critical point, and the difference between the two forms is large:

For BiblioRed's web catalog, every 10 minutes is more than enough: the site saying "2 available" when there has been 1 for the last three minutes is acceptable, because the member is going to check at the front desk anyway.

Practical rule: always start with a materialized view. If the complete refresh takes too long —and with years of history it eventually will— migrate to a summary table with an incremental refresh. It is the order that minimizes the work.

(Note: SQLite has no materialized views. The equivalent is a normal table populated by the application. It is one of the differences to keep in mind when choosing between PostgreSQL and SQLite, as we saw in 01-02.)

A variant of the summary table that does not aggregate but flattens: it stores one row per fact, but with all the columns you need already resolved, with no JOINs.

Each row carries twelve columns that in the normalized schema would require five JOINs. Any report —loans by author and year, by material type and branch, by member age band— is resolved with a GROUP BY over a single table.

The important part of this technique is the phrase "at the moment of the loan". The values are copied when the loan is closed and are never updated again. If Marta Alsina transfers to the South branch in 2027, the loans she made in 2026 still say "North", which is the historical truth. A report on the North branch's activity in 2026 must count them.

Here is the key difference from the previous techniques: this is not a copy that has to be kept in sync. It is a record of what was true then. There is no risk of inconsistency because there is nothing to propagate, and that is why this is one of the safest denormalizations there is.

All the previous techniques are one-off denormalizations on a normalized schema. The star schema is something else: it is a complete data model designed from the start to denormalize, systematically and as a matter of policy.

Here we pick up the OLTP versus OLAP distinction from lesson 01-02:

The star schema organizes the data into two kinds of table:

• Fact table: one row per measurable event, with the numeric metrics that are going to be aggregated and foreign keys to the dimensions. It is enormous —millions or billions of rows— and very narrow.
• Dimension tables: the context you want to filter and group by. They are small, wide and deliberately denormalized: a dimension is not decomposed even if it has transitive dependencies.

It is called a "star" because the diagram has the fact table in the center and the dimensions around it:

And analytical queries become trivial to write and extremely fast to run:

A single level of JOIN, with no chains. On the normalized schema, getting from a loan to the author's nationality requires traversing loans → copies → materials → authors.

Four reasons, and all four are solid:

1. There are no concurrent writes. The warehouse is loaded in batches from the operational system. The main cost of denormalization —keeping consistency in the face of writes— simply does not exist.
2. The source of truth is somewhere else. If the warehouse gets corrupted, it is reloaded from the OLTP. The redundancy cannot cause an unrecoverable loss.
3. The data is historical and immutable. A 2019 loan does not change. And when the context changes —a material is recataloged— the right thing to do is keep the old value for the old facts, which is exactly what denormalization gives you.
4. The query pattern is known and stable. You know in advance what you are going to group by, and the model is designed for that.

In short: in OLAP all the conditions that justify denormalizing hold at once, and none of those that argue against it. That is why there it is the rule and not the exception.

(There is a variant called snowflake that does normalize the dimensions —pulling author out of dim_material into its own table, for example—. It saves space and complicates the queries. The majority view in the industry is star unless the dimensions are gigantic. Data warehouse design is a discipline of its own; here we only care about recognizing it as systematic denormalization and understanding why it is justified.)

Every denormalization that is not frozen history creates an obligation: keeping the copy in sync with the source. There are three ways of meeting it and you have to choose consciously.

The code that writes the loan also updates the counter:

In favor: the logic is where the team can see it, it is easy to debug and to test.

Against, and this is a serious problem: it only takes one write path forgetting for the copy to start diverging. And there are more write paths than it seems: the web application, the front-desk application, the nightly import process, the correction script somebody ran by hand one Tuesday afternoon. Every one of them has to remember.

A trigger is a function the DBMS runs automatically when an event happens on a table. The decisive advantage: it does not matter who writes or from where.

A line-by-line reading of the code, because this is the first time a trigger appears in the course:

• RETURNS TRIGGER marks the function as suitable for being invoked by a trigger.
• TG_OP is a special variable holding the operation: 'INSERT', 'UPDATE' or 'DELETE'.
• NEW is the new row (it exists in INSERT and UPDATE); OLD is the previous one (it exists in UPDATE and DELETE).
• AFTER ... FOR EACH ROW means it runs once per affected row, after the change is applied. If an UPDATE touches 500 rows, the trigger runs 500 times.
• Everything happens inside the same transaction as the original operation: if the counter UPDATE fails, the loan INSERT is rolled back too. That atomicity is precisely what makes this option reliable, and it is the material of lesson 06-01.

In favor: impossible to bypass. Consistency guaranteed by the DBMS.

Against: it is business logic hidden in the database, invisible to whoever reads the application code; it complicates debugging; and it costs on every write. A bulk UPDATE of 100,000 rows runs the trigger 100,000 times.

A scheduled job recomputes the copy periodically:

In favor: zero cost on writes, simple code, and —this is valuable— it corrects any divergence by itself, wherever it came from.

Against: the data is stale between runs. You have to decide whether that is acceptable, and say so in the interface ("data as of yesterday").

The combination that works best in practice: pick one of the three for maintenance and always add the batch one as an audit. Even if you use a trigger, schedule the nightly check query. If it ever returns rows, you will know there is a hole before a user discovers it.

If while reading section 7 you thought "this looks a lot like embedding documents", you have seen exactly what there is to see.

The document modeling of lesson 03-03 —embedding instead of referencing, duplicating fields on purpose, designing the aggregate around the query that will read it— is denormalization. It is not a similar technique: it is the same decision, taken for the same reasons, with the same consequences.

Compare it point by point:

The difference is not in the technique but in the default starting point. In a relational system, the norm is to normalize and to denormalize in specific, justified cases. In a document system, the norm is to aggregate —denormalize— and to normalize (reference) in specific, justified cases. The axis is the same; what changes is where the neutral point sits.

And the discipline it demands is identical. In 03-03 we established that every duplicated field must be classified —immutable, frozen history, or live with propagation— and that if it is not written down, in six months nobody will know whether it has to be updated. It is exactly the documentation that section 5 of this lesson demands for every redundant column. The rule is the same in both worlds: duplicate what you display, never what you use to decide; and always write down which is the source of truth.

This also explains something that in module 3 could sound contradictory. When we said that MongoDB "does not need JOINs" we were not saying that the problem the JOIN solves had disappeared: we were saying that it is paid up front, on the write, in the form of maintained duplication. It is the same trade as in section 3 of this lesson, with the same pans on the scale.

Answer them in writing. If any of them has no answer, do not denormalize yet.

1. Have I measured the problem? How long does the query take now, with production data and real volume? If you do not have the number, you do not have a problem: you have a suspicion.

2. Have I tried an index? EXPLAIN ANALYZE of the query, a review of the existing indexes, ANALYZE on the tables. This is lesson 06-03 and it is mandatory before going any further.

3. What is the specific aim? "This report must open in under two seconds" is an aim. "Go faster" is not, because you cannot tell whether it has been achieved.

4. What is the read/write ratio? Measured, not estimated. Below 10:1, denormalization rarely pays off.

5. Is the copy immutable, frozen history or live? It is the question that saves the most. The first two are almost free. Only the third demands a propagation mechanism, and only then do you have to answer the next two.

6. Who maintains the copy and what happens if it fails? Application, trigger or batch (section 12). And the failure scenario: a wrong number on a screen, or a wrong amount on an invoice? The severity decides the mechanism.

7. How is divergence detected and repaired? The audit query and the recomputation procedure, written down and scheduled. If you do not have them, the denormalization is not finished.

A denormalization is not forever. These six signs indicate that it needs reviewing, and probably reverting:

1. The audit queries return rows regularly. The maintenance mechanism has a hole that has not been closed. Every divergence detected is a value somebody saw wrong before the audit caught it.

2. Nobody remembers why that column is there. If the documentation does not exist or nobody can find it, the denormalization is no longer deliberate: it is debt.

3. The copy has become the source of truth. The symptom is somebody correcting a value in the copy instead of in the original. From then on there are two data systems contradicting each other and no way of deciding which one rules.

4. Writes have become the bottleneck. The system optimized reads and now the front desk waits. Measure again: the balance may have shifted.

5. The original reason has gone away. Nobody uses the report that justified the summary table any more. The new version of PostgreSQL runs that JOIN a hundred times faster. An index was added that solves the case. Review your denormalizations at least once a year: some of them expire.

6. The maintenance logic has become more complex than the JOIN it was avoiding. If the trigger has forty lines and three special cases in order to save a two-table JOIN, the trade no longer makes sense.

With the same discipline it was done with, and in the reverse order to 05-03: the reads are switched first so that they use the normalized schema, you check that they give the same results, the maintenance mechanism is removed, and only at the end is the column or the table dropped. Saving a copy beforehand, always.

Denormalizing without having measured. It is mistake number one and the cause of most of the unnecessary redundancy out there in production. "This is going to be slow when it grows" is a prediction, not a measurement, and predictions about performance fail constantly: the bottleneck is almost never where you expected.

Denormalizing before trying an index. That is the whole of section 2. A CREATE INDEX is reversible, free in terms of risk, and instantaneous; a denormalization is permanent in practice. Start with 06-03.

Starting out denormalized "just in case". It breaks the golden rule. You do not yet know which queries will dominate, or at what volume, or with what write pattern. And undoing it afterwards is far more expensive than doing it now.

Not documenting the source of truth. Every duplicated value has to have written down which of the two copies rules. Without that, the day they disagree —and they will— nobody will know which to correct, and somebody will pick wrong.

Treating the copy as the source of truth. The symptom is a direct UPDATE on the summary table to "make a figure add up". That UPDATE fixes nothing: it creates a permanent divergence that the next refresh will wipe out, or worse, will not.

Putting a trigger that refreshes an entire materialized view on every write. It completely cancels out the benefit and turns every INSERT into a global recomputation. Materialized views are refreshed on a schedule.

Forgetting the recomputation procedure. Every denormalization needs a documented UPDATE/INSERT that rebuilds it from scratch. One day it will have to be run, in a hurry, and that will not be the moment to write it.

Confusing "frozen value" with "denormalized value". They are not the same and confusing them leads to two opposite mistakes: propagating a value that should have stayed still (and falsifying a history), or failing to propagate a live copy (and showing stale data). The question that separates them is in section 4: if the original changes tomorrow, must this one change too?

Never reviewing. Denormalizations expire. An annual review of all the ones in the schema, with the measurements repeated, usually finds at least one that is no longer needed.

For each of these four BiblioRed cases, decide whether you would denormalize or not. Justify it with the questions from section 14 and, if you do denormalize, say which technique you would use and which maintenance mechanism.

a) An event's detail page shows the room's name and its capacity. It is consulted around 300 times a day. The normalized query takes 4 milliseconds.

b) The annual management report crosses the 84,000 loans with members, materials, authors and branches, grouping by author and year. It takes 38 seconds and 15 people open it every morning throughout January.

c) The receipt printed when a fine is paid shows the member's name, the amount and the reason.

d) The web catalog shows, for each material, how many copies are available at each branch. It is consulted 40,000 times a day and the data changes with every loan and every return.

Six months ago BiblioRed added the column members.total_loans, maintained by the front-desk application (option A in section 12). Today, the manager of the North branch says that a member's page shows 12 loans and their history only has 9.

You are asked to:

• a) Write the audit query that finds all the members whose counter does not add up, showing the difference.
• b) Write the UPDATE that repairs the counter for all of them.
• c) Propose three plausible causes of the divergence, bearing in mind that maintenance is in the application.
• d) Propose the change of mechanism that would stop it happening again, and say what you gain and what you pay.

BiblioRed's management asks for an events activity dashboard with these figures, by branch and month: number of events held, total number of confirmed registrations, offered seats, average occupancy as a percentage, and average rating from the event reports.

You are asked to:

• a) Write the CREATE TABLE for the summary table, with its primary key and the control columns recommended in section 8.
• b) Write the INSERT ... SELECT that loads it from events, rooms, registrations and event_reports, covering closed months only.
• c) Decide the refresh mechanism and justify it.
• d) Write the audit query that checks that one row of the summary matches the operational data.

Solution 1

a) Do not denormalize. Question 1 settles it: 4 milliseconds is not a problem. With 300 queries a day, the total CPU time devoted to that JOIN is a little over a second a day. Introducing a copy of room_name and room_capacity into events would in addition duplicate a live value —the capacity changes if the room is refurbished, and in fact that is the update anomaly example we used in 04-01—, so it would demand a complete propagation mechanism. High cost, zero benefit.

b) Do denormalize: a summary table or a materialized view. It is case 2 of section 4 in its purest form. 38 seconds × 15 people = almost ten minutes of accumulated waiting a day, over data that does not change: the loans of closed years are immutable. And question 2 is not going to save it: no index significantly speeds up a GROUP BY that walks all 84,000 rows anyway.

The right technique is a summary table with author-year granularity and a closed column, refreshed once a month by a batch process. Past years are computed once in their lifetime. The maintenance cost tends to zero and the query goes from 38 seconds to a few milliseconds.

c) Yes, but this is not an optional denormalization: it is correctness. It is case 3 of section 4. The receipt issued on 12 March says what it says and cannot change: not if the member changes their name, not if the by-law raises the rate, not if the fine is reclassified. All three values must be copied into the payments table (or into a receipts table) at the moment the receipt is issued, and never touched again.

No maintenance mechanism is needed —precisely because nothing is propagated— and there is no risk of inconsistency. It is the safest denormalization there is and the only one of the four where it would be a mistake not to do it.

d) Do denormalize: a materialized view. It is case 1 of section 4. The read/write ratio is overwhelming: 40,000 queries a day against a few hundred loans and returns. And the normalized query is a grouped COUNT over copies for each material, which on the catalog page runs many times.

The technique is the mv_material_availability from section 9, with a scheduled refresh every 5 or 10 minutes. The key is accepting the lag: the web catalog saying "2 available" when there is 1 left is tolerable, because the member will check the real availability when they ask for it. What would not be tolerable is using that view to decide whether to grant a loan: for that you have to query copies, which is the source of truth. It is the exact application of the rule from 03-03: duplicate what you display, never what you use to decide.

Solution 2

a) Audit query:

The LEFT JOIN is indispensable: with an inner JOIN, members with no loans at all would disappear from the result, and they are precisely the ones who can have a wrongly positive counter.

b) Repair:

The COALESCE covers members with no loans, whose COUNT over the LEFT JOIN gives 0 but whose subset might not show up. And running the check inside the transaction, before the COMMIT, is the correct practice: if the number is not zero, you ROLLBACK.

c) Three plausible causes, all of them characteristic of maintenance in the application:

1. A write path that does not update the counter. The nightly process importing loans from the neighboring library, or the correction script somebody ran by hand, inserted into loans without touching members. It is the most frequent cause.
2. A DELETE that was not accounted for. When Iván Pereda asked for his history to be deleted, the loans rows were deleted but the code only decremented the counter in the return case, not in the deletion case.
3. A partially committed transaction. If the INSERT and the UPDATE were not inside the same transaction, a failure between the two leaves the loan inserted and the counter not incremented. It is a subtle bug and it produces off-by-one divergences that are very hard to trace afterwards.

d) Change of mechanism: move to a trigger (option B), and add the batch audit.

The trigger from section 12 runs wherever the write comes from: the web application, the front desk, the import process or somebody's psql on a Tuesday afternoon. It eliminates causes 1 and 2 at the root. And since it runs inside the same transaction as the original operation, it eliminates 3 as well.

What you gain: consistency guaranteed by the DBMS, not by the discipline of every team that writes.

What you pay: an added cost on every write to loans —noticeable if a bulk UPDATE is ever run—; business logic that lives in the database and cannot be seen by reading the application code; and one more plpgsql function to test and maintain.

And in any case, the query from part a) gets scheduled as a nightly check anyway. Even with a trigger: if the trigger is ever disabled for a bulk load and somebody forgets to re-enable it, the audit will catch it that same night.

Solution 3

a) The summary table:

The closed and updated_at columns are the control ones the brief asked for. The CHECKs are not decoration: a derived table with a 340 % occupancy rate gives away a bug in the load query, and it is better for the INSERT to catch it than a director in a meeting.

b) The load:

Three decisions worth commenting on:

• The LATERAL subquery for the registrations avoids the classic mistake of multiplying rows when you join two detail tables (registrations and reports) against the same events table. Without it, an event with 20 registrations and one report would produce 20 rows and the average rating would be counted 20 times. It is exactly the "inventing rows when joining" problem from 05-03, in its aggregation form.
• e.status = 'held' excludes cancelled and scheduled events, which should not count as activity.
• occupied_seats instead of counting registrations: the generated column in registrations already includes the companions, which is what really takes up capacity.

c) Refresh mechanism: a batch process, monthly, on the 1st of each month.

The justification lies in the nature of the data. Closed months never change: an event held in March with its registrations and its report is a done deal. Refreshing more often would be pointless work. And since the dashboard is a management tool looked at monthly, data "as of the end of last month" is exactly what is needed.

The ON CONFLICT ... DO UPDATE additionally lets you re-run the load safely if an event report is filled in late.

A trigger here would be a serious mistake: recomputing monthly aggregates on every registration is a permanent cost for a benefit consumed once a month.

d) Audit query for a particular row (March 2026, North branch):

If matches is f, the summary table has drifted and the load for that month has to be re-run. Scheduling this check for the previous month, run weekly, is enough: the data for closed months should not move, and if it does then somebody is correcting historical data, which is something worth knowing about.

This module started with a promise from module 4: to put the BiblioRed schema through a formal examination we had avoided until then. It is done, and it is worth looking at the whole journey.

In 05-01 we built the instruments. The three anomalies —insertion, update and deletion— stopped being a footnote and became three failures we provoked with SQL over the loans sheet. We learned to write their cause as a functional dependency X → Y, to tell full dependencies from partial and transitive ones, to derive with Armstrong's axioms, and to compute the closure X⁺ in order to find candidate keys with an algorithm instead of with intuition.

In 05-02 we went through the catalog. First normal form and the atomicity that depends on usage; second and partial dependencies; third and transitive ones; Boyce-Codd with its one-line definition and its small print about dependency preservation; fourth and independent multivalued dependencies; fifth and the honesty of saying it almost never shows up. And the criterion that governs everything: up to 3NF/BCNF always, beyond that only if the case calls for it.

In 05-03 we did the work. From a thirteen-column spreadsheet to nine tables in BCNF, step by step, with the data in front of us and the migration SQL. We learned that INSERT ... SELECT DISTINCT is the canonical way to migrate, that a duplicate key error is the new schema doing its job, that Heath's condition is what separates a correct decomposition from one that invents rows, and that in production you normalize in phases and not in one go. And we found a real defect in the module 4 schema: fines violated 3NF through loan_id → member_id, which made it possible to charge one member for another's fine.

And in this lesson we have closed the circle with the opposite decision. Denormalizing is not the opposite of normalizing: it is what you do after normalizing, on a schema that is already correct, in order to get something specific that the schema did not give you. We have seen the seven techniques —redundant column, generated column, duplicated attribute, summary table, materialized view, flat history, star schema—, the three ways of maintaining consistency with their guarantees and their costs, and the seven questions to answer in writing before touching anything. We have also seen that the strongest case for denormalizing is not performance but correctness: a fine's amount, the name on a receipt and the rate on an invoice are frozen historical values, and storing them is not a concession, it is the only correct answer. And we have recognized that the document modeling of 03-03 is this same discipline moved to the center of the method, with the same categories and the same obligations.

The golden rule sums up the whole module: normalize first, then denormalize on purpose, measuring, and never the other way round. A normalized schema that has been stepped back from at two specific, documented, measured and audited points is a good schema. A redundant schema that never went through normalization is not a denormalized schema: it is an undesigned one.

That closes module 5, Normalization. BiblioRed's schema has passed the examination, with one defect found and fixed and four denormalizations now justified and written down. We know that the structure is correct and that the data cannot contradict itself. What we still do not know is what happens when two people at the front desk record a loan of the same copy at the very same instant, or what happens if the server shuts down halfway through an operation, or how long a query really takes when the table has ten million rows, or who can read the members' phone numbers. In module 6, Transactions, Performance and Security, we stop looking at the schema and start looking at the system in operation: transactions and the ACID properties that guarantee an operation happens in full or not at all (06-01); concurrency and the isolation levels that decide what each user sees while another writes (06-02); indexes and execution plans, which are —remember— the first thing to try before denormalizing (06-03); and security, permissions and backups, which are what separates a database from an accident waiting to happen (06-04).