Database normalization

Database normalization is the discipline of storing each fact in exactly one place. You write down the rules that tie columns together (functional dependencies), then split tables along those rules until every column describes the key, the whole key and nothing but the key. The result is a schema where a change touches one row, a new thing can be recorded before anything refers to it, and deleting one fact never deletes another.

Picture it first: a tiffin service’s register

A tiffin service starts with one long register. Every delivery gets a line: date, customer name, flat number, dish, price, cook. It works for a month. Then:

• Mrs Iyer moves flats. Her flat number is on 600 lines; the owner fixes the latest page and the delivery boy goes to the old flat.
• A new customer signs up for next week. With no delivery yet, there is no line to write her on.
• Mr Das stops his tiffin and his pages are torn out, taking the only record that the cook makes fish curry on Fridays.

The fix is a few card boxes: one card per customer, one per dish, and a register whose lines only say “customer 14, dish 3, two boxes”. A move is one card, a new customer gets a card on day one, and tearing out deliveries never loses a recipe.

Map it back:

• The register is a wide, unnormalized table.
• The three problems are the update, insertion and deletion anomalies.
• The card numbers are keys; a register line pointing at a card holds a foreign key.
• Deciding which fact goes on which card is normalization.

One flat table, three anomalies

Here is the kind of export a food-delivery start-up’s operations team lives in: one row per dish per order.

order_lines_flat(order_id, dish_id, placed_at, customer_id, customer_name, restaurant_id, restaurant_name, restaurant_area, dish_name, unit_price, qty), with the primary key (order_id, dish_id).

Update anomaly. Dosa Corner renames itself Dosa Point. Its name sits on every line of every order it has ever had: at 120 orders a day and 2.5 dishes per order, 300 rows a day, so after a year 300 × 365 ≈ 110,000 rows to rewrite. Lock them all in one long transaction, or update in batches while readers see both names. Miss a batch and the table disagrees with itself for good.

Insertion anomaly. Chaat Street signs up on Monday and takes its first order on Friday. Until then it has no row, because order_id and dish_id form the primary key and a key column cannot be NULL. You cannot record one fact without inventing another.

Deletion anomaly. Meera cancels order 5004 and support deletes its rows. That was the only row mentioning filter coffee, so the menu loses a dish and the system forgets Meera ever signed up.

All three have one root cause: a column holds a fact about something other than the row’s key, so the fact repeats wherever that something appears. Codd flagged it in the paper that introduced the relational model, with an employee table that also stored each manager’s name.

Functional dependencies: the rules behind the columns

A functional dependency X → Y (“X determines Y”) says that any two rows that agree on the columns X must agree on Y. Every normal form above 1NF is defined with them, and they come from business rules:

1. An order is placed once, by one customer, at one restaurant: order_id → placed_at, customer_id, restaurant_id.
2. A customer has one name: customer_id → customer_name.
3. A restaurant has one name and one area: restaurant_id → restaurant_name, restaurant_area.
4. A dish is on one restaurant’s menu: dish_id → dish_name, restaurant_id.
5. Quantity and the price charged belong to the line: order_id, dish_id → qty, unit_price.

Rules, not sample data

The sample in Figure 1 contains two traps.

• Every customer name is unique, so customer_name → customer_id happens to hold. It is not a rule: two customers called Asha is normal, and a design built on it would merge them.
• Masala dosa is ₹90 on both orders, so dish_id → unit_price seems to hold. But a line keeps the price charged that day: after a rise to ₹95, new lines say 95 and old ones 90. That is the snapshot idea from data modeling.

Data can disprove a dependency (two rows that agree on X and differ on Y) but never prove one. Ask the product owner instead: can an order span two restaurants? Can a dish move between menus? Each answer is a dependency, or its absence. Dependencies also assume clean identifiers: Kent notes that one person stored under two spellings of an address shows no dependency at all.

Keys, prime attributes and closure

