Model an operational database that keeps data correct — entities and relationships, primary and foreign keys, and normalizing to third normal form to kill the update anomalies that corrupt data.
Data modeling starts by naming the things the business cares about (entities), how they relate, and how each row is uniquely identified (a primary key). Foreign keys wire the relationships together and let the database enforce them. Get this layer right and the schema tells the truth about the domain; get it wrong and every query fights the data.
A simple e-commerce operational model:
customers (customer_id PK, email, name)
| 1
| has many
| N
orders (order_id PK, customer_id FK -> customers, ordered_at, status)
| 1
| contains
| N
order_items (order_id FK, product_id FK, qty, unit_price) PK = (order_id, product_id)
products (product_id PK, name, price)
PK uniquely identifies a row FK references a PK elsewhere
The FK is what lets the DB REFUSE an order for a customer that doesn't exist.Normalization is the process of splitting data so every fact lives in exactly one place. The payoff is the elimination of anomalies: you never update a customer’s email in 500 order rows, you never lose a product because its last order was deleted, and you cannot store two contradictory values. Each normal form removes a specific class of redundancy.
The problem — one wide, redundant "orders" table:
order_id | customer_email | customer_name | product | price
1 | ann@x.com | Ann | Keyboard | 40
2 | ann@x.com | Ann | Mouse | 20 <- email/name repeated
Anomalies this causes:
UPDATE Ann changes email -> must update every one of her rows (miss one = wrong)
INSERT can't add a customer until they place an order
DELETE delete Ann's last order -> Ann vanishes entirely
Normalization = store each fact ONCE, reference it by key. Anomalies gone.In practice, operational (OLTP) schemas target third normal form: atomic columns (1NF), every non-key column depending on the whole key (2NF), and no column depending on another non-key column (3NF). You rarely need higher forms. Knowing the three rules lets you spot and fix a bad table on sight.
-- 3NF version of the model: each fact stored once, joined on keys
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
name TEXT NOT NULL
);
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
ordered_at TIMESTAMPTZ NOT NULL,
status TEXT NOT NULL
);
-- 1NF: atomic columns (no comma-separated lists)
-- 2NF: no column depends on PART of a composite key
-- 3NF: no non-key column depends on another non-key column
-- Ann's email now lives in ONE row. Update it once.Normalization optimizes for correct, cheap writes — the right default for operational systems. But joins cost time, and analytics reads the same data millions of times, so the warehouse deliberately denormalizes for read speed. The skill is knowing which world you are in: normalize where data changes, denormalize where it is read. That trade-off is the bridge to the rest of this course.
Two worlds, opposite goals:
OPERATIONAL (OLTP) many small writes, correctness first -> NORMALIZE (3NF)
ANALYTICAL (OLAP) huge reads, aggregate speed first -> DENORMALIZE
(star schema)
Denormalization trades some redundancy + write cost for far fewer joins
at read time. It's not "wrong" — it's the right choice for analytics.
The next lessons are how to denormalize WELL (dimensional modeling).