Data Modeling

Most system design decisions eventually come back to the data: where it lives, how it's shaped, and how fast you can read the slice you need. Get the model right and the rest of the system has an easy job. Get it wrong and you'll spend the design fighting your own storage.

The single most useful habit: model for your access patterns, not your entities. Write down the exact queries the system must answer, then choose the store and schema that make those queries cheap.

SQL or NoSQL?

This is the first fork, and the honest answer is usually "SQL, unless you have a specific reason."

Relational (Postgres, MySQL) gives you a strict schema, joins, transactions, and decades of tooling. It's the right default when your data has relationships and you value correctness — which is most of the time. Modern Postgres scales further than people expect.

NoSQL is a family, not one thing. Reach for a specific member when you have a specific need:

The real trade is flexibility and scale vs. correctness and ad-hoc queries. SQL makes new queries easy and consistency automatic; many NoSQL stores make you design the schema around one access pattern up front, and relax consistency to scale writes.

Normalize, then denormalize on purpose

Normalization stores each fact once. A user's name lives in users; an order references it by user_id. No duplication means no update anomalies — change the name in one place. The cost is joins at read time.

Denormalization deliberately duplicates data to avoid those joins. A social feed might store author_name and author_avatar directly on each post, so rendering the feed needs no join to users. The cost is keeping copies in sync when the source changes.

The rule: normalize by default; denormalize a specific hot read path when a join becomes the bottleneck — and accept that you now own the job of keeping the copies fresh.

Model relationships deliberately

• One-to-many (a user has many posts): the "many" side holds a foreign key (posts.user_id).
• Many-to-many (students and courses): a join table holding two foreign keys.
• One-to-one (a user and their profile): a foreign key with a unique constraint, or just more columns.

In NoSQL you often embed related data instead (a document holds its comments) when it's read together and bounded in size, and reference it when it's large or shared.

Snapshot data that must not change

When a value is a record of something that happened, copy it — don't reference it. An order line should store the price at the time of sale, not a foreign key to the product's current price. Next month's price change must never rewrite what someone paid last month. This looks like duplication and isn't; it's history.

Let the access pattern pick the key

The shape of your primary key and indexes should come straight from your top queries:

• "Get a user's posts, newest first" → index on (user_id, created_at DESC).
• "Look up an order by reference" → unique index on reference.
• In a key-value/wide-column store, the partition key is the thing you query by — pick it so each common query hits one partition.

Estimate the size early

A back-of-the-envelope capacity check often settles the SQL/NoSQL question on its own. Rows × bytes-per-row × growth tells you whether one Postgres box holds years of data (it usually does) or whether you're genuinely in distributed-database territory. Most apps overestimate this wildly.

What to say in an interview

State your top access patterns out loud, pick a default (usually a relational DB), and justify any move away from it with scale or a specific shape:

"I'll use Postgres. The entities are users, rides and payments, with rides referencing users. The hot read is a driver's recent rides, so I'll index (driver_id, created_at). Payments snapshot the fare at charge time. If ride events grow into the billions I'd move just that stream to a wide-column store keyed by ride id — but the core stays relational for the transactions."

---

Next up: Caching — making the hot reads above effectively free.