Normalization and schema design

The Olist schema is well-designed. Customers are in one table, orders in another, products in a third. This is normalization: splitting data to eliminate redundancy. Let's understand WHY it is designed this way and the rules behind it.

Normalization: reducing redundancy

Denormalized data is faster to read but dangerous to write. If a customer moves cities, you would need to update hundreds of rows instead of one. Normalization prevents these "update anomalies" and keeps data consistent, which is critical for transactional systems.

denormalized.sql
sql
-- What a flat denormalized table looks like:
-- order_id | customer_name | customer_city | product_name | product_category | price
-- 1        | Maria         | Sao Paulo     | Widget A     | electronics      | 99.99
-- 2        | Maria         | Sao Paulo     | Widget B     | books            | 29.99
-- 3        | Joao          | Rio            | Widget A     | electronics      | 99.99

-- Problems:
-- 1. Maria's city is stored twice (redundancy)
-- 2. Widget A's category is stored twice
-- 3. If Maria moves, you must update EVERY row
-- 4. Delete Maria's orders = lose her customer data

Denormalized data repeats information across rows, leading to update anomalies, storage waste, and data integrity risks.

Normalization solves these problems by splitting each entity into its own table. Each fact is stored exactly once, connected by foreign keys.

normalized.sql
sql
-- Normalized: each fact stored ONCE
-- customers: customer_id | name | city
-- orders: order_id | customer_id | date
-- products: product_id | name | category
-- order_items: order_id | product_id | price

-- Maria's city stored once in customers
-- Widget A's category stored once in products
-- Changes only need to update ONE row

Normalization eliminates redundancy by splitting data into related tables connected by foreign keys.

Normalize for writes (OLTP systems: e-commerce, banking). Denormalize for reads (OLAP systems: dashboards, analytics). The Olist operational database is normalized. A data warehouse built from it would be denormalized (star schema) for fast analytics queries.

Ordering exercise: Normalization steps

Loading practice…

Quiz: Quiz

Loading practice…

Fill in the blanks: Create a normalized table

Loading practice…

Code playground: Spot normalization issues

Loading practice…