Data Modeling Explained: From One Messy Table to a Real Schema

Prefer to watch? ▶ Data Modeling Explained in 90 seconds ✈ Telegram

Your schema is the contract every pipeline, query, and dashboard depends on. Get the modeling wrong, and you'll spend 3 a.m. fixing silent duplicates and mismatched joins. Get it right, and the whole stack hums.

Mental model: Data modeling is deciding which real-world nouns become tables, which facts live where, and how they reference each other—so you have one source of truth and every query knows where to look.

The Problem: One Fat Table

Imagine you're building the backend for BeanBox, a coffee store that takes online orders. You start simple: one orders table. Every row is an order.

order_id | customer_name | customer_email | product_name | product_price | order_date
1        | Ada           | ada@b.co       | Espresso     | 3.50          | 2024-01-15
2        | Ada           | ada@b.co       | Latte        | 4.50          | 2024-01-16
3        | Bob           | bob@b.co       | Espresso     | 3.50          | 2024-01-16
4        | Ada           | ada@b.co       | Cappuccino   | 4.75          | 2024-01-17

This works until Ada changes her email to ada.new@b.co. Now you have two choices: update all four of her rows, or leave the old ones and live with inconsistency. Miss one row and you have two emails for one person. Your aggregations break. Your dashboard and your pipeline disagree on how many unique customers you have.

This is called an update anomaly—a sign that data is scattered where it should be singular.

Layer One: The Conceptual Model

Start by naming the real-world things your system cares about:

Customer — a person who buys coffee
Order — a purchase event
Product — something we sell

Draw lines between them:

A Customer places many Orders (one-to-many)
An Order contains one or more Products (many-to-many)
A Product appears in many Orders

This is the conceptual model: just nouns and relationships. No keys yet, no databases. Just: "What are the real things, and how do they connect?"

Layer Two: The Logical Model

Now turn those nouns into tables and add structure.

Customer becomes a table with:

customer_id (primary key — the unique identifier for this row)
name
email

Product becomes a table with:

product_id (primary key)
name
price

Order becomes a table with:

order_id (primary key)
customer_id (foreign key — references the Customer table)
order_date

OrderItem (or OrderProduct) handles the many-to-many:

order_item_id (primary key)
order_id (foreign key)
product_id (foreign key)
quantity

Now an order does not copy the customer's email. It just points to the customer with customer_id. If Ada changes her email, you update the customers table once, and every order automatically references the new email.

customers:
id | name | email
1  | Ada  | ada.new@b.co
2  | Bob  | bob@b.co

products:
id | name       | price
1  | Espresso   | 3.50
2  | Latte      | 4.50
3  | Cappuccino | 4.75

orders:
id | customer_id | order_date
1  | 1           | 2024-01-15
2  | 1           | 2024-01-16
3  | 2           | 2024-01-16
4  | 1           | 2024-01-17

order_items:
id | order_id | product_id | quantity
1  | 1        | 1          | 1
2  | 2        | 2          | 1
3  | 3        | 1          | 1
4  | 4        | 3          | 1

This is normalization: every fact lives in exactly one place.

The Three Relationships

Relationship	Pattern	Implementation	Example
One-to-Many	One A owns many Bs	B has a foreign key to A	One Customer, many Orders. `orders.customer_id` → `customers.id`
One-to-One	One A is exactly one B	Either table has a foreign key to the other; often separate for security or audit	One User, one EncryptedPassword. `passwords.user_id` → `users.id`
Many-to-Many	Many As relate to many Bs	Join table with two foreign keys	Many Orders, many Products. `order_items.order_id` → `orders.id`, `order_items.product_id` → `products.id`

Normalization: One Source of Truth

Normalization is a set of rules (Normal Forms: 1NF, 2NF, 3NF, BCNF, and beyond) that ensure:

Every fact lives in exactly one place — no redundant copies
Updates are atomic — change an email once, it's updated everywhere
No insertion anomalies — you can add a new customer without creating a fake order
No deletion anomalies — you can delete an order without losing customer information

For BeanBox, your normalized schema means:

Change Ada's email one time in the customers table
Every query that joins orders to customers sees the new email
No silent mismatches, no data rot

Layer Three: The Physical Model

Once you've normalized, the database gets real:

You pick data types: customer_id is a BIGINT, email is a VARCHAR(255), price is a DECIMAL(10,2)
You add indexes: put an index on orders.customer_id so joins are fast
You decide on denormalization on purpose

When to Denormalize

Normalization is for correctness: one source of truth, no anomalies, correct writes. But analytics has different demands: you want wide, flat tables so queries avoid expensive joins.

This is denormalization, and it's not cheating—it's deliberate and separate.

-- Normalized: correct, but six joins
SELECT
  c.name,
  COUNT(o.id) AS order_count,
  SUM(oi.quantity * p.price) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
GROUP BY c.id, c.name;

-- Denormalized: wide, one table, fast read
SELECT customer_name, order_count, revenue
FROM customer_revenue_summary;

For an operational database (OLTP), normalize. For a data warehouse or analytics layer (OLAP), denormalize into a star schema or wide fact tables. Build the wide table from the normalized source on a schedule—Airflow, dbt, whatever.

-- dbt: build the wide table from normalized sources
SELECT
  c.id AS customer_id,
  c.name AS customer_name,
  c.email,
  o.id AS order_id,
  o.order_date,
  p.name AS product_name,
  p.price,
  oi.quantity,
  (p.price * oi.quantity) AS line_total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id

Then your dashboard query is a single table scan. Fast, explicit, and the denormalization is version-controlled.

Why the Schema Matters for Data Engineering

Your schema is a contract. It says:

"Here are the tables and their columns"
"Here are the primary and foreign keys"
"Here are the data types"
"Here is the one source of truth for each fact"

Every pipeline, query, and dashboard bets on that contract. When you change it without planning:

A pipeline that expected orders.customer_id breaks if you rename it
A dashboard that counts distinct customers breaks if you suddenly allow NULL in customer_id
Two teams build different logic to join orders to customers because the relationship was never documented

A clean schema prevents silent bugs. It's the difference between 3 a.m. on-call and sleeping through the night.

For LLM-Serving Systems

If you're building a system where an LLM needs to query a database (e.g., a retrieval-augmented generation pipeline), data modeling becomes a latency surface:

TTFT (time to first token) depends on query latency to fetch context. A poorly normalized schema with N+1 join problems will serialize your token generation.
TPOT (time per output token) stays constant, but the prefill depends on context window size, which depends on how much data you can fetch in time.
Denormalization (wide tables, materialized views, embedding caches) reduces query latency and gets you context faster, letting the LLM start generating tokens sooner.

Your schema design directly affects how fast a user sees a response from an LLM-powered application.

Verdict

Normalize for correct writes and one source of truth. Use normalized schemas in operational databases (OLTP) and data sources of record. Denormalize deliberately for analytics. Build wide, flat tables in your warehouse using dbt or similar tools; denormalization is a choice, not a side effect.

If you're unsure whether a fact should be split across tables, ask: "If this value changes, how many places would I have to update it?" If the answer is more than one, normalize it.

Watch the 90-second reel to see this unfold on one live example.