Normalization

Normalization is a set of rules for deciding which columns belong in which table, so that every fact is stored in exactly one place. It sounds academic until you inherit a spreadsheet-shaped table and watch the same customer's city drift into three spellings.

The goal is practical: remove redundancy, so that the data cannot contradict itself.

Start with a bad table#

A first-draft design, the kind someone builds straight from a printed invoice:

order_lines
 order_id | order_date | customer_email  | customer_city | product_id | product_name  | category    | qty | unit_price
----------+------------+-----------------+---------------+------------+---------------+-------------+-----+-----------
    1     | 2024-02-01 | aisha@example   | Mumbai        |     1      | Keyboard      | Accessories |  1  |  89.00
    1     | 2024-02-01 | aisha@example   | Mumbai        |     2      | Mouse         | Accessories |  2  |  29.50
    2     | 2024-03-15 | aisha@example   | Mumbai        |     4      | Monitor       | Displays    |  1  | 289.00
    2     | 2024-03-15 | aisha@example   | Mumbai        |     3      | USB-C Hub     | Accessories |  1  |  45.00

The primary key is (order_id, product_id). Everything works until you try to change something.

Three anomalies#

Update anomaly. Aisha moves from Mumbai to Pune. You must update every row she appears in. Miss one and the database now says she lives in two cities.

Insert anomaly. You want to add a new product to the catalog before anyone orders it. There is no row to put it in, since every row needs an order_id.

Delete anomaly. Cancel the only order containing the HD Webcam and delete its rows. The product itself, its name and its category, vanishes from the database.

All three come from one cause: facts about different things (customers, products, orders) are mixed in one table. Normal forms are the steps to separate them.

First normal form (1NF)#

Every column holds a single atomic value, there are no repeating groups, and the table has a primary key.

A common violation:

 order_id | products
----------+------------------------
    1     | Keyboard, Mouse

You cannot query "orders containing a Mouse" without string-hunting, and you cannot constrain the contents. The fix is one row per order-product pair, which is the shape of the table above. That table is already in 1NF.

Second normal form (2NF)#

The table is in 1NF, and every non-key column depends on the whole key, not part of it. This only matters when the key is composite.

The key is (order_id, product_id). Check each column:

Partial dependencies are the source of the redundancy. The order date is repeated for every line. The product name is repeated for every order that contains it.

Split along those lines:

orders            order_id (PK) | order_date | customer_email | customer_city
products          product_id (PK) | product_name | category
order_items       order_id, product_id (PK) | qty | unit_price

Third normal form (3NF)#

The table is in 2NF, and no non-key column depends on another non-key column. In other words, no transitive dependencies.

In orders, customer_city is determined by customer_email, and the email is determined by order_id. So order_id → customer_email → customer_city. The city is stored per order, repeated each time that customer orders.

Pull the customer out:

customers   customer_id (PK) | email | city
orders      order_id (PK) | customer_id (FK) | order_date
products    product_id (PK) | name | category
order_items order_id, product_id (PK) | quantity | unit_price

That is the course schema. A useful way to remember 2NF and 3NF together: every non-key column should depend on the key, the whole key, and nothing but the key.

Now check the three anomalies. Aisha's city lives in one row, so an update touches one row. Products exist independent of orders, so inserts and deletes no longer interact. The anomalies are gone.

Boyce-Codd normal form (BCNF)#

BCNF tightens 3NF. Every determinant, meaning every column set that determines another, must be a candidate key. It differs from 3NF only in unusual situations, usually overlapping candidate keys.

The classic example. A university records which instructor teaches each student in each course:

enrollment   student | course | instructor
              Ana     | SQL    | Dr. Rao
              Ben     | SQL    | Dr. Rao
              Ana     | Stats  | Dr. Lee

Rules of this school: a student takes a course with one instructor, and each instructor teaches only one course. So instructor → course, but instructor alone is not a key of this table. That dependency violates BCNF. It also invites an anomaly: if Dr. Rao switches to teaching Python, you must change every row, or the data says Dr. Rao teaches two courses.

Decompose it:

instructor_course   instructor (PK) | course
student_instructor  student | instructor        (PK: both)

The cost is that the original rule "a student has one instructor per course" is no longer enforced by a single constraint. BCNF can occasionally lose a dependency, and in practice many designs stop at 3NF. Know both.

Higher forms exist. 4NF deals with independent multi-valued facts, 5NF with join dependencies. You will rarely meet them by name, though a table with two unrelated lists of values per row is the usual 4NF symptom.

What the rules give you#

When to denormalize#

Normalized schemas read through joins. For almost every application, joins on indexed keys are fast and the integrity is worth far more than the speed. Denormalize only with evidence.

Legitimate reasons:

• A measured read bottleneck on an expensive aggregate. Precompute it, ideally in a materialized view, so the base data stays normalized.
• Reporting and warehouse schemas, where wide, flat tables are scanned in bulk and rarely updated.
• Capturing history. order_items.unit_price copies products.unit_price, and that is correct. The price at the time of purchase is a different fact from today's price.
• Counters, such as a comment_count column, when counting live is too slow. Update it in the same transaction as the change.

What denormalization costs: every duplicated fact becomes a place for contradiction, and you now own the job of keeping the copies in sync. If you cannot name the mechanism that keeps them consistent, do not duplicate the data.

Common mistakes#

Normalizing everything to the extreme. Splitting a one-line address into ten tables for purity makes queries painful and buys nothing.

Confusing history with redundancy. A snapshot of a price, name or address at transaction time is not a duplicate. It is a record of what happened.

Denormalizing "for speed" without measuring. An index usually fixes the slowness at a fraction of the cost.

Stopping at 1NF. A table with atomic values and a key can still be riddled with update anomalies.

Applying the rules without knowing the functional dependencies. The normal forms are just consequences of facts about your data. If you do not know whether instructor determines course, no technique can tell you.

Practice#

1. Take this table and decide its key: employee_id, employee_name, department_id, department_name, project_id, project_name, hours. Identify every partial and transitive dependency, then decompose it to 3NF. Write the final tables.
2. A contacts table has a column phones holding comma-separated numbers. Which normal form does it violate, and what is the fix?
3. For each anomaly (insert, update, delete), describe a concrete scenario in the order_lines table above that triggers it.
4. Look at order_items.unit_price. Argue for why this is not a 3NF violation even though it equals products.unit_price at insert time.
5. Name one case in the course schema where adding a stored order_total column would be reasonable, and describe how you would keep it correct.