System Design: Data Modeling
How entities are stored, related, and indexed — with an interactive ER explorer, normalization trade-offs, and interview reasoning for scalable schemas.
Introduction
Data modeling answers three questions before you shard, replicate, or cache anything:
- What are the core entities?
- How do they relate (1:1, 1:N, N:M)?
- Which queries must be fast — and therefore need indexes?
This guide walks through a practical e-commerce schema and lets you explore entities interactively.
← Back to System Design overview
Core concepts
Entities and keys
Every table needs a primary key — usually a surrogate id (UUID or bigint) even when a natural key exists (email, SKU). Surrogate keys keep joins stable when business identifiers change.
Relationships
| Type | Example | Storage pattern |
|---|---|---|
| 1:1 | User ↔ Profile | FK on one side, or shared PK |
| 1:N | User → Orders | user_id on orders |
| N:M | Orders ↔ Products | Junction table order_items |
Indexes
Indexes speed up reads but slow writes. Add them for:
- Foreign keys used in joins (
user_id) - Filter columns (
status,created_at) - Unique business keys (
email,sku)
Interactive ER explorer
Click each entity below to inspect fields, indexes, and relationships.
Interactive ER Explorer
Click an entity to inspect fields, indexes, and relationships.
Selected: users
Fields
- • id (PK)
- • email (unique)
- • name
- • created_at
Indexes
- • PRIMARY (id)
- • UNIQUE (email)
- • INDEX (created_at)
Relationships
Normalization vs denormalization
Normalized schemas reduce duplication and keep updates simple. Denormalized schemas duplicate data for faster reads — common in analytics, feeds, and search indexes.
Example: order totals
Normalized:
SELECT o.id, SUM(oi.qty * p.price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 501
GROUP BY o.id;Denormalized: store orders.total updated on write — faster read, more logic on insert/update.
Interview checklist
| Step | Question to answer |
|---|---|
| 1 | Who are the actors and what do they create/read? |
| 2 | What is 1:N vs N:M? |
| 3 | Read-heavy or write-heavy? |
| 4 | Which columns appear in WHERE and ORDER BY? |
| 5 | When does one DB stop being enough? |
Subscribe to my newsletter
Stay up to date and get notified when I share new contents.
No spam ever, unsubscribe anytime