• A superkey is a set of columns that determines every column, so no two rows can share its values. (order_id, dish_id, qty) is a superkey of the flat table.
• A candidate key is a minimal superkey: remove any column and it stops being one. (order_id, dish_id) is the flat table’s only candidate key.
• The primary key is the candidate key you declare; any others become UNIQUE constraints.
• A prime attribute belongs to some candidate key (order_id and dish_id here); the rest are non-prime. 2NF and 3NF only restrict non-prime columns, which matters for BCNF.

Closure is the one tool you need. To find what a set of columns X determines, start with X, keep adding the right side of any dependency whose left side you already have, and stop when nothing new appears. The result is X⁺.

• {order_id}⁺: rule 1 adds placed_at, customer_id and restaurant_id; rules 2 and 3 then add the customer and restaurant names and the area. Seven columns, but no dish_id, dish_name, qty or unit_price, so order_id alone is not a key.
• {order_id, dish_id}⁺: those seven, plus dish_name (rule 4), qty and unit_price (rule 5). All eleven columns, so a superkey; neither half is one alone, so it is a candidate key.

X is a superkey exactly when X⁺ contains every column, and X → Y holds exactly when Y is inside X⁺. Armstrong’s axioms (reflexivity, augmentation, transitivity) formalize this: they derive every implied dependency and nothing else.

First normal form: one value per cell

Codd’s 1970 paper used “normal form” for one property: no column whose values are themselves tables (nonsimple domains). He removed them by pushing the parent’s key down into each nested table, and noted that “further operations of a normalizing kind” were possible. Today that is first normal form: every column holds one value from its domain, with no repeating groups. The team’s first export broke it twice:

• A list in a cell: items = "2× Chicken biryani @280; 2× Raita @40". You cannot index a dish, sum quantities or point a foreign key into that string.
• Repeating groups: dish1, qty1, dish2, qty2, dish3, qty3. The fourth dish has nowhere to go, and “orders with raita” must test three columns.

The fix for both is one row per order line: the flat table. So the flat table is in 1NF and still has all three anomalies. 1NF is the entry ticket, not the cure.

Arrays and jsonb: when they are fine

“Atomic” is a property of how you use a value, not of its type. The db-book slides use a roll number like CS0012: atomic, until code reads the department from its first two letters. PostgreSQL’s docs say the same of its rich types. Arrays “are not sets”, and searching for specific elements “can be a sign of database misdesign”. A JSON document should ideally be “an atomic datum” that cannot reasonably be split into independently modified parts, and any update locks the whole row.

So a jsonb snapshot of the delivery address, or a payment gateway’s raw response kept for audit, is one value: written once, read whole. A dish’s tags text[] that you display and filter as a set is fine too:

ALTER TABLE dishes ADD COLUMN tags text[] NOT NULL DEFAULT '{}';
CREATE INDEX ON dishes USING gin (tags);
SELECT dish_id, name FROM dishes WHERE tags @> ARRAY['jain'];

But an array of dish IDs on the order, or a jsonb item list edited line by line, is a child table in disguise: no foreign key per element, no per-item quantity, no cheap “who ordered dish 91?”. If you query inside it, update parts of it or need constraints on its pieces, make it a table.

Second normal form: the whole key

A table is in 2NF if it is in 1NF and no non-prime column depends on a proper subset of a candidate key. It only bites with multi-column keys: in Kent’s terms, a non-key field must not be a fact about a subset of the key.

The flat table fails (the teal arrows in Figure 2): placed_at, customer_id and restaurant_id depend on order_id alone, and dish_name on dish_id alone. Only qty and unit_price need the whole key. Split once per partial dependency: move the determinant and what it determines into a table keyed by the determinant, and leave the determinant behind as a foreign key.

• orders_wide(order_id PK, placed_at, customer_id, customer_name, restaurant_id, restaurant_name, restaurant_area)
• dishes(dish_id PK, dish_name, restaurant_id)
• order_lines(order_id, dish_id, qty, unit_price), keyed on both

Now a dish can exist before anyone orders it, and cancelling an order no longer deletes the menu. But orders_wide still repeats Asha’s name on every order she places and Dosa Corner’s on every order it receives.

