Data normalization
Data normalization is the process of structuring relational tables to reduce data redundancy and prevent update anomalies, typically by applying a sequence of rules called normal forms.
Overview
Normalization, formalized by Edgar Codd alongside the relational model, organizes data so that each fact is stored in exactly one place. It proceeds through a series of normal forms, each stricter than the last:
- 1NF: every column holds a single, atomic value (no repeating groups or lists in a cell).
- 2NF: builds on 1NF, and every non-key column depends on the whole primary key, not just part of a composite key.
- 3NF: builds on 2NF, and eliminates transitive dependencies — non-key columns must depend only on the key, not on other non-key columns.
Higher forms exist (BCNF, 4NF, 5NF) but most operational schemas stop at 3NF, which removes the great majority of redundancy-driven bugs.
Why it matters
A normalized customer/order schema stores a customer's address once, in a customers table, rather than repeating it on every row of an orders table. Update it once and every order automatically reflects the change — no risk of an orders table drifting out of sync with the true address. This matters most for transactional (OLTP) systems, where many small, concurrent writes need strong consistency and minimal update overhead.
Example
Copy code
-- Unnormalized: customer info repeated on every order row
-- order_id | customer_name | customer_email | product | amount
-- Normalized (3NF)
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name VARCHAR,
email VARCHAR
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
product VARCHAR,
amount DECIMAL(10,2)
);
DuckDB angle
DuckDB enforces standard relational constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL) so you can build and query normalized schemas directly. But DuckDB's columnar, vectorized engine is optimized for analytical scans and joins rather than the high-frequency single-row writes normalized OLTP schemas are designed for — in a warehouse or MotherDuck context, data is usually denormalized into star schemas after being extracted from a normalized source system, trading some redundancy for simpler, faster analytical queries.
Related terms
Third normal form (3NF) is a database design rule requiring a table to already be in second normal form and to have no transitive dependencies — every non-key column must depend only on the primary key.
OLAP vs OLTP →OLAP (Online Analytical Processing) and OLTP (Online Transaction Processing) are two workload categories: OLTP handles many small, concurrent transactional writes on normalized schemas, while OLAP handles large read-heavy analytical queries on denormalized, columnar data.
Denormalization →Denormalization is the deliberate process of combining or duplicating normalized data — for example, flattening related tables together — to reduce joins and speed up read-heavy analytical queries.
OLTP →OLTP (Online Transaction Processing) refers to systems designed for fast, high-concurrency, small read/write transactions — like inserting an order or updating an account balance — typically backed by normalized relational schemas.
Denormalized table →A denormalized table is a table that intentionally stores redundant, pre-joined data from multiple normalized sources, trading storage and update simplicity for faster, simpler reads.
Snowflake schema →A snowflake schema is a dimensional model where dimension tables are normalized into multiple related sub-tables instead of one flat table, reducing redundancy at the cost of extra joins.
FAQS
To eliminate redundant data and the update anomalies it causes, by ensuring each fact is stored in only one place and every column depends only on its table's key.
Denormalized data is generally preferred for analytics because it reduces the number of joins needed to answer a query. Normalization is more important for transactional systems where write consistency and avoiding update anomalies matter more than read simplicity.
