Data Modeling I — Relational & Normalization

Why the shape of data matters

Imagine someone hands you the marketplace's data as one giant spreadsheet: every row is a rental, and each row also repeats the customer's name, email, and tier, plus the GPU's model and hourly price. It looks convenient — everything's in one place. Then the questions start.

A customer changes their email. Now you have to find and update it in every rental row they ever made — miss one and your data disagrees with itself. A new customer signs up but hasn't rented yet: there's nowhere to put them, because a row only exists when a rental exists. You delete an old rental and accidentally erase the only record that a GPU model ever existed. These are the three classic anomalies — update, insert, and delete — and they all come from one root cause: the same fact is stored in many places.

A good model stores each fact exactly once, in the one place it belongs, and links facts together by reference. When you get that right, downstream work gets easy: queries are simple, joins are predictable, and metrics are trustworthy because there's a single source of truth for "what is this customer's tier?" When you get it wrong, every report becomes a fight against your own data, and no amount of clever SQL or fancy tooling downstream will fully save you. That's why modeling comes first in this course, and why it's the skill that most separates engineers who ship reliable platforms from those who ship fragile ones.

Entities & relationships

Relational modeling starts by naming the things your business cares about. Each distinct kind of thing is an entity, and it becomes a table. The facts you record about each thing are its attributes, and they become columns. The ways entities connect to each other are relationships.

For our GPU-rental marketplace, three entities fall out almost immediately:

• Customer — a person or company who rents GPUs. Attributes: name, email, signup date, tier.
• GPU — a model of card available to rent. Attributes: model name, memory, hourly price.
• Rental — one booking: a customer renting a particular GPU for a span of time. Attributes: start time, end time, hours used.

Now the relationships. A single customer can have many rentals, but each rental belongs to exactly one customer — that's a one-to-many relationship (one customer, many rentals). Likewise one GPU model can appear in many rentals. The rental entity sits in the middle, connecting a customer to a GPU. In fact, customers and GPUs have a many-to-many relationship with each other (any customer can rent any GPU, repeatedly), and rental is the junction that resolves it into two one-to-many links.

We draw this as an entity-relationship diagram (ERD). The notation below uses "crow's foot" marks: a single bar (──) means "one," and the splayed foot (< / >) means "many." Read each line as "one customer has many rentals."

Keys: primary, foreign, surrogate

A model is only as solid as its keys. A primary key (PK) is the column (or columns) that uniquely identifies each row in a table — no two rows may share it, and it can never be null. It's how you point at exactly one customer or exactly one rental. A foreign key (FK) is a column in one table that holds the primary-key value of a row in another table; it's the wire that links tables together. In the ERD above, rental.customer_id is a foreign key referencing customer.customer_id. The database can enforce that link, refusing to insert a rental for a customer that doesn't exist.

