Database Design Interview Questions

A curated set of database design interview questions, ordered roughly from fundamentals to more advanced — the kind you'll actually be asked in real screens and on-sites.

Normalization#

Q: Explain 1NF, 2NF, and 3NF in your own words. 1NF requires every column to hold a single atomic value, with no repeating groups or comma-separated lists standing in for what should be separate rows. 2NF (which only applies to tables with a composite key) additionally requires every non-key column to depend on the entire key, not just part of it. 3NF goes further still, requiring every non-key column to depend directly on the primary key rather than transitively through another non-key column — in practice, most real schemas are designed straight to 3NF rather than treated as three separate sequential migrations.

Q: What are the practical downsides of normalization, and when would you deliberately denormalize? Normalization reduces duplication and prevents update/insertion/deletion anomalies, but it means reassembling a full picture usually requires joining several tables, which has a real query cost. Deliberate denormalization is the right call for read-heavy reporting tables or dashboards (flattening data ahead of time so a page load doesn't need a five-table join), and for intentional historical snapshots — an order line item storing the product's price at the time of purchase is supposed to diverge from the live product price over time, which is correct denormalization, not a bug.

Schema design#

Q: How do you decide whether a relationship should be modeled with a foreign key or a join table? A one-to-many relationship (one user has many orders) is modeled with a foreign key on the "many" side, pointing back at the "one" side's primary key. A many-to-many relationship (an order can contain many products, and a product can appear on many orders) needs a join table in between, holding foreign keys to both sides — and if that pairing itself has its own attributes (like quantity on an order/product pairing), that's the clearest signal a join table is required rather than a single foreign key.

Q: What's the difference between a primary key and a foreign key, and can a column be both? A primary key uniquely identifies each row within its own table and can never be null; a foreign key is a column that references another table's primary key, enforcing that the referenced row actually exists. Yes, a column can be both — in a join table like order_items, order_id and product_id are each a foreign key back to their own source table, and together they commonly form that table's composite primary key.

Q: When would you choose a natural key (like an email address) over a surrogate key (like an auto-incrementing id) for a primary key? A surrogate key is the safer default in almost all cases — it never needs to change even if a "natural" identifying value does (an email address can be changed by the user; an auto-incrementing id never needs to be). A natural key is occasionally reasonable for genuinely immutable, guaranteed-unique values (a country's ISO code, for instance), but even then many teams still prefer a surrogate key purely for consistency across the schema and to avoid ever needing a painful key-change migration later.

Transactions#

Q: Give a concrete example of what breaks if atomicity is violated. A transfer between two bank accounts runs as two separate UPDATE statements — debit one account, credit the other. If the application or database crashes between the two statements without atomicity, the debit could persist while the credit never happens, and $100 effectively vanishes from the system. Atomicity guarantees that either both updates take effect or neither does, so a crash mid-transaction can never leave that kind of partial, inconsistent state behind.

Q: How do you decide where transaction boundaries belong in an application? A transaction boundary should wrap exactly the set of writes that only make sense together as one logical business operation — for example, creating an order and decrementing the corresponding product's stock, since "an order exists but stock was never reserved" is exactly the inconsistent state a transaction should prevent. Boundaries drawn too narrowly (each write auto-committed independently) reintroduce partial-failure risk; boundaries drawn too broadly (wrapping unrelated work, external API calls, or user-input waits into one transaction) hold locks longer than necessary and hurt concurrency.

Applied design#

Q: A requirement says "an order ships to one of the customer's saved addresses." Why is a plain foreign key from orders to addresses usually the wrong design? Because a customer can edit or delete a saved address at any time, and an order that already shipped needs to keep showing the address it actually shipped to, unchanged, forever. If orders merely references addresses by id, editing that address later silently rewrites the historical order's shipping details, and deleting it can break the order record entirely. The correct approach is copying the relevant address fields directly onto the order at the moment it's placed — the same deliberate-denormalization reasoning that justifies storing a product's price on an order line rather than pointing back at the live, changeable price.

Q: What's the difference between a read replica and a denormalized reporting table, and when would you reach for each? A read replica is a continuously synchronized copy of the primary with an identical schema — it offloads expensive read traffic onto separate hardware without changing what's queried or introducing any staleness beyond normal replication lag. A denormalized reporting table stores the precomputed result of an expensive query, refreshed on a schedule, so a dashboard reads a cheap lookup instead of re-running a costly join and aggregation every time. Reach for a read replica first, since it requires no schema changes and no risk of the data drifting from reality; reach for a reporting table once the underlying query itself is too expensive to run live at the frequency it's actually needed, even on a replica.

Q: Why should a materialized aggregate (like a running daily revenue total) be updated in the same transaction as the write that changes it? If the aggregate update happens as a separate transaction, there's a real window where one succeeds and the other fails independently — the order commits but the revenue total's increment doesn't, or vice versa — silently drifting the aggregate away from what the underlying data actually says. Updating both inside one transaction guarantees they move together: either the order and its contribution to the running total both commit, or neither does.