Lesson 9.6 · Reading time: ~10 min

Database normalization is a way to design tables so that every fact is stored in exactly one place. A normalized database splits data into related tables linked by keys, which removes duplicates and the errors they cause. In this lesson you'll learn the first three normal forms (1NF, 2NF, 3NF) and BCNF, see how to split a wide table with SQL, and find out when duplicating data on purpose is the better choice.

Database Normalization: 1NF, 2NF and 3NF

In the previous lessons of this module you created tables, changed them and built views on top of them. The remaining question is which tables to create in the first place: what goes into one table and what deserves its own.

Normalization answers that question with a few formal rules called normal forms. Sakila is a well-normalized database, so its tables serve as examples throughout the lesson.

What Is Database Normalization?

Normalization is the process of organizing columns and tables so that:

• each table describes one kind of entity: a customer, a film, a rental;
• each fact is stored once, and other tables refer to it by key;
• every non-key column depends on the key of its table, and only on it.

The rules are grouped into normal forms: 1NF, 2NF, 3NF, BCNF and higher. Each form includes the previous one. In practice, a schema in third normal form (3NF) is considered normalized; the higher forms deal with rare cases.

Why Normalize: Data Anomalies

Imagine that rentals are stored in one wide table:

The table is easy to read, but the customer and film data are repeated in every rental. This causes three kinds of problems, called anomalies:

• Update anomaly. Mary moves to another city. You must update every one of her rentals; if one row is missed, the database holds two different cities for the same customer.
• Insert anomaly. A new film can't be added until someone rents it, because every row is a rental.
• Delete anomaly. Deleting Patricia's only rental also deletes everything known about Patricia.

Normalization removes these anomalies by moving each fact to the table where it belongs.

First Normal Form (1NF)

A table is in first normal form when:

• every column holds a single (atomic) value, not a list;
• there are no repeating groups of columns such as phone1, phone2, phone3;
• each row can be identified by a primary key.

The film_actors column above breaks 1NF: it stores a list of names in one value. Such a column is hard to query. To find films with Penelope Guiness, you search inside the text with LIKE, which can't use an index and matches similar names by mistake. Counting films per actor is even harder.

Sakila has a real example of the same trade-off. In MySQL, the film.special_features column has the SET type and stores values like Trailers,Commentaries:

SELECT special_features, COUNT(*) AS films
FROM film
GROUP BY special_features
ORDER BY films DESC
LIMIT 3;

Result: 15 combinations of four features, starting with Trailers,Commentaries,Behind the Scenes (79 films). To count films per single feature, you have to split these strings.

The 1NF solution is a separate table with one row per value. Sakila does exactly this for actors: the film_actor table stores one pair (actor_id, film_id) per row.

SELECT a.first_name, a.last_name, COUNT(*) AS films
FROM film_actor AS fa
JOIN actor AS a ON a.actor_id = fa.actor_id
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY films DESC
LIMIT 3;

Result: the actors with the most films; GINA DEGENERES leads with 42. With a list in a text column, this query would be impractical.

Second Normal Form (2NF)

A table is in second normal form when it is in 1NF and every non-key column depends on the whole primary key, not on part of it.

2NF matters only for tables with a composite key of several columns. Suppose film_actor also stored names:

The key is (actor_id, film_id), but actor_name depends only on actor_id and film_title only on film_id. This is a partial dependency: the actor's name is repeated for each of their films.

The fix is to move each column to the table whose key it depends on: names to actor, titles to film. The real film_actor keeps only the two keys and last_update.

Third Normal Form (3NF)

A table is in third normal form when it is in 2NF and no non-key column depends on another non-key column. Such a chain, key → column A → column B, is called a transitive dependency.

For example, if the address table stored the country:

city and country depend on city_id, not on address_id. Every address in the same city repeats the city name and country, and renaming a country means updating thousands of addresses.

Sakila solves this with a chain of tables: address stores city_id, city stores country_id, and country stores the name. Each name is stored once, and a query collects them with JOIN:

SELECT co.country, COUNT(*) AS customers
FROM customer AS c
JOIN address AS a ON a.address_id = c.address_id
JOIN city AS ci ON ci.city_id = a.city_id
JOIN country AS co ON co.country_id = ci.country_id
GROUP BY co.country
ORDER BY customers DESC
LIMIT 5;

Result: India (60), China (53), United States (36), Japan (31) and Mexico (30). Normalized data is read with joins; this is the price for storing each fact once.

A short way to remember 1NF–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 stricter version of 3NF: every column that determines another column must be a candidate key. The difference shows up only in tables with several overlapping candidate keys, which are rare. Most 3NF tables are already in BCNF. The fourth and fifth normal forms deal with multi-valued dependencies and are rarely discussed outside database theory.

How to Normalize a Table with SQL

