Data Modeling
Data modeling is the art of representing real-world entities and relationships in a database. The key decisions: entities (what tables), relationships (one-to-one, one-to-many, many-to-many), normalization vs denormalization (consistency vs read speed), primary keys (natural vs surrogate), and indexes (what to index, what not to). Good data modeling makes queries fast and invariants enforceable; bad modeling makes everything slow and inconsistent.
Foundational.
How it works
Data modeling answers four questions:
- Entities: what are the nouns? (users, orders, products)
- Attributes: what properties does each entity have? (user.email, order.total)
- Relationships: how do entities relate? (a user has many orders; an order has many products through line items)
- Constraints: what invariants must hold? (email is unique; order.total > 0; line_item.quantity > 0)
The standard modeling technique is entity-relationship (ER) modeling: draw entities as boxes, attributes as fields, relationships as lines with cardinality (1:1, 1:N, N:M). Then translate to tables.
Three relationship types:
- One-to-one: rare; usually modeled as fields on the parent. Use a separate table only if the entity is optional or very large.
- One-to-many: foreign key on the "many" side. (
orders.user_idreferencesusers.id.) - Many-to-many: requires a junction table. (
user_groupswithuser_idandgroup_id.)
Normalization is the process of removing redundancy. 3rd normal form (3NF): every non-key field depends only on the key, the whole key, and nothing but the key. In practice: don't store the same fact in two places.
Why normalize?
- Consistency: if the product price is stored once, it can't disagree with itself.
- Smaller storage: no duplicated data.
- Easier updates: change the price in one place; all orders see it.
Why denormalize?
- Read performance: joining 5 tables to render a product page is slow. Denormalize by storing a snapshot of the price in
order_items.unit_price_centsso historical orders show the price at the time, not the current price. - Write performance: avoid cascading updates.
- Operational simplicity: a single read is easier than a 5-way join.
The trade-off: normalization optimizes for writes and consistency; denormalization optimizes for reads. Most OLTP systems are normalized; most OLAP / reporting systems are denormalized (star schemas). The e-commerce schema above is normalized for the most part, with strategic denormalization (unit_price_cents in order_items — a snapshot of the historical price).
Choosing primary keys:
- Natural keys (email, SSN, ISBN): meaningful, but they change (users change emails) and may have privacy implications (SSN as PK exposes it in every foreign key).
- Surrogate keys (auto-increment integer, UUID): meaningless, but immutable and stable. The standard choice.
Integer vs UUID:
- Auto-increment integer: 8 bytes, fast to index, sequential (good for B-tree locality). Cons: reveals total count (the 1000th user knows your growth); central allocator (a bottleneck in sharded systems).
- UUID: 16 bytes (or 8 for UUIDv7), globally unique (no central allocator), no count leakage. Cons: random UUIDs fragment B-trees; larger indexes. UUIDv7 (time-ordered) fixes the fragmentation.
Modern default: UUID (or UUIDv7 for time-ordering) as PK, with a separate auto-increment column if you need count-based IDs externally. Use ULID or Snowflake for time-ordered unique IDs that work across shards.
Never use email or username as the primary key. Users change emails; renaming cascades through every foreign key — slow and error-prone. Use a stable surrogate key; store email as a unique constraint.
An index is a sorted data structure (B-tree, hash) that lets the database find rows without scanning the whole table. Every foreign key should have an index — joins and lookups become O(log N) instead of O(N). Every column used in WHERE or ORDER BY frequently should be indexed. But every index adds write cost: each INSERT/UPDATE/DELETE must update all affected indexes. Don't index everything — index the queries that matter. Use composite indexes for multi-column queries (e.g., (user_id, created_at) for "list this user's orders by date"). The order of columns in a composite index matters — it supports prefix lookups. Profile with EXPLAIN to find slow queries and missing indexes.
Common data-type mistakes:
- Money: never use FLOAT — floating-point can't represent decimals exactly (0.1 + 0.2 ≠ 0.3). Use INTEGER cents (or DECIMAL).
$10.50is stored as1050cents. Libraries handle the conversion. - Timestamps: always store UTC. Always include timezone awareness. Never store local time — daylight savings corrupts comparisons.
- Booleans: fine for true/false, but if you might add a third state later ("unknown"), use an enum.
- Enums vs strings: enums are type-safe and indexed efficiently. Strings are flexible but error-prone (typos). Use enums for fixed sets.
- JSON columns: useful for sparse or evolving data, but you lose query performance and constraint enforcement. Use sparingly — extract to columns when the data is queried.
- Text length: don't use VARCHAR(255) everywhere. Use TEXT for unbounded text, VARCHAR(N) only when there's a real limit.
- NOT NULL: default to NOT NULL. Nullable columns cause bugs. Allow NULL only when there's a meaningful difference between "unknown" and "empty."
You're storing the price of products in an e-commerce schema. Users place orders that reference products. Why store `unit_price_cents` in `order_items` instead of joining to `products` to get the current price?
Pick one answer.
Why should you avoid using `email` as the primary key for a users table?
Pick one answer.
Why is FLOAT a bad choice for storing monetary amounts?
Pick one answer.
Engineering mental model
Mental model. Think of Data Modeling as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does Data Modeling mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”
Before choosing Data Modeling, name the workload, the critical user path, the dominant bottleneck, the failure you are trying to absorb, and the trade-off you are willing to accept. If you cannot name those five things, the design is probably premature.
// Pseudocode
request = receive()
result = data_modeling(request)
return result
// Production questions:
// 1. What happens on timeout?
// 2. Can this operation be retried safely?
// 3. What is the bottleneck?Back-of-the-envelope reasoning
Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.
Interactive thought experiment: Data Modeling
Change the variables below and predict what breaks first in Data Modeling. The production lab can later reuse these same inputs.
Change one variable at a time. Predict the failure mode first, then move the slider and see whether your mental model matches the simplified system response.
If you are stuck on Data Modeling, start by drawing the request path and marking every network hop, stateful component, queue, cache and failure boundary. Then estimate where the system will saturate.
You increase traffic by 10× in a system using Data Modeling. What should you inspect first?
Pick one answer.
Which statement is the safest engineering habit when using Data Modeling?
Pick one answer.
You have dashboards for traffic, latency, errors and saturation. You can change the architecture, but every change has operational cost.
Production scenario: your system uses Data Modeling, traffic suddenly spikes, and p99 latency doubles. What is your first move?
Interview drill
Answer this without notes: When would you choose Data Modeling, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.
A useful engineering lens for Data Modeling: define the problem it solves, the simpler design that fails first, the constraint that forces you to introduce this concept, and the new failure modes the concept creates.
Numerical sanity check
Back-of-the-envelope reasoning beats fake precision. State your traffic, payload, concurrency and growth assumptions explicitly, then calculate enough to know whether the current architecture is orders of magnitude away from the target.
Do not optimize for a memorized definition. Reason from the workload and failure mode.
Imagine the simplest version of a system using Data Modeling. What breaks first as traffic grows by 10×, and what would you change before reaching 100×?
Pick one answer.
What you gain, what you pay
- +Normalized schemas enforce consistency (no duplicated facts to drift).
- +Foreign keys and unique constraints enforce invariants at the DB level.
- +Proper indexes make queries fast.
- +Stable surrogate keys make schema evolution safe.
- −Joins on normalized schemas can be slow for read-heavy workloads.
- −Denormalization for performance requires careful update logic.
- −Too many indexes slow writes.
- −Schema migrations on large tables can be slow and risky.
How this breaks in production
- Using FLOAT for money — rounding errors accumulate.
- Email/username as PK — cascading updates when it changes.
- Missing indexes on foreign keys — slow joins and full table scans.
- Storing money as current price instead of snapshotting at order time.
Don't fall into these traps
- •Using natural keys (email) instead of surrogate keys (UUID) as PK.
- •Forgetting to index foreign keys — most databases don't auto-index them.
- •Using FLOAT instead of INTEGER cents or DECIMAL for money.
- •Storing local time instead of UTC — daylight savings corrupts comparisons.
Real systems using this
How real systems implement this
- Stripe — Stores all monetary amounts as integer cents. Every record has a UUID primary key. Their schema is normalized for consistency with strategic denormalization (e.g., snapshotting the fee structure at charge time).
- GitHub — Uses integer auto-increment IDs for most entities (revealing total counts — sometimes controversial). Many-to-many relationships (repo ↔ user) use junction tables. Heavily indexed for read performance.
Practice saying it out loud
- Q1Design the data model for an e-commerce system. What tables, relationships, and indexes?
- Q2When would you normalize vs denormalize?
- Q3Why store money as INTEGER cents instead of FLOAT?
- Q4Why use a surrogate key (UUID) instead of email as the primary key?
Further reading & references
Core explanations are original NO CAP material. External references are provided for deeper study and standards.
What next?
Mark as understood once the mental model clicks.
Next recommended
SQL vs NoSQL