Database Design — Interview Q&A
Q: Walk me through normalizing a table, and explain what each normal form actually fixes.
A: Start from an unnormalized table with repeating/duplicated data (Overview's order example: customer name/email repeated on every order row). 1NF fixes atomicity — no arrays or repeating groups in a single column. 2NF removes partial dependencies — separating data (customer info) that doesn't depend on the full composite key. 3NF removes transitive dependencies — data (like product category name) that depends on another non-key column rather than directly on the primary key. A strong answer names the specific anomaly (update, insertion, or deletion) each step prevents, not just the rule's definition.
Q: When would you deliberately denormalize a schema, and what's the real cost of doing so?
A: When a specific, high-frequency query pattern has a real, measured performance cost from the joins a fully normalized schema requires — denormalization trades some duplicate data (and the update-anomaly risk that comes with it) for avoiding that join cost. The key word is deliberate and measured — denormalizing without first confirming the join cost is actually a problem, or without a plan for keeping the duplicated data consistent, is just reintroducing normalization's original problems without the performance justification that would make it worthwhile.
Q: Why does column order matter in a composite index?
A: A composite index is physically structured to search efficiently by its leading column first, then narrow within that by subsequent columns — a composite index (customer_id, status) efficiently serves queries filtering on customer_id alone or customer_id AND status together, but doesn't efficiently serve a query filtering on status alone, since the index isn't organized to search by status independently of customer_id. The general guidance: highest-selectivity equality-condition column first, range conditions or lower-selectivity columns after.
Q: What's the tradeoff of adding an index — why not just index every column?
A: Every index speeds up the specific reads it's designed for, but slows down every write to that table (each insert/update/delete has to update the index too) and consumes real storage. Indexing every column optimizes reads at a real, compounding write-performance and storage cost — the correct approach is indexing based on actual query patterns (what you're actually filtering/sorting/joining on frequently), not indexing defensively "just in case."
Q: How would you design a database schema to support multiple tenants (customers) in a SaaS application, and what are the tradeoffs of your approach?
A: Three common approaches: shared tables with a tenant_id column (simplest to operate, but requires disciplined, ideally database-enforced — not just application-code — tenant scoping on every query, since a missing filter is a serious cross-tenant data leak); schema-per-tenant (stronger isolation, harder to manage migrations consistently across potentially thousands of schemas); database-per-tenant (strongest isolation, most operational overhead, usually reserved for enterprise tenants with genuine compliance needs). A strong answer picks based on actual isolation requirements and expected tenant count/scale, not a default choice, and mentions row-level security as a stronger enforcement mechanism than relying on application code alone for the shared-table approach.
Q: What's the difference between partitioning and sharding, and when would you need each?
A: Partitioning splits a large table into smaller physical pieces within a single database instance, improving query and maintenance performance while the table is still logically one table to queries. Sharding splits data across multiple, separate database instances entirely, necessary once a single instance's write throughput or storage genuinely can't scale further vertically. Partitioning is reached for earlier and more often (large-table performance within a still-manageable single instance); sharding is a bigger architectural commitment, with real costs (cross-shard queries are expensive, shard-key choice is hard to change later) that shouldn't be taken on before the scale genuinely requires it.
Q: A read replica is returning stale data compared to the primary — is this a bug?
A: Not inherently — read replicas are asynchronously replicated by design, and some lag is expected, especially under write load. The real design question is whether the specific read that hit stale data actually required up-to-date data — most analytics/reporting queries tolerate replica lag fine; a user immediately viewing their own just-submitted write typically doesn't, and needs to either read from the primary for that specific case or use an explicit "read your own writes" consistency pattern. Treating all replica lag as uniformly a bug misses that this tradeoff is the entire point of using read replicas for horizontal read scaling.