Let's split the wide table from the beginning, stored as rental_flat, into normalized tables. Run these statements in your own database: the sample databases on SQLtest are read-only. First, the new structure:

CREATE TABLE customer_n (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(90) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE film_n (
    film_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE rental_n (
    rental_id INT PRIMARY KEY,
    rental_date DATE NOT NULL,
    customer_id INT NOT NULL,
    film_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customer_n (customer_id),
    FOREIGN KEY (film_id) REFERENCES film_n (film_id)
);

Note: this is MySQL syntax. In PostgreSQL, replace INT AUTO_INCREMENT with INT GENERATED ALWAYS AS IDENTITY.

Then copy the data. SELECT DISTINCT leaves one row per customer and per film, and the rentals find their keys through JOIN:

INSERT INTO customer_n (email, name, city)
SELECT DISTINCT customer_email, customer_name, customer_city
FROM rental_flat;

INSERT INTO film_n (title)
SELECT DISTINCT film_title
FROM rental_flat;

INSERT INTO rental_n (rental_id, rental_date, customer_id, film_id)
SELECT r.rental_id, r.rental_date, c.customer_id, f.film_id
FROM rental_flat AS r
JOIN customer_n AS c ON c.email = r.customer_email
JOIN film_n AS f ON f.title = r.film_title;

Result: 2 customers, 2 films and 3 rentals. Mary's city is now stored in one row, so a single UPDATE changes it everywhere.

If SELECT DISTINCT returns two rows for the same email, the data is already inconsistent: the same customer has different names or cities in different rentals. The UNIQUE constraint on email will reject the second row, and you'll have to decide which value is correct. Normalization often reveals such problems.

The actor list goes into its own pair of tables, actor and film_actor, in the same way. The city would follow the 3NF example: a separate city table and a city_id column in customer_n.

Denormalization: When Duplicates Are Acceptable

Denormalization is deliberately storing redundant data to make reading faster or simpler. It is a conscious trade-off, not a design mistake, and Sakila itself contains a few examples:

• payment stores customer_id, although it can be found through rental_id. The duplicate saves a join in payment reports; the two values match in all 16,044 payments linked to a rental.
• The film_text table repeats title and description from film for full-text search.
• The film_list view returns actors as one comma-separated string. A view doesn't store anything, so it gives a convenient wide format without the anomalies.

Typical reasons to denormalize:

• reports and dashboards that join many large tables on every request;
• counters and totals, such as a customer's total payments, that are expensive to recalculate;
• analytical warehouses with star schemas, where data is loaded in batches and rarely updated.

The rule of thumb: design normalized first, then denormalize specific places where you've measured a performance problem. Every duplicate needs a way to stay in sync: a trigger, a scheduled job or application code.

Key Takeaways:

• Normalization stores each fact once and links tables by keys; it prevents update, insert and delete anomalies.
• 1NF: one value per column, no lists or repeating column groups, a primary key in every table.
• 2NF: no column depends on part of a composite key.
• 3NF: no column depends on another non-key column; "the key, the whole key, and nothing but the key".
• Normalized data is read back with JOIN; a view can present it in a wide format.
• Denormalize deliberately and only where reads need it, with a plan to keep the copies in sync.

This lesson completes the module on creating and changing tables. The next module covers writing SQL that is readable and fast: best practices, query optimization and indexes.

Frequently Asked Questions

What is normalization in a database?

Normalization is the process of organizing data into related tables so that each fact is stored only once. It removes duplicated data and the anomalies it causes, such as one customer having two different addresses in different rows.

What is the difference between 1NF, 2NF and 3NF?

1NF requires atomic values and a primary key. 2NF additionally forbids columns that depend on only part of a composite key. 3NF additionally forbids columns that depend on other non-key columns, such as a country name stored next to a city ID.

Is a normalized database always better?

For data that changes, usually yes: it keeps the data consistent and the schema flexible. For reporting and analytics, controlled denormalization can make reads much faster. Start normalized and denormalize where you've measured a need.

Does normalization make queries slower?

Reads need more joins, which costs some time, but with indexes on the key columns joins are fast. Writes are often faster, because each change updates one row instead of many copies.

Interview Questions

Explain update, insert and delete anomalies.

An update anomaly is when one fact is stored in several rows and an update misses some of them. An insert anomaly is when you can't add a fact without unrelated data, such as a film without a rental. A delete anomaly is when deleting one fact also deletes another, such as a customer's only rental removing the customer.

What is a transitive dependency? Give an example.

It is when a non-key column depends on another non-key column instead of the key. In address(address_id, city_id, city_name), city_name depends on city_id, not on address_id. The fix is a separate city table, which brings the schema to 3NF.

When would you denormalize a schema?

When reads are much more frequent than writes and joins have become a measured bottleneck, for example in reports, dashboards and data warehouses. Store the redundant data with a clear way to keep it in sync, such as a trigger or a scheduled refresh.