Data Normalization
An early version of the PedalCity app stored the station's name and address on every ride row. When station 17 was renamed from "Market Gate" to "Old Market", 212,000 ride rows needed changing. The job crashed halfway, and for a week the station had two names. Normalization is the design habit that prevents this.
The rule
Store each fact once; point to it from everywhere else.
Here is the problem table:
| ride_id | station_id | station_name | station_address | fare |
|---|---|---|---|---|
| 88121 | 17 | Market Gate | 4 Bazaar Rd | 45 |
| 88122 | 17 | Market Gate | 4 Bazaar Rd | 30 |
| 88123 | 4 | Lake View | 12 Shore St | 60 |
The station's name is a fact about the station, not about the ride, yet it is copied onto every ride. That causes three problems, known as anomalies:
- Update anomaly: renaming a station means changing thousands of rows, and missing one leaves two versions of the truth.
- Insert anomaly: you cannot record a new station until someone rides from it.
- Delete anomaly: delete a station's only ride and you lose the station's address too.
The fix is to move station facts to their own table and keep only the key on each ride:
CREATE TABLE stations (
station_id INT PRIMARY KEY,
station_name TEXT NOT NULL,
station_address TEXT NOT NULL
);
CREATE TABLE rides (
ride_id BIGINT PRIMARY KEY,
station_id INT REFERENCES stations (station_id),
fare NUMERIC(8,2)
);The formal steps are called normal forms. First normal form: one value per cell, with no lists packed into a column. Second and third: every non-key column must depend on the key, on the whole key, and on nothing but the key. station_name depends on station_id, not on ride_id, so it moves out.
Normalization is ideal for the app's database, where writes are constant and must stay consistent. Analytical models often denormalize on purpose to save joins, as later lessons show. Know the rule before you bend it.
The shortcut
For each column ask: "Is this a fact about the row's key, or about something else?" If it describes something else, that something deserves its own table.
fare
The fare is a fact about this ride. It stays.
Drills
Answer fast — trust the rule.
Station 17 is renamed and some ride rows still show the old name. Which anomaly is this?
In a rides table keyed by ride_id, which column should move to another table?
A column holds "17,4,9" to list the stations a van visited. Which rule does it break?
Where does strict normalization help most?
Remember
- Store each fact once; point to it from everywhere else.
- Ask of each column: is it about this row's key, or about something else?
- Normalize for writes; analytical models may denormalize on purpose.