SQL examples use PostgreSQL unless another dialect is named. Short snippets assume their referenced tables already exist. Validate DDL in your target database before applying it.
What is Database Normalization?
Database normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity. Introduced by Edgar F. Codd in the 1970s, normalization involves dividing large tables into smaller, related tables and defining relationships between them.
The goal is to represent dependencies consistently and reduce avoidable duplication. Historical snapshots, such as a sale price, may belong in a different table because they represent different facts.
Why Normalize?
- Eliminate redundancy — Avoid storing the same data in multiple places.
- Prevent update anomalies — Changes to data only need to happen in one place.
- Prevent insertion anomalies — You can add new data without requiring unrelated data.
- Prevent deletion anomalies — Deleting a record doesn't accidentally remove unrelated data.
- Support maintainable queries — Clear dependencies make queries easier to reason about. Performance depends on access patterns, indexes, and joins; normalization alone does not make a query faster.
First Normal Form (1NF)
A table is in 1NF when:
- Each column contains only atomic (indivisible) values
- Each column contains values of a single type
- Rows are distinguishable by a candidate key; declare a primary key to enforce a chosen identifier in SQL
- There are no repeating groups or arrays
Before 1NF (Violating)
-- BAD: phone_numbers contains multiple values
| id | name | phone_numbers |
|----|-------|------------------------|
| 1 | Alice | 555-0100, 555-0101 |
| 2 | Bob | 555-0200 |After 1NF
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE phone_numbers (
id SERIAL PRIMARY KEY,
contact_id INTEGER REFERENCES contacts(id),
phone VARCHAR(20) NOT NULL
);
-- Each phone number is in its own row
-- | id | contact_id | phone |
-- |----|-----------|----------|
-- | 1 | 1 | 555-0100 |
-- | 2 | 1 | 555-0101 |
-- | 3 | 2 | 555-0200 |Second Normal Form (2NF)
A table is in 2NF when:
- It's already in 1NF
- Every non-prime attribute depends on each entire candidate key, with no dependency on only part of a key
A candidate key is a minimal set of attributes that uniquely identifies a row. Attributes appearing in any candidate key are prime attributes. Partial dependencies matter when a candidate key contains several columns; candidate keys include alternate unique identifiers, not only the chosen primary key. If a non-key column depends on only part of the key, it should be moved to a separate table.
Before 2NF (Violating)
-- BAD: course_name depends only on course_id, not the full key
-- PK: (student_id, course_id)
| student_id | course_id | course_name | grade |
|-----------|-----------|-------------|-------|
| 1 | 101 | Math | A |
| 1 | 102 | Physics | B |
| 2 | 101 | Math | C |After 2NF
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE enrollments (
student_id INTEGER REFERENCES students(id),
course_id INTEGER REFERENCES courses(id),
grade CHAR(2),
PRIMARY KEY (student_id, course_id)
);
-- course_name now lives only in the courses table
-- grade depends on both student_id AND course_idThird Normal Form (3NF)
A table is in 3NF when:
- It's already in 2NF
- For each nontrivial functional dependency X → A, X is a superkey or A belongs to a candidate key. In the examples below, this removes transitive dependencies between non-key attributes.
Before 3NF (Violating)
-- BAD: department_name depends on department_id, not on id
| id | name | department_id | department_name |
|----|-------|---------------|-----------------|
| 1 | Alice | 10 | Engineering |
| 2 | Bob | 10 | Engineering |
| 3 | Carol | 20 | Marketing |After 3NF
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department_id INTEGER REFERENCES departments(id)
);
-- department_name is stored only in the departments table
-- employees references it via foreign keyA Complete Normalized Example
Here is an e-commerce schema that separates products, customers, addresses, orders, and order lines. Assuming each listed identifier determines the remaining attributes, these tables satisfy the illustrated normal forms. Stored sale prices are historical facts, not copies of the current product price.
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
category_id INTEGER REFERENCES categories(id)
);
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
full_name VARCHAR(150) NOT NULL
);
CREATE TABLE addresses (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
street VARCHAR(200) NOT NULL,
city VARCHAR(100) NOT NULL,
state VARCHAR(50),
zip VARCHAR(20) NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
shipping_address_id INTEGER REFERENCES addresses(id),
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT now()
);
CREATE TABLE order_items (
order_id INTEGER REFERENCES orders(id),
product_id INTEGER REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10, 2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);Paste this into Erd Go's editor to see the visual ERD with all relationships mapped out automatically.
When to Denormalize
While normalization is generally good practice, there are cases where denormalization improves performance:
- Read-heavy workloads — If your application reads far more than it writes, storing computed or duplicated data can avoid expensive JOINs.
- Reporting and analytics — Denormalized tables or materialized views are often used for dashboards.
- Caching hot data — Storing frequently accessed aggregated values directly in a table.
- NoSQL-style access patterns — When your queries always need a specific combination of data.
The key is to normalize first, then denormalize intentionally where performance requires it, documenting why the trade-off was made.