When choosing a primary key you face a fork: use a natural key (a real-world attribute that's already unique, like an email address or a GPU model name) or a surrogate key (a meaningless, system-generated id like an auto-incrementing integer). Both work; here's how they compare.

Normalization: 1NF → 3NF

Normalization is a step-by-step process for organizing columns into tables so that each fact lives in exactly one place. Each "normal form" removes a specific kind of redundancy. You don't need to memorize the formal definitions — you need the intuition, which is captured by a classic line: every non-key column should depend on the key, the whole key, and nothing but the key.

Let's normalize a real mess. Here's the denormalized "rentals" spreadsheet from the intro — one wide table where everything is jammed together:

This table has every anomaly. Watch each normal form clean up one problem:

• 1NF — atomic values, no repeating groups. The gpus_rented cell holds a list (A100; H100). That's not atomic — you can't filter or join on it cleanly. First normal form says each cell holds one value and there are no repeating groups. Fix: split the list so each rented GPU is its own row.
• 2NF — no partial dependencies on a composite key. Second normal form matters when your key is made of multiple columns. It says every non-key column must depend on the whole key, not just part of it. If the key were (customer_email, gpu), then customer_name depends only on customer_email — half the key — which is a partial dependency. Fix: move customer attributes into their own table keyed by customer.
• 3NF — no transitive dependencies. Third normal form says non-key columns must depend on the key directly, not through another non-key column. Here tier_discount depends on tier, and tier depends on the customer — so tier_discount only depends on the key transitively, through tier. That's why the 10% is copied on every Ada row. Fix: pull tier → discount into its own little table.

After normalizing to 3NF, the one messy table becomes four clean ones, each owning a single kind of fact:

Now Ada's discount is stored once in tier; her email is stored once in customer; a new customer can be inserted with zero rentals; and deleting a rental can't erase a GPU. Every anomaly is gone — not by being careful, but by structure.

When to normalize (and when not to)

Here's the nuance that trips people up: normalization is the right default for source systems, but the wrong default for analytics. The two have opposite priorities.

A normalized model is fantastic for the marketplace's live database: when a customer changes their email, you update one row, and there's never any disagreement. But analytics asks questions like "revenue by customer tier per month," which would force a chain of joins across four tables, run over and over. For that, you deliberately denormalize — pre-joining and duplicating data into wide, read-friendly tables — trading the write-time safety you don't need for the read-time speed you do.

Hands-on: build the normalized schema

Time to make it real. Open your Postgres prompt (docker compose exec postgres psql -U postgres) and type this DDL. Notice how the design choices above show up in syntax: GENERATED ALWAYS AS IDENTITY gives us surrogate keys, PRIMARY KEY and REFERENCES wire up the keys, and UNIQUE guards the natural attribute (email) without making it the PK.

-- A clean schema to work in, so we don't collide with Course 2's tables
CREATE SCHEMA IF NOT EXISTS marketplace;
SET search_path TO marketplace;

-- tier: the lookup table that kills the transitive dependency (3NF)
CREATE TABLE tier (
    tier        TEXT PRIMARY KEY,          -- natural key: a short, stable code
    discount    NUMERIC(4,2) NOT NULL      -- e.g. 0.10 = 10% off
);

-- customer: surrogate PK; email is unique but NOT the key
CREATE TABLE customer (
    customer_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email        TEXT NOT NULL UNIQUE,     -- natural attribute, enforced unique
    name         TEXT NOT NULL,
    tier         TEXT NOT NULL REFERENCES tier(tier),   -- FK → tier
    signup_date  DATE NOT NULL DEFAULT CURRENT_DATE
);

-- gpu: one row per rentable model
CREATE TABLE gpu (
    gpu_id        INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    model         TEXT NOT NULL UNIQUE,
    memory_gb     INT NOT NULL,
    hourly_price  NUMERIC(8,2) NOT NULL
);

-- rental: the junction, with two FKs and facts about the booking itself
CREATE TABLE rental (
    rental_id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES customer(customer_id),  -- FK → customer
    gpu_id       INT NOT NULL REFERENCES gpu(gpu_id),            -- FK → gpu
    started_at   TIMESTAMPTZ NOT NULL,
    ended_at     TIMESTAMPTZ,
    hours        NUMERIC(6,2)
);

Now seed a few rows. Because the keys are enforced, the database will reject any rental that points at a customer or GPU that doesn't exist — a guarantee a spreadsheet can never give you.

INSERT INTO tier (tier, discount) VALUES
    ('free', 0.00),
    ('pro',  0.10);

INSERT INTO customer (email, name, tier) VALUES
    ('ada@lab.io',  'Ada',  'pro'),
    ('kade@io.dev', 'Kade', 'free');

INSERT INTO gpu (model, memory_gb, hourly_price) VALUES
    ('T4',   16, 0.35),
    ('A100', 40, 2.10),
    ('H100', 80, 4.50);

-- A rental, joining a customer to a GPU. We look up the surrogate ids by
-- their natural attributes so we don't have to hard-code 1, 2, 3.
INSERT INTO rental (customer_id, gpu_id, started_at, ended_at, hours)
SELECT c.customer_id, g.gpu_id,
       '2026-06-20 09:00+00', '2026-06-20 12:00+00', 3.0
FROM customer c, gpu g
WHERE c.email = 'ada@lab.io' AND g.model = 'A100';

-- Read it back with the joins the model makes easy:
SELECT c.name, c.tier, t.discount, g.model, r.hours,
       g.hourly_price * r.hours * (1 - t.discount) AS charged
FROM rental r
JOIN customer c ON c.customer_id = r.customer_id
JOIN tier     t ON t.tier        = c.tier
JOIN gpu      g ON g.gpu_id      = r.gpu_id;

✓ Check yourself

• Can you name the three anomalies (update, insert, delete) and explain what causes all of them?
• Can you explain why a surrogate key is usually preferred over a natural key as a primary key?
• Given a wide table, can you spot a column that depends on a non-key column (a 3NF violation)?
• Can you say why you normalize a source system but denormalize for analytics?

-- vendor now owns the vendor→country fact, stored once
CREATE TABLE vendor (
    vendor          TEXT PRIMARY KEY,
    vendor_country  TEXT NOT NULL
);

-- gpu keeps only facts that depend on gpu_id, plus an FK to vendor
CREATE TABLE gpu_v2 (
    gpu_id        INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    model         TEXT NOT NULL UNIQUE,
    vendor        TEXT NOT NULL REFERENCES vendor(vendor),  -- FK, not a copy
    hourly_price  NUMERIC(8,2) NOT NULL
);

INSERT INTO vendor VALUES ('NVIDIA','USA'), ('AMD','USA');
INSERT INTO gpu_v2 (model, vendor, hourly_price) VALUES
    ('T4','NVIDIA',0.35), ('A100','NVIDIA',2.10), ('MI300','AMD',3.20);

Next

You've built a normalized source that's correct and anomaly-free — perfect for live writes, painful for analytics. Next we flip the priorities and reshape this same data for fast reads. → Data Modeling II — Dimensional Modeling