Denormalization & Real-World Trade-offs

9. Practice Problems

Twelve problems on real denormalization decisions. Most have a runnable SQLite answer.

1. Add a trigger that decrements review_count and stars_sum when a review is deleted.
   review delete হলে review_count ও stars_sum কমানোর জন্য trigger লিখুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans1.sql

   CREATE TRIGGER reviews_after_delete
   AFTER DELETE ON reviews
   BEGIN
       UPDATE products
          SET review_count = review_count - 1,
              stars_sum    = stars_sum    - OLD.stars
        WHERE product_id = OLD.product_id;
   END;
   INSERT INTO reviews(review_id, product_id, stars) VALUES (10, 1, 4);
   DELETE FROM reviews WHERE review_id = 10;
   SELECT * FROM products;

   Run Copy Open in SQLite Online

2. Write an UPDATE trigger so editing a review's star value keeps the cached sum correct.
   review edit করার জন্য UPDATE trigger লিখুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans2.sql

   CREATE TRIGGER reviews_after_update
   AFTER UPDATE OF stars ON reviews
   BEGIN
       UPDATE products
          SET stars_sum = stars_sum + NEW.stars - OLD.stars
        WHERE product_id = NEW.product_id;
   END;
   UPDATE reviews SET stars = 3 WHERE review_id = 1;
   SELECT * FROM products;

   Run Copy Open in SQLite Online

3. A reconciliation query — write SQL that finds products whose cached review_count disagrees with the actual count of rows in reviews.
   cached count এবং actual count মিলছে না — এমন product বের করুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans3.sql

   SELECT p.product_id, p.review_count AS cached, COUNT(r.product_id) AS actual
   FROM products p
   LEFT JOIN reviews r ON r.product_id = p.product_id
   GROUP BY p.product_id, p.review_count
   HAVING p.review_count <> COUNT(r.product_id);

   Run Copy Open in SQLite Online

4. Suggest one place where storing customer_full_name on every orders row is correct, not a bug.
   প্রতিটি orders row-এ customer_full_name রাখা কখন সঠিক?
   ✨ Show Answer (উত্তর দেখুন)

   Answer: When you need a frozen historical snapshot. If the customer later changes their name, the invoice should still reflect the name at the time of purchase. This is not denormalization — it is treating the value as a different fact (the name-on-the-day-of-sale) which simply happens to coincide with the live name today.

5. Write a single SQL statement that fully recomputes review_count and stars_sum from scratch, as a nightly drift check.
   nightly recompute-এর জন্য SQL লিখুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans5.sql

   UPDATE products
      SET review_count = (SELECT COUNT(*) FROM reviews r WHERE r.product_id = products.product_id),
          stars_sum    = COALESCE((SELECT SUM(stars) FROM reviews r WHERE r.product_id = products.product_id), 0);
   SELECT * FROM products;

   Run Copy Open in SQLite Online

6. List two queries on a Daraz-style site that should NOT be denormalized, and explain why.
   কোন দুটি query denormalize করা উচিত নয় — কেন?
   ✨ Show Answer (উত্তর দেখুন)

   Answer: (1) Customer billing address — must always be the latest, not a cached copy that drifts. (2) Real-time stock count — denormalizing into a cached column risks selling phantom inventory under concurrency. Both want strict consistency more than speed.

7. Convert a normalized order_items(order_id, product_id, qty) + products(product_id, price) into a fact table for nightly reporting.
   উপরের টেবিলগুলো থেকে fact_order_items তৈরি করুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans7.sql

   CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, qty INTEGER);
   CREATE TABLE products    (product_id INTEGER PRIMARY KEY, name TEXT, price INTEGER);
   INSERT INTO products    VALUES (1,'Phone',25000), (2,'Book',450);
   INSERT INTO order_items VALUES (100,1,2), (100,2,3);
   
   CREATE TABLE fact_order_items AS
   SELECT oi.order_id, oi.product_id, p.name, oi.qty, p.price, oi.qty * p.price AS line_total
   FROM order_items oi JOIN products p ON p.product_id = oi.product_id;
   SELECT * FROM fact_order_items;

   Run Copy Open in SQLite Online

8. Snowflake the dim_product dimension by extracting category into dim_category. Show the new schema.
   dim_product-কে snowflake করে dim_category বের করুন।
   ✨ Show Answer (উত্তর দেখুন)

   ans8.sql

   CREATE TABLE dim_category (category_key INTEGER PRIMARY KEY, category_name TEXT);
   CREATE TABLE dim_product  (
       product_key  INTEGER PRIMARY KEY,
       name         TEXT,
       category_key INTEGER REFERENCES dim_category(category_key)
   );
   INSERT INTO dim_category VALUES (1,'Electronics'),(2,'Books');
   INSERT INTO dim_product  VALUES (10,'Phone',1),(11,'Book',2);
   SELECT p.name, c.category_name
   FROM dim_product p JOIN dim_category c ON c.category_key = p.category_key;

   Run Copy Open in SQLite Online

9. A junior engineer suggests replacing every JOIN in the OLTP database with denormalized columns. Give two reasons this is dangerous.
   প্রতিটি JOIN-কে denormalized column দিয়ে replace করা কেন বিপজ্জনক — দুটি কারণ দিন।
   ✨ Show Answer (উত্তর দেখুন)

   Answer: (1) Every write now updates many rows; INSERT/UPDATE latency explodes. (2) Drift becomes inevitable — without disciplined triggers and reconciliation, the cached copies diverge silently from the source of truth, producing wrong financial reports and customer-visible bugs.

10. Identify which tables in a bKash transaction system you would NOT denormalize and why.
    bKash-এ কোন কোন টেবিল কখনো denormalize করবেন না — কেন?
    ✨ Show Answer (উত্তর দেখুন)

    Answer: The tx (transaction) and account_balance tables. Both demand strict, second-by-second correctness. A denormalized column drifting by even Tk 0.01 is an audit failure. Reads here are also already cheap — accounts are looked up by primary key.

11. Write a query that uses the daily_revenue materialized table to find the top 3 revenue days.
    daily_revenue থেকে top 3 দিন বের করুন।
    ✨ Show Answer (উত্তর দেখুন)

    ans11.sql

    SELECT order_date, revenue
    FROM daily_revenue
    ORDER BY revenue DESC
    LIMIT 3;

    Run Copy Open in SQLite Online

12. Describe a "100× faster" denormalization story you can defend in a code review.
    code review-তে defend করার মতো একটি "100× faster" denormalization গল্প বর্ণনা করুন।
    ✨ Show Answer (উত্তর দেখুন)

    Answer: "Our product page query joined products, reviews and order_items with two GROUP BYs. p99 latency was 480 ms. We added a products.review_count and products.stars_sum column, maintained by three triggers covering insert/update/delete, plus a nightly reconciliation script. p99 dropped to 4 ms — a 120× improvement on a page that loads 3 million times per day. Reconciliation has flagged drift exactly twice in 18 months, both caught and fixed within an hour."

    একটি বাস্তব গল্প — measure, change, measure again, এবং drift ধরার জন্য nightly reconciliation। এটিই defendable engineering।