Database Normalization: 1NF, 2NF and 3NF with Examples

Normalization is the process of organizing tables so that each fact is stored once. It reduces duplicate data and prevents the update problems that come with it. Interviewers usually ask for 1NF, 2NF and 3NF with a simple example, so this article uses one example all the way through.

Why normalize? The three anomalies

Imagine a single wide table:

Storing the same facts in many rows causes three problems:

• Insertion anomaly: you cannot add a new product until someone orders it, because every row needs an order.
• Update anomaly: if the keyboard price changes, you must change it in every row. Miss one and the data contradicts itself.
• Deletion anomaly: deleting order 2 also deletes the only record that Ben exists.

First normal form (1NF)

A table is in 1NF when:

• every column holds a single, atomic value (no lists such as "9990001, 9990002" in one cell),
• there are no repeating groups (no columns like phone1, phone2, phone3),
• each row can be uniquely identified (there is a key).

The fix for a multi-valued column is to move the values into rows (or a separate table), one value per row.

Second normal form (2NF)

A table is in 2NF when it is in 1NF and has no partial dependency: no non-key column depends on only part of a composite key.

order_items has the composite key (order_id, product_id):

product_name and unit_price depend only on product_id, which is just half of the key. Move them to their own table:

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    unit_price   DECIMAL(10, 2) NOT NULL
);

CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL REFERENCES products (product_id),
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

If you need the price at the time of purchase, keep a column in on purpose. That is a different fact from today's catalogue price.

Third normal form (3NF)

A table is in 3NF when it is in 2NF and has no transitive dependency: no non-key column depends on another non-key column.

depends on , which is not the key of this table. The chain is to to . Move the department into its own table and keep only here.

A memorable summary: every non-key column must depend on the key, the whole key, and nothing but the key.

BCNF in one paragraph

Boyce-Codd normal form is a slightly stricter 3NF: for every dependency where determines , must be a key (a candidate key or a superset of one). Most 3NF tables already satisfy it. For a fresher interview, the definition is usually enough.

Denormalization: breaking the rules on purpose

Denormalization adds redundancy deliberately to make reads faster, for example storing an order total on the order row, or flattening tables for reports. It is common in data warehouses and reporting databases (OLAP), where reads are heavy and data changes rarely. The cost is extra storage and the work of keeping the copies consistent. Transactional systems (OLTP) that change data constantly are normally kept in 3NF.

Quick answers for the interview

Practise

Take a spreadsheet you know (an invoice, a student list) and normalize it to 3NF on paper. Then check yourself with the Indexes, Design and Transactions quiz.