Denormalization interview questions, with answers

The questions, with answers

1. 1.Why would you denormalize a schema you took the trouble to normalize?

   In short: Because one specific read — a join or an aggregate that runs constantly — has become too slow, and keeping a copy current costs less than recomputing it on every read.

   Normalization optimises for correctness and cheap writes: each fact lives in one place, so an update touches one row. Reads pay for that with joins and aggregates. When one read dominates — a course page showing how many students are enrolled, loaded thousands of times for every new enrolment — computing COUNT(*) over Enrollments each time is wasted work, and storing enrolled_count on the Courses row turns the read into a primary-key lookup. The trade is explicit: faster reads in exchange for more work on every write and a new way to be wrong. So the order matters: normalize first, measure, and denormalize the one path the measurement points at, not the whole schema in advance.

2. 2.What are the common denormalization techniques?

   In short: Copy a column into the table that reads it, store a derived value such as a count or a total, pre-join tables, keep summary tables, or let the database maintain a materialized view.

   A copied column repeats an attribute from a parent table, such as a course title stored on each enrolment, to avoid a join. A stored derived value keeps the result of a calculation — an enrolled count, an order total, a running balance — instead of computing it on read. A pre-joined table merges two tables that are always read together. A summary table holds aggregates at a coarser grain, such as enrolments per course per day, for reports. A materialized view is the same idea with the database doing the maintenance, where the database supports it. They differ in what can go stale and in who keeps them fresh, and that is the question to answer for each one before choosing it.

3. 3.How do you keep a denormalized value consistent with its source?

   In short: Update it in the same transaction as the source — in application code or with a trigger — or refresh it later on a schedule if the readers can tolerate staleness.

   Same-transaction maintenance keeps the copy exact: the insert into Enrollments and the change to enrolled_count commit together or not at all. Doing it in application code works until one code path forgets; a trigger, as in the SQL below, moves the rule into the database so that every writer obeys it. The triggers are written in SQLite's syntax; PostgreSQL wraps the same UPDATE in a trigger function, and MySQL adds FOR EACH ROW. When a few minutes of staleness are acceptable, a scheduled job or a change-data-capture stream can rebuild the copy asynchronously and keep writes fast. The choice follows from one question — how wrong may this value be, and for how long — so write the answer down next to the column.

   CREATE TABLE Courses (
       course_id      INTEGER PRIMARY KEY,
       title          TEXT NOT NULL,
       enrolled_count INTEGER NOT NULL DEFAULT 0    -- denormalized: derived from Enrollments
   );
   CREATE TABLE Enrollments (
       student_id INTEGER NOT NULL,
       course_id  INTEGER NOT NULL REFERENCES Courses (course_id),
       PRIMARY KEY (student_id, course_id)
   );
   
   CREATE TRIGGER enrollment_added AFTER INSERT ON Enrollments
   BEGIN
       UPDATE Courses SET enrolled_count = enrolled_count + 1 WHERE course_id = NEW.course_id;
   END;
   
   CREATE TRIGGER enrollment_removed AFTER DELETE ON Enrollments
   BEGIN
       UPDATE Courses SET enrolled_count = enrolled_count - 1 WHERE course_id = OLD.course_id;
   END;

4. 4.What goes wrong with a stored counter when two writes happen at once?

   In short: Reading the value, adding one in application code and writing it back loses updates under concurrency; incrementing inside a single UPDATE lets the database apply the changes one at a time.

   If two enrolment requests each read enrolled_count as 40, add one in application code and write back 41, the count ends at 41 although there are now 42 enrolments: a lost update. The fix is to make the increment a single statement, UPDATE Courses SET enrolled_count = enrolled_count + 1 WHERE course_id = ?, because the database locks the row for the update and applies the two increments one after the other. Where the logic is more complex, SELECT … FOR UPDATE before a read-modify-write gives the same protection. A periodic job that recomputes each count from Enrollments and reports any difference is cheap insurance for every denormalized value that matters.

5. 5.When should you not denormalize?

   In short: When an index, a better query or a cache would fix the slow read, when the copied data changes often, or when a stale value would be a correctness bug rather than a cosmetic one.

   Check the cheaper fixes first: a missing index on the join column, a covering index that answers the query from the index alone, or a query rewritten to aggregate before it joins. If those fail, look at the write side of the copy. A course title changes rarely, so copying it is cheap; a student's attendance percentage changes daily, so every change fans out to every copy. Finally, ask what a stale value costs. A like count that lags by a minute is harmless; a stock level or an account balance that disagrees with its source can oversell or overpay. For values like those, keep one source of truth and make the read fast another way.

6. 6.How is a materialized view different from a denormalized table?

   In short: A materialized view stores a query's result and lets the database refresh it from the declared query; a denormalized table is kept correct only by your own code or triggers.

   Both store redundant data for faster reads; the difference is who owns the maintenance. A materialized view keeps the query itself as its definition, so the base tables stay normalized and the view can always be rebuilt. The database does the refreshing: on demand in PostgreSQL, with REFRESH MATERIALIZED VIEW, and on commit or on a schedule in Oracle, while SQL Server's indexed views are updated on every write, much like a trigger would be. A denormalized column has no definition in the schema at all, only the code that updates it, so if that code has a bug the database cannot tell. Prefer the view wherever the database offers the refresh behaviour you need.

7. 7.Why are data warehouses deliberately denormalized?

   In short: Analytical queries read huge ranges and join the same few lookup tables every time, so warehouses keep wide, flattened dimension tables around a central fact table: the star schema.

   An operational database serves many small reads and writes, so it normalizes. A warehouse serves a few enormous reads — total sales by region and month over three years — and is loaded in batches, so writes are rare and controlled. A star schema puts the measurements, one row per sale, in a fact table, and describes each sale through a handful of dimension tables — date, product, store — each flattened into one wide table, so a report joins the fact table to each dimension once instead of walking a chain of normalized tables. The redundancy is cheap there, because the loader controls every write and columnar storage compresses repeated values well.

8. 8.How does denormalization look in a document database such as MongoDB?

   In short: It is the default: related data is embedded in the document that reads it, and the design question becomes what to embed, what to reference and what to copy.

   In a document store, a course document can hold its sessions as an embedded array, so one read returns everything the course page needs, with no join. Embed data that is read together, owned by the parent and bounded in size; reference data that is shared by many parents, grows without limit or changes independently — MongoDB also caps a single document at 16 MB. A common middle path, which MongoDB calls the extended reference pattern, copies just the fields a read needs, such as an instructor's name inside each course, and accepts that a rename must update every copy. It is the same trade as in SQL, made the default instead of the exception.

How the diagnostic asks it

One question from the DBMS bank, exactly as a sitting would show it. The bank has 6 on denormalization and 67 across DBMS.