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

Answer from memory before opening each one.

1. The marketplace still runs on the one wide spreadsheet, one row per rental. It buys a new GPU model that nobody has rented yet, and later deletes the only rental ever made on an old model. What goes wrong in each case, and what single cause do both share with the "customer changed their email" problem?

   Show answer

   The new model has nowhere to live, because a row only exists when a rental exists (insert anomaly); deleting the old rental erases the only record that the model existed (delete anomaly). Along with the update anomaly they share one root cause: the same fact is stored in many places — or only as a passenger on another fact's row — instead of once, where it belongs. See Why the shape of data matters.

2. A colleague proposes making email the primary key of customer: "it's already unique, and it saves a column." What happens the day Ada changes her email, and how does the chapter's schema keep emails unique without that cost?

   Show answer

   A primary key should never change, because every foreign key pointing at it — every one of Ada's rows in rental — would have to change with it. The schema uses a surrogate customer_id that stays the same forever and puts a UNIQUE constraint on email, so duplicates are still refused. Natural keys are for truly stable, externally governed codes, like the tier code here. See Keys.

3. To save a join in the "charged" query, someone adds a discount column to customer, filled in from the customer's tier. Which normal form does that break, and what happens when the pro discount moves from 10% to 15%?

   Show answer

   Third normal form: discount depends on tier, a non-key column, so it depends on customer_id only transitively. The 10% is now copied onto every pro customer, and the change means updating all of them — miss one and the data disagrees with itself. With the tier table it is a one-row update. See Normalization.

4. You re-run the hands-on rental INSERT … SELECT but mistype the email as 'ada@lab.oi'. What does Postgres report, and why does the foreign key on rental.customer_id not complain?

   Show answer

   It reports INSERT 0 0 — zero rows, no error. The SELECT found no matching customer, so it produced no row to insert; a foreign key rejects rows that point at an id that doesn't exist, but it cannot notice a row that was never produced. The tempting belief is "constraints will catch my mistakes" — they catch bad rows, not missing ones, so read the row count. See the hands-on.

5. An analyst is tired of four-table joins and asks you to add customer_name, tier and gpu_model columns to the live rental table. What do you offer instead, and what decides where duplicating a fact is acceptable?

   Show answer

   Keep the source normalized and build the wide, pre-joined table downstream, in a copy that nothing writes back to. What decides it is whether the system takes live writes: where it does, a duplicated fact reintroduces the update anomaly and production data can silently disagree with itself; in a read-only analytics copy, that safety isn't needed and read speed and simple queries win. See When to normalize.

-- 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);

Takeaway

• A fact stored in more than one place produces update, insert and delete anomalies; normalization fixes the cause by storing each fact once.
• A primary key should never change, so use a surrogate id and put a UNIQUE constraint on the natural identifier, such as email.
• A non-key column that depends on another non-key column, such as a discount that follows the tier, breaks third normal form; give it its own table so a change touches one row.
• A foreign key rejects a row that points at a missing id, but it cannot notice a row that was never produced, so read the row count after an INSERT … SELECT.
• Normalize wherever the system takes live writes, and build wide, pre-joined tables downstream in a copy nothing writes back to.

A normalized source is safe to write and slow to ask questions of, so the next chapter reshapes the same data for reading. → Data Modeling II — Dimensional Modeling

Further reading

• PostgreSQL: Constraints — PostgreSQL docs, accessed 2026. Primary key, unique, foreign key and check constraints, with the exact rules for each.
• PostgreSQL: Identity columns — PostgreSQL docs, accessed 2026. How generated surrogate keys work and where GENERATED ALWAYS differs from BY DEFAULT.
• PostgreSQL tutorial: Foreign keys — PostgreSQL docs, accessed 2026. A short worked example of referential integrity, including what a violation looks like.
• The Data Warehouse Toolkit, 3rd edition — Kimball and Ross, Wiley, 2013. The standard text on the read-side model that the next chapter builds.