Schema Design & Normalization
Structuring tables to avoid redundant, inconsistent data — and knowing when to deliberately break the rules for performance.
Try answering in your head first, then click a question to check the model answer.
Q1.What's an update anomaly, and how does normalization prevent it?(show answer)
If the same fact (e.g. a customer's address) is duplicated across many rows in a denormalized table, updating it means finding and updating every duplicate — miss one, and the data becomes inconsistent. Normalization stores each fact in exactly one place (e.g. a separate customers table referenced by ID), so an update happens in one row, and every referencing row sees the change consistently.
Q2.Explain the difference between 1NF, 2NF, and 3NF at a high level.(show answer)
1NF: each column holds atomic (indivisible) values, no repeating groups. 2NF: 1NF, plus every non-key column depends on the whole primary key (relevant when the key is composite) — no partial dependency. 3NF: 2NF, plus no non-key column depends on another non-key column (no transitive dependency) — every non-key column depends only on the key.
Q3.Give a concrete example of when denormalization is the right call despite the redundancy risk.(show answer)
A read-heavy reporting/analytics table that's queried far more often than it's written — pre-joining and flattening normalized source tables into one wide table avoids expensive joins on every read, at the cost of needing a refresh process to keep it in sync. This is standard practice in data warehousing (star schemas), where read performance matters more than avoiding redundancy.
Q4.What does a foreign key constraint prevent that application code alone might miss?(show answer)
It prevents inserting a row that references a non-existent parent (e.g. an order with a customer_id that doesn't exist) and, depending on configuration, controls what happens when a referenced row is deleted (block the delete, cascade the delete, or set the reference to NULL) — enforced at the database level, so it holds even if application code has a bug or a change is made directly in the database.