Normalization — 1NF, 2NF, 3NF (and When Not To)

TL;DR

Normalization is the process of designing tables so every fact is stored exactly once. The motivation: redundancy → update anomalies. If a customer’s address is in 50 order rows, changing it requires 50 updates and one of them will get missed.

Codd defined a ladder of normal forms, each stricter than the last:

• 1NF — atomic columns; no repeating groups; one value per cell.
• 2NF — 1NF + every non-key column depends on the whole primary key (matters only with composite keys).
• 3NF — 2NF + non-key columns depend on the key, not on other non-key columns (“the key, the whole key, and nothing but the key”).
• BCNF — a stricter 3NF; rare in practice to need beyond 3NF.

Reach 3NF for transactional schemas. Denormalize deliberately — and documentedly — when the read pattern justifies the duplication. Analytics warehouses (star schemas) and ML feature tables are intentionally denormalized.

A worked example — the un-normalized table

orders
+----+-----------+----------------+----------+----------+--------+----------+
| id | customer  | customer_email | products | quantities | totals | placed   |
+----+-----------+----------------+----------+------------+--------+----------+
| 1  | Alice     | alice@x.com    | A,B      | 2,1        | 30,15  | 2026-04-01 |
| 2  | Alice     | alice@x.com    | C        | 3          | 60     | 2026-04-15 |
| 3  | Bob       | bob@x.com      | A,C      | 1,2        | 15,40  | 2026-04-20 |
+----+-----------+----------------+----------+------------+--------+----------+

Multiple problems here:

1. products is a comma-separated list — violates 1NF (not atomic).
2. Alice’s email is duplicated across her two orders — update anomaly: if she changes email, you have to update every row.
3. To find all of Alice’s orders, you’d WHERE customer = 'Alice' — but if there are two Alices, ambiguous.

1NF — atomic columns

Split the comma-separated lists into rows:

orders                          order_items
+----+-----------+----------+   +----+----------+---------+----------+--------+
| id | customer  | placed   |   | id | order_id | product | quantity | total  |
+----+-----------+----------+   +----+----------+---------+----------+--------+
| 1  | Alice     | 04-01    |   | 1  | 1        | A       | 2        | 30     |
| 2  | Alice     | 04-15    |   | 2  | 1        | B       | 1        | 15     |
| 3  | Bob       | 04-20    |   | 3  | 2        | C       | 3        | 60     |
+----+-----------+----------+   | 4  | 3        | A       | 1        | 15     |
                                | 5  | 3        | C       | 2        | 40     |
                                +----+----------+---------+----------+--------+

Now each cell is one value. The customer-email duplication remains.

3NF — non-key columns depend on the key

Customer name and email depend on the customer, not on the order. Move them to a separate table:

customers                     orders
+----+-----------+-------------+   +----+-------------+----------+
| id | name      | email       |   | id | customer_id | placed   |
+----+-----------+-------------+   +----+-------------+----------+
| 1  | Alice     | alice@x.com |   | 1  | 1           | 04-01    |
| 2  | Bob       | bob@x.com   |   | 2  | 1           | 04-15    |
+----+-----------+-------------+   | 3  | 2           | 04-20    |
                                   +----+-------------+----------+

Now Alice’s email is stored once. Updating it touches one row.

The same logic applies to products (a products table with id, name, sku, base_price) and to anything else that’s referenced from multiple rows.

2NF — composite keys

Only matters when the primary key spans multiple columns. Suppose order_items had (order_id, product_id) as its composite PK and we added product_name:

order_items
+----------+------------+--------------+----------+
| order_id | product_id | product_name | quantity |
+----------+------------+--------------+----------+
| 1        | A          | Widget       | 2        |
| 1        | B          | Gadget       | 1        |
| 2        | C          | Gizmo        | 3        |
+----------+------------+--------------+----------+

product_name depends on product_id alone — not the whole key. That’s a 2NF violation. Move product_name into the products table.

BCNF — a sharper 3NF

Boyce-Codd Normal Form fires when a non-key column functionally determines part of the key. Rare; usually arises with awkward composite keys. If you’ve reached 3NF, you’ll meet BCNF violations rarely and the fix is the same shape (extract a table).

The case for normalization

• One source of truth. Customer email lives in one place; updates are atomic.
• Smaller storage. No duplication.
• Constraint enforcement. Foreign keys ensure referential integrity.
• Insert/update/delete anomalies prevented. No partial-update inconsistencies.

The case for denormalization

For transactional (OLTP) workloads, normalize. For analytics (OLAP) and ML, the calculus flips:

• Joins are expensive at warehouse scale; pre-joining (denormalizing) beats per-query joins.
• Analytics workloads are mostly read; update anomalies don’t bite.
• Wide flat tables are friendly to columnar storage and BI tools.
• Feature stores serve features by primary key — wide rows mean one lookup, not five joins.

The dominant analytics pattern is the star schema:

• A central fact table (e.g., orders) with foreign keys.
• Surrounding dimension tables (customers, products, dates) with descriptive columns.

Dimensions are mildly denormalized (a customers table with country_name instead of country_id joining to a countries table — trades a little redundancy for fewer joins). See Star Schema and ML Feature Stores (SQL 403).

Common pitfalls

• Over-normalization. Five-way joins to assemble a single user profile = pain. If a “join” never breaks because the values are effectively immutable (country codes, ISO currency), inline them.
• Under-normalization in OLTP. Storing comma-separated lists, duplicating mutable facts, no foreign keys. The classic “I’ll refactor later” tech debt.
• Wide tables in OLTP. A 200-column users table where most columns are NULL most of the time is a smell — split into related tables.
• Denormalization without write discipline. If you denormalize product names into order_items, you must update every row when a product is renamed. Either don’t denormalize, or accept that the denormalized name is “the name as of order time” (which is often the right semantic for receipts).
• Confusing OLTP and OLAP needs. Same data, different schemas. The transactional system is normalized; the analytics warehouse is denormalized. dbt or a CDC pipeline transforms one into the other.

Production patterns for ML

1. The OLTP-to-warehouse transform. Your application DB is normalized; the warehouse is a star schema:

OLTP (3NF):  users, addresses, orders, order_items, products, categories
                       |
                   ETL / dbt
                       |
                       v
WAREHOUSE (denormalized):
  fact_orders(user_id, order_id, day_id, total, ...,
              user_country, product_category, ...)
  dim_users(...), dim_products(...), dim_dates(...)

The warehouse has the FKs to dimensions and the most-queried dimension attributes flattened into the fact table — a few joins or zero joins for common queries.

2. Feature tables: maximally denormalized. A feature row is one user, hundreds of columns, all the features the model needs:

CREATE TABLE user_features (
    user_id              int PRIMARY KEY,
    -- demographic
    country              text,
    age_bucket           text,
    -- behavior 30d
    sessions_30d         int,
    avg_session_s_30d    float,
    purchases_30d        int,
    spend_30d            numeric,
    -- behavior 90d
    sessions_90d         int,
    spend_90d            numeric,
    -- many more ...
    feature_computed_at  timestamptz
);

Online serving: one PK lookup, the model gets every feature it needs. This is the polar opposite of 3NF and exactly right for the use case.

Resources

• PostgreSQL documentation — postgresql.org/docs
• C. J. Date — An Introduction to Database Systems — the canonical relational-theory textbook.
• Designing Data-Intensive Applications, ch. 2 — dataintensive.net — relational vs document data models.
• Kimball — The Data Warehouse Toolkit — the canonical book on dimensional modeling.