SQL 78 🛢️ Database Normalization: 1NF, 2NF, and 3NF
Normalization is the process of organizing a database’s columns and tables to reduce redundancy and eliminate the anomalies that redundancy causes. The theory is older than most of the databases that use it, but the practice is as relevant as ever: a table that stores a customer’s address on every order will eventually have two orders with two different addresses for the same customer. Normalization defines a series of normal forms, each stricter than the last, that describe how a table should be structured to avoid a specific class of problem. The first three forms — 1NF, 2NF, and 3NF — are the ones that appear in practice. Most production schemas are in 3NF, and the forms beyond it are used selectively.
Key point: Normalization is about eliminating redundancy, not about making the schema pretty. Each normal form fixes a specific kind of anomaly: insertion, update, and deletion. The forms are applied in order, and each one depends on the previous. A table cannot be in 2NF unless it is already in 1NF.
Why normalization exists
The redundancy problem. Storing the same fact in multiple places means the fact can become inconsistent. If a customer’s address is stored on every order, updating the customer’s address requires updating every order, and a missed order leaves the database with two different addresses for the same customer.
The insertion anomaly. A table that combines customer and order data cannot record a customer who has not placed an order, because the primary key requires an order. The customer information is unavailable until an order exists.
The update anomaly. Changing a customer’s phone number requires updating every row where the customer appears. If one row is missed, the table contains contradictory data, and no query can determine which value is correct.
The deletion anomaly. Deleting an order may delete the only record of a customer, which loses the customer information as a side effect. The data that should have been preserved is destroyed.
The multi-value problem. A column that holds a comma-separated list of values, such as a list of phone numbers, cannot be queried efficiently. Finding all customers with a specific phone number requires string matching, which cannot use an index.
The partial-dependency problem. In a table with a composite primary key, a non-key column that depends on only part of the key is a partial dependency. The value must be repeated for every combination of the key that shares that part, which is redundant.
The transitive-dependency problem. A non-key column that depends on another non-key column is a transitive dependency. The value is determined by a column that is not the primary key, which means it is redundant with respect to the key.
a. First Normal Form (1NF)
A table is in 1NF when every column contains a single atomic value, every row is unique, and no column contains repeating groups. The rule is simple: one value per cell.
The repeating-group violation
A table that stores a list of values in a single column violates 1NF.
-- Violates 1NF
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
phones VARCHAR(200) -- "555-1234, 555-5678"
);The phones column holds multiple values. Finding a customer by phone number requires string matching, and updating one phone number requires parsing the string.
The 1NF solution
The solution is to move the multi-valued attribute into its own table, with one row per value.
-- 1NF compliant
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE customer_phones (
customer_id INT,
phone VARCHAR(20),
PRIMARY KEY (customer_id, phone),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);Each phone is its own row. The primary key is the combination of customer_id and phone, so a customer cannot have the same phone twice.
The repeating-column violation
A table with columns like phone1, phone2, phone3 also violates 1NF, because the columns form a repeating group.
-- Violates 1NF
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
phone1 VARCHAR(20),
phone2 VARCHAR(20),
phone3 VARCHAR(20)
);The table can only hold three phones. A fourth phone requires a schema change. The solution is the same: a separate table with one row per phone.
The atomic-value rule
The rule is that each cell holds a single value. A cell that holds a JSON document, an array, or a comma-separated list violates the rule, even though the database can store the value. The violation is not about the storage format; it is about whether the cell holds one fact or many.
-- Violates 1NF if the JSON holds multiple values.
CREATE TABLE orders (
id INT PRIMARY KEY,
items JSON -- [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 1}]
);The JSON column can be indexed in some databases, and it can be queried, but the values inside it are not atomic with respect to the relational model. Whether this matters depends on the use case: a document store pattern can be appropriate when the JSON is treated as an opaque document, but it is a violation of 1NF when the values inside it are queried as relational data.
The unique-row rule
A table must have a primary key, and the key must uniquely identify each row. A table without a primary key is not in 1NF, because there is no way to distinguish two identical rows.
-- Violates 1NF: no primary key
CREATE TABLE logs (
message TEXT,
level VARCHAR(10)
);Without a primary key, two identical log messages cannot be distinguished. The solution is to add a surrogate key.
CREATE TABLE logs (
id BIGSERIAL PRIMARY KEY,
message TEXT,
level VARCHAR(10)
);b. Second Normal Form (2NF)
A table is in 2NF when it is in 1NF and every non-key column depends on the entire primary key, not just part of it. The rule applies to tables with composite primary keys. A table with a single-column primary key is automatically in 2NF if it is in 1NF.
The partial-dependency violation
A table that stores order line items with a composite key of (order_id, product_id) and a product_name column has a partial dependency: product_name depends on product_id alone, not on the full key.
-- Violates 2NF
CREATE TABLE order_items (
order_id INT,
product_id INT,
product_name VARCHAR(100), -- Depends on product_id alone
quantity INT,
PRIMARY KEY (order_id, product_id)
);The product_name is repeated for every order that contains the product. If the product is renamed, every row must be updated, and a missed row leaves the database inconsistent.
The 2NF solution
The solution is to move the partially-dependent column to its own table.
-- 2NF compliant
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100)
);The product_name is stored once in the products table. The order_items table stores only the quantity, which depends on the full key.
The single-key exception
A table with a single-column primary key does not have partial dependencies, because there is no “part” of the key. The table is automatically in 2NF if it is in 1NF.
-- 2NF compliant: single-column key
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total DECIMAL(10,2)
);Every non-key column depends on id, which is the entire key. There is no partial dependency.
The candidate key
A table can have more than one candidate key. A non-key column must depend on every candidate key, not just the primary one. A table where a column depends on one candidate key but not another violates 2NF.
-- Violates 2NF
CREATE TABLE employee_projects (
employee_id INT,
project_id INT,
employee_name VARCHAR(100), -- Depends on employee_id alone
PRIMARY KEY (employee_id, project_id),
UNIQUE (project_id, employee_id)
);The employee_name depends on employee_id, which is part of both candidate keys. The violation is the same: the name is repeated for every project the employee works on.
The 2NF test
The test for 2NF is: for each non-key column, does it depend on the entire primary key? If the answer is no for any column, the table is not in 2NF.
Table: order_items
Key: (order_id, product_id)
order_id → depends on the full key (both parts)
product_id → depends on the full key (both parts)
quantity → depends on the full key (the quantity is per order-product)
product_name → depends on product_id alone → VIOLATIONc. Third Normal Form (3NF)
A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. The rule eliminates transitive dependencies: a column that is determined by a column other than the key.
The transitive-dependency violation
A table that stores employees with a department_id and a department_name has a transitive dependency: department_name depends on department_id, which is not the key.
-- Violates 3NF
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
department_name VARCHAR(100) -- Depends on department_id
);The department_name is repeated for every employee in the department. If the department is renamed, every employee row must be updated.
The 3NF solution
The solution is to move the transitively-dependent column to its own table.
-- 3NF compliant
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(100)
);The department_name is stored once in the departments table. The employees table stores only the department_id.
The dependency chain
The transitive dependency is a chain: id → department_id → department_name. The key determines department_id, and department_id determines department_name. The chain means department_name is determined by the key transitively, but the rule requires that every non-key column depend on the key directly.
The 3NF test
The test for 3NF is: for each non-key column, does it depend on a non-key column? If the answer is yes for any column, the table is not in 3NF.
Table: employees
Key: id
id → the key
name → depends on id (direct)
department_id → depends on id (direct)
department_name → depends on department_id (transitive) → VIOLATIONThe Boyce-Codd Normal Form
Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF. A table is in BCNF when every determinant is a candidate key. The difference appears in tables with multiple candidate keys where a non-key column determines part of a candidate key.
-- 3NF but not BCNF
CREATE TABLE course_instructors (
student_id INT,
course_id INT,
instructor_id INT,
PRIMARY KEY (student_id, course_id),
UNIQUE (student_id, instructor_id)
);The instructor_id determines course_id, but instructor_id is not a candidate key. The table is in 3NF because course_id is part of a candidate key, but it is not in BCNF. The BCNF form is rarely needed in practice, and the trade-off is more tables.
The denormalization trade-off
Normalization reduces redundancy but increases the number of joins. A query that needs the employee and the department name must join two tables. On a read-heavy workload, the join cost may outweigh the redundancy cost, and denormalization may be the right choice.
-- Denormalized: one table, no join.
SELECT id, name, department_name FROM employees;
-- Normalized: two tables, one join.
SELECT e.id, e.name, d.name
FROM employees e
JOIN departments d ON d.id = e.department_id;The decision depends on the workload. A reporting query that reads millions of rows benefits from the denormalized form. A transactional query that updates one row benefits from the normalized form.
Complete Example Session
-- ============================================
-- PART 1: THE UNNORMALIZED TABLE
-- ============================================
CREATE TABLE orders_flat (
order_id INT,
order_date DATE,
customer_id INT,
customer_name VARCHAR(100),
customer_email VARCHAR(100),
product_id INT,
product_name VARCHAR(100),
product_price DECIMAL(10,2),
quantity INT
);
-- Problems:
-- 1. Customer data repeated for every order line.
-- 2. Product data repeated for every order line.
-- 3. No primary key.
-- 4. Update anomalies: changing a customer email
-- requires updating every order line.
-- ============================================
-- PART 2: 1NF — ATOMIC VALUES AND PRIMARY KEY
-- ============================================
-- The table already has atomic values.
-- Add a composite primary key.
CREATE TABLE orders_1nf (
order_id INT,
order_date DATE,
customer_id INT,
customer_name VARCHAR(100),
customer_email VARCHAR(100),
product_id INT,
product_name VARCHAR(100),
product_price DECIMAL(10,2),
quantity INT,
PRIMARY KEY (order_id, product_id)
);
-- Still violates 2NF: customer_name depends on
-- customer_id, not on the full key.
-- ============================================
-- PART 3: 2NF — REMOVE PARTIAL DEPENDENCIES
-- ============================================
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
customer_id INT
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
product_price DECIMAL(10,2)
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
customer_email VARCHAR(100)
);
-- Still violates 3NF if a customer has a department
-- or a product has a category.
-- ============================================
-- PART 4: 3NF — REMOVE TRANSITIVE DEPENDENCIES
-- ============================================
CREATE TABLE categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(100)
);
ALTER TABLE products
ADD COLUMN category_id INT,
ADD FOREIGN KEY (category_id) REFERENCES categories(category_id);
-- Remove the redundant category_name from products.
-- ============================================
-- PART 5: THE COMPLETE SCHEMA
-- ============================================
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
customer_email VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(100) NOT NULL
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
product_price DECIMAL(10,2) NOT NULL,
category_id INT,
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- ============================================
-- PART 6: THE UPDATE ANOMALY
-- ============================================
-- Change a customer's email.
UPDATE customers SET customer_email = 'new@example.com'
WHERE customer_id = 42;
-- One row updated. No redundancy. No anomaly.
-- ============================================
-- PART 7: THE INSERTION ANOMALY
-- ============================================
-- Add a customer who has not placed an order.
INSERT INTO customers (customer_id, customer_name, customer_email)
VALUES (99, 'New Customer', 'new@example.com');
-- No order needed. The customer exists.
-- ============================================
-- PART 8: THE DELETION ANOMALY
-- ============================================
-- Delete an order without deleting the customer.
DELETE FROM orders WHERE order_id = 1001;
-- The customer remains. The products remain.
-- ============================================
-- PART 9: THE NORMALIZED QUERY
-- ============================================
SELECT
o.order_id,
o.order_date,
c.customer_name,
p.product_name,
cat.category_name,
oi.quantity,
p.product_price * oi.quantity AS line_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
LEFT JOIN categories cat ON cat.category_id = p.category_id;
-- The query joins five tables.
-- The data is consistent.
-- No redundancy.
-- ============================================
-- PART 10: THE DENORMALIZED VIEW
-- ============================================
CREATE VIEW orders_denormalized AS
SELECT
o.order_id,
o.order_date,
c.customer_name,
p.product_name,
p.product_price,
oi.quantity
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id;
-- The view provides the denormalized shape.
-- The storage remains normalized.
-- The best of both: consistency in storage, convenience in queries.The ten parts showed the unnormalized table, the 1NF fix, the 2NF fix, the 3NF fix, the complete schema, and the three anomalies that the normalization eliminates.
Quick Reference
The Three Normal Forms
| Form | Rule | Violation |
|---|---|---|
| 1NF | Atomic values, unique rows | Multi-valued columns |
| 2NF | No partial dependencies | Non-key column depends on part of the key |
| 3NF | No transitive dependencies | Non-key column depends on another non-key column |
Anomalies
| Anomaly | Cause | Example |
|---|---|---|
| Insertion | Combined entities | Cannot add customer without order |
| Update | Repeated data | Must update every row |
| Deletion | Combined entities | Deleting order deletes customer |
Dependency Types
| Type | Example | Violates |
|---|---|---|
| Partial | product_name → product_id | 2NF |
| Transitive | department_name → department_id | 3NF |
| Full | quantity → (order_id, product_id) | — |
Normal Form Tests
| Test | Question |
|---|---|
| 1NF | Are all values atomic? |
| 2NF | Does every non-key column depend on the full key? |
| 3NF | Does every non-key column depend only on the key? |
Denormalization Trade-off
| Aspect | Normalized | Denormalized |
|---|---|---|
| Redundancy | None | Present |
| Joins | Many | Few |
| Update cost | Low | High |
| Read cost | High | Low |
| Consistency | Guaranteed | At risk |
Best Practices
✅ Do This:
-- Store one value per cell.
CREATE TABLE customer_phones (customer_id INT, phone VARCHAR(20)); -- ✅
-- Give every table a primary key.
CREATE TABLE logs (id BIGSERIAL PRIMARY KEY, message TEXT); -- ✅
-- Move partial dependencies to their own tables.
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100)); -- ✅
-- Move transitive dependencies to their own tables.
CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(100)); -- ✅
-- Use foreign keys to enforce the relationships.
FOREIGN KEY (department_id) REFERENCES departments(id); -- ✅
-- Denormalize only when the workload requires it.
CREATE VIEW orders_denormalized AS SELECT ...; -- ✅
-- Verify with the dependency tests.
-- For each non-key column, ask what it depends on. -- ✅❌ Don’t Do This:
-- Don't store comma-separated lists.
phones VARCHAR(200) -- ❌ Violates 1NF
-- Don't use repeating columns.
phone1, phone2, phone3 -- ❌ Violates 1NF
-- Don't create a table without a primary key.
CREATE TABLE logs (message TEXT); -- ❌ Violates 1NF
-- Don't repeat non-key data for convenience.
product_name in order_items -- ❌ Violates 2NF
-- Don't store derivable data.
department_name in employees -- ❌ Violates 3NF
-- Don't denormalize prematurely.
-- Start normalized; denormalize when the workload demands it. -- ❌
-- Don't ignore the anomalies.
-- A redundant column will eventually become inconsistent. -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Multi-valued column | Convenience | Separate table |
| Repeated columns | Fixed number of values | Separate table |
| No primary key | Table used as a log | Add a surrogate key |
| Partial dependency | Composite key with derived data | Move to a separate table |
| Transitive dependency | Non-key column determines another | Move to a separate table |
| Over-normalization | Too many small tables | Denormalize selectively |
| Missing foreign keys | Relationships not enforced | Add the constraints |
Real-World Examples
1. Multi-Valued Column
-- Violation
phones VARCHAR(200)
-- Fix
CREATE TABLE customer_phones (customer_id INT, phone VARCHAR(20));2. Repeating Columns
-- Violation
phone1, phone2, phone3
-- Fix
CREATE TABLE customer_phones (customer_id INT, phone VARCHAR(20));3. Partial Dependency
-- Violation
order_items(order_id, product_id, product_name)
-- Fix
products(product_id, product_name)4. Transitive Dependency
-- Violation
employees(id, department_id, department_name)
-- Fix
departments(department_id, department_name)5. No Primary Key
-- Violation
CREATE TABLE logs (message TEXT);
-- Fix
CREATE TABLE logs (id BIGSERIAL PRIMARY KEY, message TEXT);6. Derived Column
-- Violation
order_items(quantity, unit_price, line_total)
-- Fix
-- line_total = quantity * unit_price (computed in the query)7. The Normalized Join
SELECT c.name, d.name
FROM employees e
JOIN departments d ON d.id = e.department_id;8. The Denormalized View
CREATE VIEW v AS SELECT ... FROM employees JOIN departments;9. The 3NF Test
-- For each non-key column, identify what determines it.
-- If it is not the key, it is a transitive dependency.10. The Boyce-Codd Case
-- A table with multiple candidate keys where a non-key
-- column determines part of a candidate key is in 3NF
-- but not BCNF.Visual
The Normalization Flow
┌────────────────────────────────────────────────────────────┐
│ NORMALIZATION PIPELINE │
│ │
│ UNNORMALIZED │
│ ┌──────────────────────────────────────────────────┐ │
│ │ order_items_flat: │ │
│ │ order_id, product_id, quantity, │ │
│ │ product_name, product_price, │ │
│ │ customer_name, customer_email │ │
│ └──────────────────────────────────────────────────┘ │
│ │ │
│ │ 1NF: atomic values, key │
│ v │
│ ┌──────────────────────────────────────────────────┐ │
│ │ order_items_1nf: │ │
│ │ PRIMARY KEY (order_id, product_id) │ │
│ │ Still has partial dependencies. │ │
│ └──────────────────────────────────────────────────┘ │
│ │ │
│ │ 2NF: remove partial │
│ v │
│ ┌──────────────────────────────────────────────────┐ │
│ │ order_items (order_id, product_id, quantity) │ │
│ │ products (product_id, product_name, price) │ │
│ │ customers (customer_id, name, email) │ │
│ │ Still has transitive dependencies. │ │
│ └──────────────────────────────────────────────────┘ │
│ │ │
│ │ 3NF: remove transitive │
│ v │
│ ┌──────────────────────────────────────────────────┐ │
│ │ order_items (order_id, product_id, quantity) │ │
│ │ orders (order_id, order_date, customer_id) │ │
│ │ products (product_id, name, price, category_id) │ │
│ │ customers (customer_id, name, email) │ │
│ │ categories (category_id, name) │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ Each step eliminates a specific class of anomaly. │
└────────────────────────────────────────────────────────────┘The Three Anomalies
┌────────────────────────────────────────────────────────────┐
│ ANOMALIES CAUSED BY REDUNDANCY │
│ │
│ INSERTION ANOMALY │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Cannot insert a customer without an order. │ │
│ │ The primary key requires the order_id. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ UPDATE ANOMALY │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Changing a customer's email requires updating │ │
│ │ every row where the customer appears. │ │
│ │ A missed row leaves contradictory data. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ DELETION ANOMALY │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Deleting an order deletes the customer's data │ │
│ │ as a side effect. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ Normalization eliminates all three by storing each │
│ fact in exactly one place. │
└────────────────────────────────────────────────────────────┘The Dependency Types
┌────────────────────────────────────────────────────────────┐
│ DEPENDENCY TYPES │
│ │
│ FULL DEPENDENCY │
│ ┌──────────────────────────────────────────────────┐ │
│ │ quantity → (order_id, product_id) │ │
│ │ The quantity depends on both parts of the key. │ │
│ │ Correct. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ PARTIAL DEPENDENCY │
│ ┌──────────────────────────────────────────────────┐ │
│ │ product_name → product_id │ │
│ │ Depends on part of the key. │ │
│ │ Violates 2NF. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ TRANSITIVE DEPENDENCY │
│ ┌──────────────────────────────────────────────────┐ │
│ │ department_name → department_id │ │
│ │ A non-key column determines another. │ │
│ │ Violates 3NF. │ │
│ └──────────────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────────┘The Normalized Schema
┌────────────────────────────────────────────────────────────┐
│ NORMALIZED SCHEMA │
│ │
│ customers │
│ customer_id (PK), name, email │
│ │
│ orders │
│ order_id (PK), order_date, customer_id (FK) │
│ │
│ order_items │
│ order_id (PK, FK), product_id (PK, FK), quantity │
│ │
│ products │
│ product_id (PK), name, price, category_id (FK) │
│ │
│ categories │
│ category_id (PK), name │
│ │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Each fact is stored in exactly one place. │ │
│ │ Updates touch one row. │ │
│ │ Deletes do not destroy unrelated data. │ │
│ │ Inserts do not require unrelated entities. │ │
│ └──────────────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| 1NF | Atomic values, unique rows, no repeating groups |
| 2NF | 1NF plus no partial dependencies |
| 3NF | 2NF plus no transitive dependencies |
| BCNF | Stricter than 3NF; every determinant is a candidate key |
| Partial dependency | Non-key column depends on part of a composite key |
| Transitive dependency | Non-key column depends on another non-key column |
| Insertion anomaly | Cannot insert one entity without another |
| Update anomaly | Redundant data requires multiple updates |
| Deletion anomaly | Deleting one entity destroys another |
| Denormalization | Reintroducing redundancy for read performance |
Key takeaways:
- Normalization eliminates redundancy by storing each fact in one place. A fact that is stored once cannot become inconsistent. The three normal forms each address a specific kind of redundancy.
- 1NF requires atomic values and a primary key. A column that holds a list of values, or a set of repeating columns, violates 1NF. The fix is a separate table with one row per value.
- 2NF eliminates partial dependencies. In a table with a composite key, a non-key column that depends on only part of the key is a partial dependency. The fix is to move the column to the table that corresponds to the part of the key it depends on.
- 3NF eliminates transitive dependencies. A non-key column that depends on another non-key column is a transitive dependency. The fix is to move the dependent column to its own table, keyed by the column that determines it.
- Each normal form fixes a specific anomaly. 1NF fixes the multi-value problem. 2NF fixes the partial-dependency problem. 3NF fixes the transitive-dependency problem. The anomalies are insertion, update, and deletion, and each is a symptom of redundancy.
- Denormalization is a deliberate trade-off. Normalized schemas require more joins, which cost read performance. When the read workload dominates and the redundancy is acceptable, denormalization is the right choice. The decision is driven by the workload, not by theory.
- The three normal forms are sufficient for most schemas. 3NF eliminates the anomalies that matter in practice. BCNF and the higher forms address edge cases that appear rarely. Most production schemas stop at 3NF.
- The tests are simple. For 1NF, ask whether every value is atomic. For 2NF, ask whether every non-key column depends on the full key. For 3NF, ask whether every non-key column depends only on the key. The answers determine the form.
Remember: Normalization is the discipline of storing each fact in exactly one place. The first three normal forms describe the rules: atomic values, full dependencies, and no transitive dependencies. Each form fixes a specific anomaly: the multi-value problem, the partial-dependency problem, and the transitive-dependency problem. The anomalies are not theoretical; they cause real bugs when a redundant value is updated in one place and not another. The fix is to move the redundant column to its own table and reference it by key. The cost is more joins, which matters for read-heavy workloads. Denormalization is the response when the join cost dominates, and it is a deliberate trade-off, not a return to the unnormalized form. The result is a schema where updates are local, deletes are safe, inserts are unconstrained, and the data is consistent by construction.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!