Normalization: Designing Tables That Do Not Fight You

You've been designing reasonably normalized tables already, splitting customers from orders instead of mixing everything together. Let's now name that principle properly and understand exactly how far to take it.

Normalization is the process of organizing tables to minimize duplicate data and avoid certain update problems. It's usually explained through "normal forms," numbered rules, but you don't need to memorize formal definitions to use it well; you need the practical instincts behind them.

One Value Per Column

The first instinct: each column should hold one single value, not a list. A customers table with a column interests = 'music, movies, hiking' breaks this. Searching for everyone interested in "movies" means ugly string matching across a comma-separated blob. The fix is a separate table:

CREATE TABLE interests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE customer_interests (
    customer_id INT NOT NULL,
    interest_id INT NOT NULL,
    PRIMARY KEY (customer_id, interest_id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (interest_id) REFERENCES interests(id)
);

This is the same many-to-many pivot pattern from the relationships lesson, applied to a new situation. Now finding everyone interested in movies is a simple, fast, properly indexed JOIN.

Avoiding Redundant Facts

The second instinct: don't store a fact that depends only on part of a combined key, separately from where it belongs. Imagine an order_items table that stores order_id, product_id, quantity, and also product_name and product_price copied in directly. The problem: product_name and product_price don't actually depend on the combination of order and product, they depend only on the product. If Tony Stark's company renames a product, you'd need to update it in every historical order_items row that ever referenced it, which is both wasteful and error-prone. The fix: keep only product_id in order_items, and look up the name and price from products via a JOIN whenever you need them.

A sharp, practical exception worth knowing: for price specifically, you often do want to store a copy, called a snapshot, directly on the order_items row, because historical orders should reflect the price at the time of purchase, not today's price. This is intentional, deliberate duplication for a real business reason, not an accident. The lesson here is subtle but important: normalization is a strong default, not a dogma. Know the rule well enough to recognize when breaking it on purpose is the actually correct choice.

Depending Only On The Key

The third instinct: avoid storing data that depends on something other than the table's own key. If a products table stores both category_id and category_name directly, and category names can change, you now have two places that must always agree, which they eventually won't. Store category_id, referencing a categories table, and look up the name through a JOIN.

When Not To Normalize

A common beginner mistake going the opposite direction: over-normalizing everything into tiny fragmented tables purely out of anxiety about duplication, even for data that never changes once written and is always read together. This causes unnecessary JOINs everywhere and real performance cost for little actual benefit. Judgment matters more than mechanically applying rules: normalize data that can change and must stay consistent; don't be afraid of a little deliberate duplication for data that's historically fixed, like a price at time of purchase.

A practical tip: whenever you're tempted to copy a value into a new table "to save a join," ask yourself: if the original value changes later, should every copy update automatically, or should old copies intentionally stay frozen in time? If the answer is "update automatically," normalize it, reference the original instead of copying. If the answer is "stay frozen," deliberate duplication, like the order price snapshot, is correct.

Try It Yourself

Hands-on check: take a single flat table idea, a bookings table with customer_name, customer_email, room_type, room_price, and booking_date all crammed together, and redesign it into properly normalized tables, deciding deliberately which values (if any) should still be snapshotted rather than referenced.