Normalize a Flat Order Table

The Problem

A small business has been tracking orders in a single spreadsheet-style table. Every row duplicates customer details, product details, and shipping information. The table has grown to thousands of rows and the team is running into problems: a customer's address was updated in some rows but not others, a product's price was changed but old orders now show the wrong historical price, and deleting the only order for a product loses the product information entirely.

These are classic symptoms of a denormalized schema -- update anomalies, inconsistencies, and data loss on deletion.

The Pattern: Normalization to Third Normal Form

Normalization is the process of organizing a database to reduce redundancy and prevent anomalies. The key concept is functional dependency -- if knowing column A always tells you column B, then B is functionally dependent on A.

The normal forms build on each other:

• 1NF -- No repeating groups. Each cell contains a single atomic value. Each row is unique.
• 2NF -- No partial dependencies. Every non-key column depends on the entire primary key, not just part of it.
• 3NF -- No transitive dependencies. Non-key columns depend only on the key, not on other non-key columns.

A useful mnemonic: every non-key column must provide a fact about "the key, the whole key, and nothing but the key."

This pattern applies in almost every database design scenario:

• Migrating spreadsheet data into a relational database
• Refactoring a legacy system that grew organically with duplicated columns
• Designing schemas for ERP, CRM, or inventory systems from scratch
• Breaking apart "god tables" that try to store everything in one place
• Data warehouse staging -- understanding which dimensions to extract from a flat fact table
• API response flattening -- deciding how to persist nested JSON into relational tables

Knowing when to normalize (OLTP, transactional systems) and when to intentionally denormalize (reporting, read-heavy analytics) is a fundamental design skill.

The Setup

Here is the denormalized table the business is currently using:

CREATE TABLE flat_orders (
    order_id INT,
    order_date DATE,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_city VARCHAR(50),
    customer_state VARCHAR(2),
    product_name VARCHAR(100),
    product_category VARCHAR(50),
    product_unit_price DECIMAL(10,2),
    quantity INT,
    shipping_method VARCHAR(20),
    shipping_cost DECIMAL(10,2)
);

INSERT INTO flat_orders VALUES
(1001, '2024-01-15', 'Alice Smith', 'alice@email.com', 'Portland', 'OR', 'Mechanical Keyboard', 'Electronics', 89.99, 1, 'Standard', 5.99),
(1001, '2024-01-15', 'Alice Smith', 'alice@email.com', 'Portland', 'OR', 'USB-C Cable', 'Electronics', 12.99, 2, 'Standard', 5.99),
(1002, '2024-01-18', 'Bob Jones', 'bob@email.com', 'Seattle', 'WA', 'Mechanical Keyboard', 'Electronics', 89.99, 1, 'Express', 12.99),
(1003, '2024-01-20', 'Alice Smith', 'alice@email.com', 'Portland', 'OR', 'Desk Lamp', 'Office', 45.00, 1, 'Standard', 5.99),
(1004, '2024-01-22', 'Charlie Davis', 'charlie@email.com', 'Denver', 'CO', 'USB-C Cable', 'Electronics', 12.99, 3, 'Express', 12.99),
(1004, '2024-01-22', 'Charlie Davis', 'charlie@email.com', 'Denver', 'CO', 'Desk Lamp', 'Office', 45.00, 2, 'Express', 12.99);

Notice the problems:

• Alice's name, email, city, and state are repeated across her orders
• "Mechanical Keyboard" price and category are duplicated for every order containing it
• Shipping method and cost are repeated for every line item in the same order
• If we delete order 1003, we don't lose Alice (she has other orders), but if Bob's only order is deleted, we lose his contact info entirely

The Challenge

Redesign this into a properly normalized schema (3NF). Your solution should:

1. Identify which columns belong together based on their functional dependencies
2. Create separate tables with appropriate primary keys and foreign keys
3. Eliminate all redundancy -- a customer's email should be stored exactly once, a product's category exactly once
4. Write CREATE TABLE statements with proper constraints (NOT NULL where appropriate, REFERENCES for foreign keys)
5. Write INSERT statements to populate your new tables with the same data
6. Write a SELECT query that JOINs your normalized tables to reproduce the original flat view

Try your solution here

Solution

Click here to see answer