When does denormalization actually win?
When the table is read all the time and written once, and the join that you would avoid is the slow part.
-- The normalised source of truth.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
city VARCHAR(80) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (id)
);
So when does it actually win? Four situations.
The first one is a summary table. A table of the total for each day and each product is much smaller than the orders, and reading it needs no join at all.
The second one is a snapshot. The shipping address on an order has to be the address that the customer had at that moment. A join to the customers table gives you today's address, so the address has to be copied onto the order.
The third one is a value that never changes, such as the name of a category or the country of a customer. Copying it costs nothing to keep in step, because it does not move.
The fourth one is when the measurement says so. You have looked at the plan, the join is the slow step, and the table is big enough for that to matter. Denormalising because it feels faster is how you get a copy that is out of step and no plan to tell you why.
So the rule to remember is this. Denormalise when the value never changes, when you need a snapshot of how it was, or when you have measured the join to be the problem.
The next two questions in this topic show the shape and then the hard part.