Normalisation — The Rules That Remove Update Anomalies
In a nutshell
Normalization is a procedure that splits tables so that the same fact is not written in several places, and the reason to do so is not storage space but to eliminate update anomalies.
Why this was needed
Suppose you wrote even the customer's name and address in a single orders table. Three problems arise.
- Update anomaly — when a customer moves, you must fix every order row of that customer. If you miss even one, the same customer has two addresses.
- Insertion anomaly — a customer who has not placed an order yet has no place to be registered.
- Deletion anomaly — if you delete the last order, that customer's information disappears with it.
Normalization is the procedure that removes these three anomalies. Saving space is only a side effect.
How it works
What is used in practice is generally up to third normal form.
- 1NF — every attribute must be an atomic value. Do not put several comma-separated values in one cell.
- 2NF — it is 1NF, and there must be no attribute that depends on only part of the primary key. This matters only with a composite key. For example, if
(주문번호, 상품번호)(order number, product number) is the key and상품명(product name) depends only on the product number, split it out. - 3NF — it is 2NF, and there must be no attribute that depends on a non-key attribute. If
우편번호(postal code) determines도시(city), move that relationship out into a separate table.
The key is not the form of the rules but the principle that a single fact is written in only one place. If you keep this principle, an update always finishes in one place, so inconsistency becomes structurally impossible.
Finding functional dependencies by hand
The definitions of the normal forms all stand on one concept, functional dependency. A → B means "once A is fixed, B is fixed to exactly one value". If you take a table and draw these arrows, judging the normal form becomes mechanical.
Let us take an order table as an example.
주문상세(주문번호, 상품번호, 수량, 상품명, 단가, 고객번호, 고객명, 고객주소)
키: (주문번호, 상품번호)
화살표를 그려 보면:
(주문번호, 상품번호) → 수량 ← 키 전체에 종속. 정상
상품번호 → 상품명, 단가 ← 키의 "일부" 에만 종속. 2정규형 위반
주문번호 → 고객번호 ← 키의 "일부" 에만 종속. 2정규형 위반
고객번호 → 고객명, 고객주소 ← 키가 아닌 것에 종속. 3정규형 위반
Split the table along the arrows. The left side becomes the key of the new table.
주문상세(주문번호, 상품번호, 수량)
상품(상품번호, 상품명, 단가)
주문(주문번호, 고객번호, 주문일)
고객(고객번호, 고객명, 고객주소)
There is only one place in this procedure where judgment enters: whether the arrow actually holds. "Postal code → city" is mostly true in Korea but has exceptions, and in the United States a single ZIP code sometimes spans several cities. This is the point where domain knowledge is needed.
Summary table of the normal forms
| Normal form | What it removes | One-line test |
|---|---|---|
| 1NF | Repeating groups, multiple values | Is there one value per cell |
| 2NF | Partial functional dependency | Is there a column hanging on only part of a composite key |
| 3NF | Transitive functional dependency | Does a non-key column determine another column |
| BCNF | Exceptions where candidate keys are entangled | Is every determinant a candidate key |
BCNF catches the rare cases in which anomalies remain even though 3NF is satisfied. It arises when there are several candidate keys that overlap, and it is rarely met in practice. Up to 3NF is what you use in practice.
The misconception that normalization hurts performance
The statement "normalizing adds joins and makes it slower" is only half right. Joins increase, but normalized tables have shorter rows, so more fit on a page, and the indexes are also smaller. An update touches only one place, so it is much faster.
When things actually get slower, it is usually the absence of indexes, not normalization itself. If a foreign key column has no index, a full scan occurs on every join. PostgreSQL creates an index automatically for primary keys but does not for foreign keys. It is common to conclude "it's slow because of normalization" without knowing this.
What it looks like in the field
Normalization is a default, not a religion. There are times to break it, and you must break it knowing what you pay in return.
A typical case where denormalization is justified is materializing an aggregate value. An example is maintaining a post's comment count in a column rather than counting it every time. The price is clear. The values in two places can diverge, and the responsibility for preventing that passes to the application. There is more than one path by which they diverge: a bulk delete that bypassed the trigger, a migration that briefly turned the trigger off, and an omission in one of several code paths.
So when you introduce denormalization, you must leave three things together: why you broke it, what guarantees consistency, and the recomputation query that fixes it when it diverges. If you do not write the third one in advance, you end up improvising it during incident response.
It is also worth knowing that when comments pile up on a popular post, everyone tries to update the same row and lock contention occurs. The alternatives are to only append increments to a separate table and add them up periodically, or to ask again whether an exact real-time value is really needed. For most counters, nothing happens if they are 5 seconds late.
What to check in the quiz that follows
Instead of memorizing the definitions of normal forms, check whether you can judge which anomaly each is meant to eliminate and when breaking it is reasonable.