Third normal form: nothing but the key

A table is in 3NF if it is in 2NF and no non-prime column depends on another non-prime column: no transitive chain key → A → B. The precise textbook form, which sets up BCNF, is: for every non-trivial dependency X → A, either X is a superkey or A is a prime attribute.

Kent’s summary covers both: every non-key column states a fact about the key, the whole key, and nothing but the key. 2NF is “the whole key”; 3NF is “nothing but”. orders_wide breaks it through the red arrows: order_id → customer_id → customer_name and order_id → restaurant_id → restaurant_name, restaurant_area. Split along each middle column:

• customers(customer_id PK, customer_name)
• restaurants(restaurant_id PK, restaurant_name, restaurant_area)
• orders(order_id PK, placed_at, customer_id FK, restaurant_id FK)

Now the rename is one row, Chaat Street is one insert into restaurants, and cancelling Meera’s order leaves both her and the filter coffee in place.

A note on history: Codd’s follow-up, “Further Normalization of the Data Base Relational Model” (an IBM research report, published in 1972), was the first formal treatment of 2NF and 3NF. BCNF carries the names of Raymond Boyce and Codd. Ronald Fagin introduced 4NF in 1977 and 5NF in 1979.

Boyce–Codd normal form: every determinant is a key

BCNF says: for every non-trivial dependency X → Y, X is a superkey. It drops 3NF’s escape hatch (“or Y is prime”), which only matters when two candidate keys overlap. Here is such a case. The start-up runs cloud kitchens: each cooks exactly one cuisine, and each area routes each cuisine to exactly one kitchen, recorded in area_routing:

The dependencies are area, cuisine → kitchen_id and kitchen_id → cuisine. The closure of (area, kitchen_id) is all three columns, so the table has two candidate keys, (area, cuisine) and (area, kitchen_id), and every column is prime.

• 3NF holds: for kitchen_id → cuisine, the right side cuisine is prime.
• BCNF fails: kitchen_id is not a superkey, yet it determines something.

The redundancy is real: “K3 cooks North Indian” is stored three times, a switch to Chinese is three updates, and a new kitchen’s cuisine has no row until it serves an area.

The BCNF split is kitchens(kitchen_id PK, cuisine) plus area_kitchens(area, kitchen_id). It is lossless (kitchen_id is the key of kitchens) but loses a dependency: area, cuisine → kitchen_id now spans two tables, so nothing stops Indiranagar being routed to K8, a second North Indian kitchen. Checking that needs a join on every insert: a trigger or application code.

The db-book slides state the general result: BCNF and dependency preservation cannot always both be had, while 3NF always can. A practical middle path is to stay in 3NF and let constraints guard the copy:

CREATE TABLE kitchens (
  kitchen_id text PRIMARY KEY,
  cuisine    text NOT NULL,
  UNIQUE (kitchen_id, cuisine)            -- a target for the composite FK below
);

CREATE TABLE area_routing (
  area       text NOT NULL,
  cuisine    text NOT NULL,
  kitchen_id text NOT NULL,
  PRIMARY KEY (area, cuisine),            -- enforces area, cuisine → kitchen_id
  FOREIGN KEY (kitchen_id, cuisine)
    REFERENCES kitchens (kitchen_id, cuisine)
    ON UPDATE CASCADE                     -- a cuisine change rewrites the copies
);

cuisine is still repeated, but the composite foreign key stops any copy disagreeing with kitchens, and the primary key enforces the rule the BCNF split lost. The extra UNIQUE (kitchen_id, cuisine) is needed because PostgreSQL only lets a foreign key reference a column set that is a primary key, a unique constraint or a non-partial unique index. More patterns like this are in foreign key design.

Fourth and fifth normal forms

4NF: two independent lists in one table

