Schema Design Fundamentals

A schema isn't designed in a vacuum. It's a response to three constraints: how much data you have, how it will be queried, and how consistent it must be. In an interview, tie every schema decision back to one of these three, so it's clear you're designing for the problem rather than reciting a textbook.

The three driving factors#

Each factor answers a question and drives a different part of the schema.

Data volume → where the data physically lives#

A billion users' data won't fit on one disk, so it gets split across servers. If users and posts end up on different machines, the database can't JOIN them, so the schema has to support application-level joins: the service fetches from each store and combines the results.

Access patterns → table structure#

Say the feed API must return each post with its total likes, instantly. In a fully normalized schema, that means counting rows in a huge likes table on every read. Instead, denormalize: keep a like_count column on posts and update it on each like. Writes get slightly more work and reads get much cheaper.

The pattern for this is a source of truth plus a read optimization:

• Source of truth: the likes table, one row per like.
• Read optimization: posts.like_count, a derived value you can always rebuild from likes.

Check your schema against your API. If the API has GET /users/{id}/feed, there must be a way to answer it without scanning ten tables.

Consistency → database choice#

A bank transfer must be exactly right; a like count can lag by a couple of seconds.

• Strong consistency: use a relational database like PostgreSQL, and keep related data in the same instance so you can use transactions.
• Eventual consistency: a distributed NoSQL store is fine. A like that takes a moment to show up everywhere is acceptable.

Default to PostgreSQL for core entities like users and accounts, then add other stores for specific needs.

Explaining decisions: "Because X, we'll do Y"#

Don't just draw tables. State the constraint, then the choice and its cost:

• Denormalization: "The feed is read-heavy and must be fast, so we'll store comment_count on posts. Keeping it in sync adds write logic, but it avoids a COUNT() on every read."
• Distribution: "Data will reach petabytes, so we'll shard by user_id. That rules out cross-user JOINs in the database, so the service layer will combine those results."
• Consistency: "Payments are lower volume but must never double-charge, so that service keeps a normalized schema in PostgreSQL with ACID transactions."

Key takeaways#

• Every schema decision traces back to data volume, access patterns or consistency.
• Large volume forces data apart, which means application-level joins.
• Access patterns justify indexes and denormalized counters. Keep a normalized source of truth.
• Consistency needs pick the store: ACID for money, eventual consistency for social signals.
• In an interview, phrase decisions as "because X, we'll do Y, at the cost of Z".