Database Normalization Explained: 1NF, 2NF, 3NF and BCNF With One Worked Example

A customer moves city. You update their address, but only on one of their ten order rows. Now your database gives two different answers to one simple question, and nobody knows which is right. Database normalization is the design discipline that prevents this kind of problem.

Definitions of normal forms can seem abstract, so we will fix one table from start to finish instead. We created the final schema in SQLite and ran the join at the end to confirm it rebuilds the original information. Column names and values are invented for this example.

What is database normalization?

Database normalization is the process of structuring a relational database to reduce duplicated data and protect its integrity. In practice, it means splitting one wide table into several focused tables, and linking them with keys. Edgar F. Codd introduced the idea in the 1970s, and the rules later developed from it are known as normal forms.

The forms are cumulative: a table in 3NF is also in 2NF and 1NF. You work through them in order, and each one removes a specific kind of redundancy.

The messy table

Imagine a small shop that keeps everything in one table:

It looks convenient, and a spreadsheet often starts just like this. But try writing a query for “how many keyboards did we sell?” You would have to parse text, and that is only the first problem.

Three anomalies that normalization prevents

Redundant data leads to three classic problems, known as anomalies:

• Update anomaly: Asha’s city is repeated on every order placed. Change it in one row but miss another, and your data contradicts itself.
• Insert anomaly: you cannot add a new product to the catalogue until someone orders it, because products exist only inside orders.
• Delete anomaly: delete Ben’s only order, and you lose the fact that Ben exists, along with the fact that you sell monitors.

Keys and dependencies in plain English

Three terms make all the rules easier to follow:

• Primary key: the column or columns that identify one row uniquely. Our guide to primary keys vs foreign keys covers this in depth.
• Functional dependency: column A determines column B if each value of A always goes with one value of B. For example, product_id determines price. It is written A → B.
• Composite key: a primary key made of more than one column, such as (order_id, product_id).

First normal form (1NF)

The rule: every column holds a single, indivisible value, there are no repeating groups, and each row can be uniquely identified.

Our products column breaks this, because it packs several items into one cell. To fix it, give every product its own row:

Now (order_id, product_id) identifies each row. Queries are possible, but the duplication is worse than before, as Asha’s details now repeat for each product. That is normal at this stage, and the next forms deal with it. Avoid the tempting shortcut of adding product1, product2 and product3 columns, since that is still a repeating group.

Second normal form (2NF)

The rule: the table is in 1NF, and every non-key column depends on the whole primary key, not just part of it.

Look at the columns against our key (order_id, product_id):

• qty depends on both. Quantity only makes sense for one product in one order.
• customer, city, pincode and order_date depend only on order_id.
• product and price depend only on product_id.

Those last two groups are partial dependencies. Move each one to its own table:

orders      (order_id PK, customer, city, pincode, order_date)
products    (product_id PK, product, price)
order_items (order_id, product_id, qty)  -- PK is both columns

A price now lives in one place, and you can add a product before anyone buys it. A table with a single-column primary key cannot have a partial dependency, so if its 1NF is sound, it is already in 2NF.

Third normal form (3NF)

The rule: the table is in 2NF, and no non-key column depends on another non-key column. Such a chain is called a transitive dependency.

Check our orders table. order_id determines the customer, the customer determines their city, and the city determines the pincode. Those facts are not really about the order, so we move them out:

orders    (order_id PK, customer_id FK, order_date)
customers (customer_id PK, name, city_id FK)
cities    (city_id PK, city, pincode)

A handy way to remember 3NF: every non-key column must depend on “the key, the whole key, and nothing but the key”.

Boyce-Codd normal form (BCNF)

BCNF is a slightly stricter version of 3NF: for every functional dependency A → B, A must be a candidate key. Most 3NF tables already satisfy it. The exception usually appears when a table has overlapping candidate keys.

For example, consider enrollments (student, course, teacher). If each teacher teaches only one course, then teacher → course, yet teacher alone is not a key. To reach BCNF, split it into teacher_courses (teacher, course) and student_teachers (student, teacher). Higher forms, 4NF and 5NF, deal with rarer multi-valued cases, and most developers seldom need them day to day.

The final schema

Here is the result as real SQL, with foreign keys linking the tables:

CREATE TABLE cities (
  city_id INT PRIMARY KEY, city TEXT, pincode TEXT
);
CREATE TABLE customers (
  customer_id INT PRIMARY KEY, name TEXT,
  city_id INT REFERENCES cities(city_id)
);
CREATE TABLE products (
  product_id INT PRIMARY KEY, product TEXT, price INT
);
CREATE TABLE orders (
  order_id INT PRIMARY KEY, order_date TEXT,
  customer_id INT REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
  order_id INT REFERENCES orders(order_id),
  product_id INT REFERENCES products(product_id),
  qty INT,
  PRIMARY KEY (order_id, product_id)
);

Nothing was lost. A join rebuilds the original view whenever you need it:

SELECT o.order_id, c.name, ci.city, p.product, oi.qty,
       oi.qty * p.price AS line_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN cities ci ON ci.city_id = c.city_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product;

-- 1 | Asha | Pune  | Keyboard | 1 | 1500
-- 1 | Asha | Pune  | Mouse    | 2 | 1000
-- 2 | Ben  | Delhi | Monitor  | 1 | 9000

If moving a customer used to mean editing many rows, it now means changing one value in one row. If those joins look unfamiliar, see SQL joins explained.

Normal forms at a glance

When to denormalize

Normalization favours correctness and clean writes, but reads may need many joins. Denormalization means deliberately adding some redundancy back for speed. It is reasonable when:

• Reporting and analytics tables are read heavily and updated in batches, as in star schemas.
• You have measured a slow query, and better indexes did not fix it.
• You need a historical snapshot rather than the current value.

That last point catches people out. Our order_items table reads the current product price, so raising a price tomorrow would change the value of old orders. Real systems store unit_price on each order line. That is not a design flaw, because the price at the time of sale is a separate fact from today’s price.

Normalize first, then denormalize only for a known reason, and plan how the copies will stay in sync. A migration is also easier to get right with good tests, so include checks for your schema in your regression testing checklist.

Final thoughts

You do not need to memorize formal definitions to design good tables. Ask of each column: “is this a fact about this table’s key, and only this key?” If not, it belongs elsewhere. Try this on the next table you create, and aim for 3NF unless you have a clear reason to do otherwise.

Frequently asked questions

Keep learning