Data Modeling

Choosing a database, designing schemas, and normalizing vs denormalizing.

Data modeling is where your design meets reality. A good model makes your most common queries cheap; a bad one makes them impossible to scale. In an interview, you don't need full schemas — you need the right database choice, the key columns, and a model that fits your access patterns.

Start from access patterns

Before choosing a database, list how the data will be read and written:

"Fetch a user's 20 most recent posts" → posts indexed by (user_id, created_at)
"Find all bookings for an event" → bookings indexed by event_id
"Check whether this seat is available" → point lookup by (event_id, seat_id)

The access patterns drive both the database choice and the keys.

Choosing a database

[!INTERVIEW] "Why not just Postgres?" is a fair question Modern relational databases handle far more than most candidates assume — tens of thousands of writes per second on one primary, terabytes of data, read replicas for scale. Choose NoSQL for a specific reason (write volume, access by key at huge scale, flexible schema), and say what that reason is.

Normalization vs denormalization

Normalized data stores each fact once and joins at read time. It avoids inconsistencies and makes writes simple.

Denormalized data duplicates facts to make reads fast — for example, storing the author's name on every post so a feed render needs no join. The cost is keeping copies in sync when the original changes.

A practical approach: start normalized, then denormalize specific read paths that are hot, and explain how the copies are updated (synchronously in a transaction, or asynchronously via events).

Keys and relationships

Primary keys: prefer opaque IDs (UUIDs, Snowflake-style IDs) over business data. Time-ordered IDs make "most recent first" queries cheap.
Foreign keys in relational DBs enforce integrity — useful, but they don't work across shards.
Partition keys in NoSQL stores decide which node holds the data; they must spread load evenly *and* match your queries (see Sharding).

Example: a simple social app

users   (id PK, username UNIQUE, created_at)
posts   (id PK, user_id FK → users.id, text, created_at)
         INDEX (user_id, created_at DESC)
follows (follower_id, followee_id, created_at)
         PRIMARY KEY (follower_id, followee_id)
         INDEX (followee_id)

The composite index on posts serves "a user's recent posts" directly. The follows table is indexed both ways so you can answer "who do I follow?" and "who follows me?".

Schema evolution

Real systems change. Mention that you'd add columns as nullable, backfill data asynchronously, and deploy readers that tolerate both old and new shapes before writers start producing the new one.