Say it once, or say it everywhere.

When you move house, you file a change of address with the post office once, and your mail follows you. Then, for months afterward, the dentist, the alumni magazine and the car insurer keep writing to the old house. Each of them kept its own copy of your address, and each copy had to be corrected separately. Somebody always misses one.

That small domestic annoyance contains the whole of one of the oldest debates in database design. One school says to store each fact in exactly one place and let everything that needs it look it up. The other says to copy each fact to wherever it is needed, so nobody has to look anything up at all. The first is called normalization. The second is called denormalization, a name that makes it sound like a lapse in standards rather than a design choice.

The debate is usually staged as a contest between purity and speed. I want to suggest that it is better understood as a confusion between two jobs. Recording the world and asking questions about it are different activities with different enemies. Normalization is the discipline of the recorder, whose enemy is contradiction. Denormalization is the convenience of the asker, whose enemy is delay. Once you see which job a system is doing, most of the argument dissolves, and what remains is a single rule about copies that is easy to state and surprisingly often ignored.

Two ways to keep a fact

Imagine a small shop that records its orders. Each order involves a customer, a product, a store and a quantity. Maria Lopez, who lives at 12 Elm Street in Burbank, has placed six orders this year.

A normalized design keeps Maria on one index card: her name and address, filed under a customer number, C-104. Each order slip carries only that number. To print a shipping label, the system follows the number back to the card. A denormalized design skips the lookup. It prints Maria's name and address on every order line, so a clerk reading the printout has everything in front of them.

Both designs hold exactly the same facts. The difference shows up only when a fact changes. So let us move Maria to Glendale and watch.

The case for saying it once

The relational model that Edgar Codd proposed in 1970 gave database designers a vocabulary for this problem, and the normal forms are its rules of hygiene. Stripped of notation, each rule closes a loophole through which the same fact could be recorded twice.

First normal form asks for one value per box: no cramming three phone numbers into a single field. Second normal form asks that every piece of information in a table depend on the table's whole identifying key, not just part of it. Third normal form asks that it depend on nothing besides the key. The computer scientist William Kent condensed the doctrine into a line that database students still recite, a pastiche of the courtroom oath.

Why go to the trouble? Because duplicated facts produce three kinds of absurdity, which the textbooks call anomalies. An update anomaly is the change-of-address problem: correct five copies, miss the sixth, and the database now gives two answers to one question. An insertion anomaly arises when a fact can be recorded only alongside some other fact: if store details live only on order lines, you cannot record a new store until someone buys something there. A deletion anomaly is the mirror image: cancel the last order from a store and you erase the only record that the store exists.

A normalized design makes all three impossible by construction rather than by vigilance. That distinction matters, because vigilance is the scarcest resource in any organization. A rule that depends on people remembering to do something will eventually meet a person who forgot. There are smaller dividends too. Each fact is stored once, so there is less to store. And when the business changes, the change stays local: a new attribute of customers goes on the customer card, and nothing else has to move.

The price of saying it once

Every virtue has a cost, and normalization's is paid by the reader. Think of a cookbook whose salad recipes each say "for the dressing, see page 212." The book is shorter and never contradicts itself about vinaigrette. But making dinner now involves a lot of flipping.

In a database the flipping is called a join, and a question that touches orders, customers, products and stores requires several of them. Joins cost the machine time, especially across large tables, and they cost people something worse, which is comprehension. A fully normalized schema can run to hundreds of tables, and an analyst who wants to know how toothpaste sold in the West must first learn which ten of them to stitch together. The engineers who designed it rarely see the problem, because they long ago forgot what it was like not to know.

The case for saying it everywhere

Now consider the cookbook that reprints the vinaigrette in every salad recipe. It is longer, and it is a joy to cook from. Denormalization makes the same trade. By folding customers, products and stores into the order rows, it lets a question be answered by reading one table from top to bottom. Queries are simpler to write and faster to run. Analysts see everything relevant in one place, and reporting tools, which mostly read rather than write, can scan wide tables at great speed.

This is why the reporting side of most organizations is denormalized on purpose. The star schema, with measurements in the middle and descriptive context around them, is a disciplined form of denormalization. It repeats descriptive details deliberately so that people can ask questions without a map of the plumbing.

The price of saying it everywhere

The cost is the one we started with. Every copy of a fact is a promise to keep that copy correct, and promises multiply. An old saying holds that a person with one watch knows the time, while a person with two is never quite sure. A table with six copies of Maria's address is a person wearing six watches.

Redundant copies also take up more space, though with storage as cheap as it is today, that is the least of the problems. The serious costs are the anomalies, which return in full force, and rigidity: when a fact has been spread across many tables, changing how it is defined means hunting down every place it went.

Recording versus asking

So which design is better? The question has the same structure as asking whether a filing cabinet is better than a briefing book. It depends on whether you are filing or briefing.

Systems that record the world, taking orders, booking appointments and posting payments, write constantly and must never contradict themselves. They are normalized, and they should be. Systems that answer questions, such as dashboards, reports and analytics, read far more than they write, and their users want answers fast and plain. They are denormalized, and they should be.

The best modern architectures do not choose. They record in normalized form and then generate denormalized copies for reading, rebuilding those copies automatically from the source on a schedule. That last step is the whole trick. A copy regenerated from a single source cannot drift for longer than the gap between rebuilds, because each rebuild reasserts the truth. A copy that people maintain by hand can drift forever, because each missed edit is permanent.

Copies are safe when they are derived from one source, and dangerous when people edit them by hand.

That gives us a rule that is easier to apply than any debate about purity. Duplication is not the sin; unmanaged duplication is. Store each fact once, at the point where it is recorded. Copy it as widely as readers need. And make sure every copy can be thrown away and rebuilt from the original, because one day it will have to be.

Seen this way, store each fact in exactly one place and copy each fact to wherever it is needed stop being rival creeds. They are two halves of one system: a careful recorder at the source and an obliging briefer downstream, connected by a rebuild that keeps the briefer honest.

Four questions for your next design

• Who writes, and how often? Frequent writes favor one copy of each fact.
• Who reads, and what do they ask? Many readers asking broad questions favor wide, ready-made tables.
• Is every copy derived? A copy someone edits by hand is a second source of truth, and it will eventually disagree with the first.
• What happens when a fact changes? Trace one change of address through the system and count how many places it has to go.