Normalization | Learn IT Fundamentals with Teacher Pochi

Key points

Normalization is a design approach that splits tables into appropriate units to prevent duplicate and inconsistent data.
Cramming too much information into a single table makes missed updates and inconsistencies more likely.
In a normalized design, each piece of information is basically held in exactly one place.

What is normalization?

Normalization is a design technique for organizing a database's tables so that the same information isn't repeated over and over. The goal is to reduce data duplication and avoid inconsistencies whenever data is updated or deleted.

Teacher Pochi's hintPicture keeping the class roster and each student's personal details on separate sheets — a "class roster (list of students)" and a "student register (addresses, contact details, and other specifics)" — instead of cramming them onto a single page.

Problems before normalization

For example, if you cram a customer's name and address directly into the "orders" table, the following problems arise.

Duplicate dataEvery time the same customer places another order, their name and address get recorded all over again.

Missed updatesWhen a customer moves, you have to fix the address on every single order row, or the data goes out of sync.

Inconsistencies ariseIf even one row's update is missed, the same customer ends up with conflicting addresses.

These problems arise from cramming too many unrelated kinds of information into a single table.

The idea behind normalization: splitting tables apart

Normalization splits data of different kinds — like "information about the customer" and "information about the order" — into separate tables.

Before:
one giant table

Split by
role

customers
table

orders
table

Customer information is recorded exactly once in the customers table, and the orders table references it via a foreign key (customer_id). This means an address change only requires updating a single place in the customers table.

A concrete example: splitting up order data

Before normalization, every order recorded the customer's name, address, and product name all together in a single row. After normalization, the data is split into tables by role, as follows.

customersCustomer name and address

ordersOrder date and customer ID

productsProduct name and price

Because each table is linked by foreign keys, you can join the tables back together whenever you need the original combined information.

Teacher Pochi's hintThis is the same idea as a library keeping separate ledgers for a "patron register," a "book catalog," and "checkout records." You can find out who borrowed what by cross-referencing the ledgers using their reference numbers.

Benefits and cautions of normalization

Normalization offers many benefits, but taking it too far introduces its own challenges. Striking a balance matters.

BenefitLess duplication, and fewer missed updates or inconsistencies.

CautionThe more tables you have, the more joining across tables you need to do to retrieve data.

In practice, teams generally normalize by default, but sometimes deliberately duplicate some data anyway — a choice called "denormalization" — when search speed is the priority.

The stages of normalization: 1st through 3rd normal form

Normalization happens in stages, tidying things up step by step. What's been covered so far mainly corresponds to the following three stages.

1st normal formDon't cram multiple values into a single column; eliminate repeating groups.

2nd normal formSplit out columns that depend on only part of the primary key (partial functional dependency) into their own table.

3rd normal formSplit out columns that depend on a non-key column (transitive functional dependency) into yet another table.

In practice, it's more useful to develop a feel for "is the same information scattered across multiple places?" than to memorize these definitions precisely.

Summary

Normalization is a design approach that splits data of different kinds into tables at an appropriate level of granularity, preventing duplication, missed updates, and inconsistencies. It's a foundational perspective in table design.