Search filters restaurants by cuisine tag and delivery PIN code, so someone builds restaurant_tags_pins(restaurant_id, tag, pin). Biryani Junction has 3 tags and delivers to 40 PIN codes. Tags and PINs are unrelated, so every tag must pair with every PIN: 3 × 40 = 120 rows, versus 3 + 40 = 43 rows in two tables. A new PIN code adds 3 rows instead of 1, and missing one hides a tag in one area.

The key is all three columns and there are no non-trivial functional dependencies, so the table is in BCNF. What it has is a multivalued dependency: restaurant_id ↠ tag means a restaurant’s set of tags is independent of the other columns, so every tag appears with every PIN. 4NF says that for every non-trivial multivalued dependency X ↠ Y, X must be a superkey. Every functional dependency is also a multivalued one, so 4NF implies BCNF. The fix is restaurant_tags(restaurant_id, tag) and restaurant_pins(restaurant_id, pin).

Kent’s test for “independent”: do the pairings carry information? If Dosa Corner serves breakfast only in some PIN codes, tag and PIN are linked and the three-column table is the right design.

5NF: a fact that needs three tables to rebuild

Fifth normal form (also called project-join normal form) deals with join dependencies: a table equal to the join of three or more of its projections, but not of any two. Suppose a rule holds: if a brand lists on a delivery platform, the platform operates in a city, and the brand has an outlet there, then the brand is listed on that platform in that city. Then brand_platform_city can be rebuilt from (brand, platform), (platform, city) and (brand, city), and the three-column table repeats facts. Without the rule, the three-column table is necessary and already in 5NF. Beyond 5NF sits domain-key normal form, which textbooks note is hard to reason with and rarely used.

Good splits: lossless and dependency-preserving

Check two properties for every split.

Lossless join: joining the pieces back gives exactly the original rows, never more. For a two-way split, a sufficient test is that the shared columns form a superkey of at least one piece; splitting on customer_id passes because it is the key of customers. A lossy split does not lose rows, it invents them. Figure 4 splits on customer_name instead, with two different customers called Asha.

Dependency preservation: every dependency can be checked inside one table. It matters because SQL databases enforce dependencies only as keys: a primary key or UNIQUE constraint is a dependency checked on every write. One that spans tables needs a join per write, and PostgreSQL’s docs say a CHECK constraint must not look at other rows, so it becomes a trigger or application code. The five-table schema keeps all five rules, each inside one table as its key.

The schema in SQL

Before, one wide table:

CREATE TABLE order_lines_flat (
  order_id        bigint        NOT NULL,
  dish_id         bigint        NOT NULL,
  placed_at       timestamptz   NOT NULL,
  customer_id     bigint        NOT NULL,
  customer_name   text          NOT NULL,
  restaurant_id   bigint        NOT NULL,
  restaurant_name text          NOT NULL,
  restaurant_area text          NOT NULL,
  dish_name       text          NOT NULL,
  unit_price      numeric(8,2)  NOT NULL,
  qty             integer       NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, dish_id)
);

After, five tables, each rule declared once:

CREATE TABLE customers (
  customer_id bigint PRIMARY KEY,
  name        text   NOT NULL
);

CREATE TABLE restaurants (
  restaurant_id bigint PRIMARY KEY,
  name          text   NOT NULL,
  area          text   NOT NULL
);

CREATE TABLE dishes (
  dish_id       bigint       PRIMARY KEY,
  restaurant_id bigint       NOT NULL REFERENCES restaurants,
  name          text         NOT NULL,
  menu_price    numeric(8,2) NOT NULL CHECK (menu_price >= 0),
  UNIQUE (restaurant_id, name)               -- one "Masala dosa" per menu
);

CREATE TABLE orders (
  order_id      bigint      PRIMARY KEY,
  placed_at     timestamptz NOT NULL,
  customer_id   bigint      NOT NULL REFERENCES customers,
  restaurant_id bigint      NOT NULL REFERENCES restaurants
);

CREATE TABLE order_lines (
  order_id   bigint       NOT NULL REFERENCES orders,
  dish_id    bigint       NOT NULL REFERENCES dishes,
  qty        integer      NOT NULL CHECK (qty > 0),
  unit_price numeric(8,2) NOT NULL,          -- price charged: a fact about the line
  PRIMARY KEY (order_id, dish_id)
);
CREATE INDEX ON order_lines (dish_id);       -- PostgreSQL does not index FK columns for you
CREATE INDEX ON orders (customer_id);

Two prices now exist, correctly: dishes.menu_price is today’s price, order_lines.unit_price is what this customer paid, two facts with different keys. Constraints that are not about dependencies (NOT NULL, CHECK) are covered in database constraints.

Reports that expect the old shape can read it from a view, which reassembles the original with joins:

CREATE VIEW order_lines_report AS
SELECT o.order_id, o.placed_at,
       c.customer_id, c.name AS customer_name,
       r.restaurant_id, r.name AS restaurant_name, r.area AS restaurant_area,
       l.dish_id, d.name AS dish_name, l.unit_price, l.qty
FROM order_lines l
JOIN orders      o ON o.order_id      = l.order_id
JOIN customers   c ON c.customer_id   = o.customer_id
JOIN restaurants r ON r.restaurant_id = o.restaurant_id
JOIN dishes      d ON d.dish_id       = l.dish_id;

Each join follows a non-null foreign key to a primary key, so it matches exactly one row: the lossless property in practice. The three anomalies from Figure 1 are now one-row statements:

UPDATE restaurants SET name = 'Dosa Point' WHERE restaurant_id = 9;     -- 1 row, not 110,000
INSERT INTO restaurants VALUES (12, 'Chaat Street', 'Koramangala');      -- no order needed
DELETE FROM order_lines WHERE order_id = 5004;                            -- Meera and the
DELETE FROM orders      WHERE order_id = 5004;                            -- coffee survive

Run the two deletes in one transaction so nobody sees an order without lines.

Comparing the normal forms

What normalization gives you, and what it doesn’t

It gives you:

• No redundancy-driven anomalies. Each fact has one home, so a change is one row and a delete removes one fact.
• Rules the database enforces for free. In BCNF every dependency inside a table has a key on its left, so it is a primary key or UNIQUE constraint the database already checks.
• Cheaper writes, smaller rows. A rename touches one row; an order line is four columns, not eleven.
• Room for new questions. A new report is a join or an index, not a migration.

It does not:

• Choose keys. Natural or surrogate, bigint or UUIDv7, is a separate decision (data modeling).
• Validate values. A negative quantity is perfectly normalized; types, NOT NULL and CHECK catch it (database constraints).
• Declare relationships. The column a split leaves behind is only a foreign key if you declare it (foreign key design).
• Enforce cross-table rules. “Every dish on an order comes from that order’s restaurant” spans three tables; no normal form expresses it. A composite foreign key or a check in code does.
• Make queries fast. It usually adds joins. Speed comes from indexes, good query plans and, sometimes, deliberate copies.
• Remove every redundancy. Kent notes the normal forms only look inside one table: an employee_location table beside employee and department tables is redundant, yet every table is in 3NF.

Where to stop, and when to go below it

For an OLTP database, aim for 3NF, and BCNF wherever it costs nothing (almost always). Check 4NF whenever a table holds two lists about the same parent. 5NF problems are rare; you will recognise one when the business states a symmetric three-way rule.

Kent is candid that the normal forms assume every non-key field is updated often and tend to penalize retrieval, so there is “no obligation to fully normalize” once real performance needs are counted. Designers deliberately go below 3NF for a counter on a hot read path, a display field copied to skip a join, a read model or search index fed by events, a document store without joins, and the star schemas of a data warehouse. Each copy buys faster reads with more writes, a window of disagreement and repair code. That trade, and how to keep copies in sync, is the subject of normalization vs denormalization.

Going deeper: traps and edge cases

Dependencies that change over time. customer_id → address holds only for the current address. Keep history and it breaks; the usual fix is a key that includes time, such as (customer_id, valid_from), or snapshots on the rows that need the old value.

Facts hidden inside a code. In a SKU like BLR-KIT-0042, whose first letters are the city, a dependency lives inside one column. Once code parses it, store the city as a column and keep the code opaque.

Over-normalizing. Splits follow dependencies, not taste. A customer_names table, or a lookup table for a five-value status, removes no anomaly and adds a join to every read.

The N+1 trap. Five tables tempt an ORM into one query per order line to fetch the dish. Use joins or batched IN queries (the N+1 query problem).

Cheat sheet

How to say it in the interview

“Normalization stores each fact once so updates, inserts and deletes can’t create contradictions. I list the functional dependencies from business rules: an order has one customer and one restaurant, a dish belongs to one menu, quantity and price belong to the order line. The flat order-lines table, keyed by order and dish, breaks 2NF because restaurant and customer details depend on the order alone, and 3NF because names hang off IDs. So I split into customers, restaurants, dishes, orders and order lines, each split lossless because the shared column is the key of one side. That’s BCNF, every rule is a primary or unique key, and the price on the line stays as a snapshot. I’d only denormalize after measuring a hot read, with a named way to keep the copy in sync.”

Is 3NF always enough? For most OLTP schemas, yes: 3NF and BCNF only differ when candidate keys overlap, and then you choose between a guarded redundancy and a cross-table check. Also check 4NF when a table mixes two independent lists; those pass BCNF and still multiply rows.

What is the difference between 2NF and 3NF? 2NF removes facts about part of a composite key (dish_name keyed by order and dish); 3NF removes facts about a non-key column (customer_name beside customer_id). A table whose only candidate key is a single column is automatically in 2NF but can still fail 3NF.

Does NoSQL mean no normalization? No. Without joins you denormalize on purpose, but the dependencies still tell you which fact is the source of truth and which copies to rewrite when it changes. Document stores also reference shared or unbounded data rather than embedding it.

Is a JSON column a 1NF violation? It depends on use. A jsonb value you write once and read whole, like an address snapshot or a gateway response, is one atomic value. If you search inside it, update parts of it, or need foreign keys or constraints on its elements, it is a child table in disguise and should become one.

Where to go next

• Normalization vs denormalization: when to store copies, and how to keep them correct.
• Data modeling and denormalization: the full modelling process, keys and IDs, snapshots vs references.
• Database constraints: NOT NULL, CHECK, UNIQUE and exclusion constraints, the rules normalization does not cover.
• Foreign key design: composite keys, cascades and indexing the referencing side.
• Database indexes: making the joins of a normalized schema cheap.
• SQL vs NoSQL and database types: where the relational model fits and where query-first design takes over.

References and further reading

1. A Relational Model of Data for Large Shared Data Banks (E. F. Codd, Communications of the ACM, June 1970)cs.uwaterloo.caSection 1.4 introduces “normal form” as removing nonsimple (nested) domains and promises further normalizing operations; section 2.2 flags a copied manager name as redundancy.
2. A Simple Guide to Five Normal Forms in Relational Database Theory (William Kent, CACM, 1983)bkent.netPlain-language 1NF to 5NF, “the key, the whole key, and nothing but the key”, the additive-vs-multiplicative row count for 4NF, and who introduced 2NF/3NF, 4NF and 5NF.
3. Database System Concepts, 7th edition, Chapter 7 slides: Normalization (Silberschatz, Korth and Sudarshan)db-book.comFormal definitions of FDs, closure, superkeys, BCNF, 3NF and 4NF; the lossless-split test; BCNF versus dependency preservation; atomicity as a property of use.
4. PostgreSQL documentation: Constraintspostgresql.orgPrimary keys imply NOT NULL and a unique index; foreign keys must reference a primary key or unique constraint and do not index the referencing columns; CHECK cannot look at other rows.
5. PostgreSQL documentation: Arrayspostgresql.orgThe tip that arrays are not sets and that searching for elements can signal misdesign, with a row-per-element table as the alternative.
6. PostgreSQL documentation: JSON typespostgresql.orgDesigning JSON documents: keep a predictable structure, each document an atomic datum, and remember an update locks the whole row.