Denormalization — কখন নিয়ম ভাঙা যায়

১ · Normalization-এর দাম — পড়তে গেলে অনেক JOIN

পর্ব ৩৬ আর ৩৭-এ আপনি টেবিল ভেঙেছেন 3NF আর BCNF পর্যন্ত। লাভটি স্পষ্ট: প্রতিটি তথ্য এক জায়গায় থাকে, তাই একটি গ্রাহকের ফোন নম্বর বদলাতে একটিই সারি বদলাতে হয়, আর update anomaly — এক কপি বদলে অন্য কপি ভুলে যাওয়া — ঘটতেই পারে না। Normalization আসলে লেখার সঠিকতার জন্য বানানো। কিন্তু পড়ার সময় উল্টো দাম দিতে হয়: যে তথ্য আলাদা টেবিলে ভাঙা হয়েছে, প্রশ্নের উত্তরে তাকে আবার JOIN দিয়ে জোড়া লাগাতে হয়।

লাইভ SQL চলে দোকানের ডেটাবেসে (db-shop): customers (customer_id, name, city, phone, joined_on — ৮ জন), categories (category_id, name — ৪টি শ্রেণি), products (product_id, name, category_id, price, stock — ১০টি পণ্য), orders (order_id, customer_id, order_date, status — ১০টি অর্ডার; status হলো pending, shipped, delivered বা cancelled) আর order_items (order_id, product_id, quantity, unit_price — ১৬টি লাইন; unit_price হলো বিক্রির মুহূর্তের দাম)। নকশাটি normalized: অর্ডারের মোট টাকা কোথাও লেখা নেই, লাইন থেকে হিসাব করতে হয়। প্রতিবার চালালে ডেটাবেস নতুন করে তৈরি হয়, foreign key চালু থাকে। নমুনা ডেটা দেখতে এডিটরের নিচের বাক্সটি খুলুন।

একটি ছোট dashboard-এর প্রশ্ন: “শহর আর পণ্যের শ্রেণি অনুযায়ী মোট বিক্রি কত, বাতিল অর্ডার বাদে?” শহর থাকে customers-এ, শ্রেণির নাম categories-এ, টাকা order_items-এ — মাঝে orders আর products. পাঁচটি টেবিল, চারটি JOIN:

SELECT c.city, cat.name AS category,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(i.quantity * i.unit_price) AS revenue
FROM customers c
JOIN orders o        ON o.customer_id   = c.customer_id
JOIN order_items i   ON i.order_id      = o.order_id
JOIN products p      ON p.product_id    = i.product_id
JOIN categories cat  ON cat.category_id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY c.city, cat.name
ORDER BY revenue DESC;

এবার একই প্রশ্ন একটি বড় দোকানে। নিচের সংখ্যাগুলো কোনো আসল প্রতিষ্ঠানের নয়, হিসাবের জন্য ধরে নেওয়া। সাথে আরেকটি শর্ত: শুধু পরিশোধিত অর্ডার, তাই একটি payments টেবিলও লাগে — ছয়টি টেবিল, পাঁচটি JOIN:

ধাপ ৫-এর কথাটি একটি সাধারণ সত্য, প্রমাণসহ বলা যায়: base table থেকে একটি SUM-এর নির্ভুল উত্তর পেতে যোগফলে অবদান রাখা প্রতিটি সারি অন্তত একবার পড়তেই হবে। কারণ, কোনো একটি সারি না পড়লে সেই সারির মান যা-ই হোক, অ্যালগরিদমের উত্তর একই থাকত — অথচ সঠিক উত্তর বদলে যেত। তাই N সারির যোগফলের খরচ অন্তত N-এর সমানুপাতিক, কোনো index বা JOIN অ্যালগরিদম এর নিচে নামাতে পারে না। নিচে নামানোর একমাত্র পথ হলো হিসাবটি আগেই করে রাখা — অর্থাৎ redundancy.

২ · Denormalization কী — আর কী নয়

সংজ্ঞাটি সাবধানে বলা দরকার। Denormalization হলো একটি normalized নকশায়, নির্দিষ্ট পড়ার কাজ দ্রুত করতে, ইচ্ছে করে নিয়ন্ত্রিত redundancy যোগ করা — সাথে সেই কপিগুলো মিলিয়ে রাখার স্পষ্ট ব্যবস্থা। তিনটি শর্ত আছে, আর তিনটিই জরুরি:

এ থেকে একটি জরুরি পার্থক্য: unnormalized বনাম denormalized. পর্ব ২-এর ফাইল সিস্টেমের যুগে প্রতিটি বিভাগ নিজের খাতায় গ্রাহকের ঠিকানা লিখত — unnormalized, কেউ ভাবেননি কোনটি আসল। দুটি টেবিল দেখতে একই হতে পারে, কিন্তু denormalized নকশার পেছনে একটি normalized মডেল আর রক্ষণাবেক্ষণের পরিকল্পনা থাকে। denormalization তাই মূলত একটি physical সিদ্ধান্ত; যৌক্তিক মডেল normalized-ই থাকে।

আসলে আপনি প্রতিদিন redundancy ব্যবহার করেন। একটি index হলো টেবিলের কিছু কলামের একটি সাজানো কপি; materialized view একটি কোয়েরির জমানো কপি। পার্থক্য হলো, index DBMS নিজে, প্রতিটি লেখার একই transaction-এ, নির্ভুলভাবে হালনাগাদ রাখে — তাই একে কেউ “নিয়ম ভাঙা” বলে না। Redundancy-র ঝুঁকি নির্ভর করে কে কপিটি মিলিয়ে রাখার দায়িত্ব নেয় তার ওপর: DBMS, আপনার trigger, না অ্যাপের কোড। এই সূত্রটি পুরো পর্ব জুড়ে কাজে লাগবে।

৩ · কখন যুক্তিযুক্ত, কখন অকালপক্ব

Denormalization একটি ওষুধ, খাবার নয়। প্রথম নিয়ম: আগে মাপুন। কোন কোয়েরি ধীর, কতটা ধীর, দিনে কতবার চলে — এগুলো slow query log, EXPLAIN আর সময় মেপে জানুন। চারটি পরিস্থিতিতে নিয়ম ভাঙা সাধারণত যুক্তিযুক্ত:

উল্টো দিকে, সিদ্ধান্তটি অকালপক্ব যখন: কোনো মাপ নেই, শুধু “JOIN ধীর হয়” শোনা কথা; ডেটা কয়েক হাজার সারির; JOIN-এর কলামে index-ই নেই; বা কপি হালনাগাদের পরিকল্পনা নেই। সঠিক index থাকলে কয়েক লাখ সারির টেবিল থেকে অল্প কিছু সারি খোঁজার চার-পাঁচ-JOIN কোয়েরি সাধারণত মিলিসেকেন্ডের ঘরেই থাকে। নিয়ম ভাঙার আগে এই সিঁড়িটি পার হন: index (পর্ব ৪০–৪১) → কোয়েরি নতুন করে লেখা → সাধারণ view বা অ্যাপের cache → materialized view → তারপর হাতে রাখা কপি।

সিদ্ধান্তের একটি সহজ হিসাব

অনুভূতির বদলে একটি সরল সমীকরণ ভাবুন। ধরুন একটি হিসাব সেকেন্ডে r বার পড়া হয়, প্রতিবার খরচ C; জমানো মান পড়তে খরচ c (অনেক কম)। আর যে লেখাগুলো এই মান বদলায়, সেগুলো সেকেন্ডে w বার আসে, প্রতিবার কপি হালনাগাদের বাড়তি খরচ m. তাহলে লাভ হয় যদি

উপরের dashboard-এ C বিশাল (৪৫ লাখ probe), c প্রায় শূন্য, আর m একটি সারির UPDATE — তাই সামান্য r-এও লাভ। কিন্তু যে টেবিলে প্রতি সেকেন্ডে হাজারো লেখা আর দিনে দুবার পড়া, সেখানে একই কৌশল ক্ষতি। শেষ পদটি ভুলবেন না: প্রতিটি কপি নতুন কোড-পথ, নতুন test আর নতুন বাগ।

৪ · কৌশল ১ — derived মান রাখা: orders.total আর order_count

সবচেয়ে প্রচলিত কৌশল: যে মান অন্য সারি থেকে হিসাব করা যায় — পর্ব ৭-এর derived attribute — সেটি একটি কলামে রেখে দেওয়া। অর্ডারের মোট টাকা orders.total-এ, গ্রাহকের অর্ডারের সংখ্যা customers.order_count-এ। পর্ব ২৯-এ দেখেছেন, এ কাজে trigger সবচেয়ে নির্ভরযোগ্য, কারণ মূল লেখা আর কপির হালনাগাদ একই transaction-এ হয়। এই এডিটরে total কলাম যোগ আর পুরোনো সারিতে ভরা (backfill) আগে থেকেই করা আছে; এখানে তিনটি ঘটনার জন্য তিনটি trigger:

CREATE TRIGGER oi_ins AFTER INSERT ON order_items BEGIN
  UPDATE orders SET total = total + NEW.quantity * NEW.unit_price
  WHERE order_id = NEW.order_id;
END;
CREATE TRIGGER oi_del AFTER DELETE ON order_items BEGIN
  UPDATE orders SET total = total - OLD.quantity * OLD.unit_price
  WHERE order_id = OLD.order_id;
END;
CREATE TRIGGER oi_upd AFTER UPDATE ON order_items BEGIN
  UPDATE orders SET total = total - OLD.quantity * OLD.unit_price
  WHERE order_id = OLD.order_id;
  UPDATE orders SET total = total + NEW.quantity * NEW.unit_price
  WHERE order_id = NEW.order_id;
END;
INSERT INTO order_items VALUES (1006, 104, 2, 650);
UPDATE order_items SET quantity = 3 WHERE order_id = 1001 AND product_id = 102;
DELETE FROM order_items WHERE order_id = 1009 AND product_id = 110;
SELECT order_id, total FROM orders WHERE order_id IN (1001, 1006, 1009);

এই trigger-গুলো increment পদ্ধতিতে কাজ করে: পুরো যোগফল আবার না গুনে শুধু পার্থক্যটুকু যোগ-বিয়োগ। একই সারির ওপর পরপর কয়েকটি ঘটনায় মানগুলো কীভাবে চলে, ধাপে ধাপে দেখুন:

বিকল্প হলো trigger-এ প্রতিবার পুরো যোগফল আবার গোনা: SET total = (SELECT SUM(…) … WHERE order_id = NEW.order_id). অর্ডারে লাইন কম, তাই খরচ সামান্য, আর আগের কোনো ভুল পরের লেখাতেই শুধরে যায়। হাজারো সারির বড় দলে অবশ্য increment-ই বাস্তবসম্মত।

এবার দ্বিতীয় derived মান: গ্রাহকের অর্ডারের সংখ্যা। সংজ্ঞাটি আগে ঠিক করুন — এখানে বাতিলসহ সব অর্ডার গোনা হচ্ছে। “বাতিল বাদে” চাইলে status বদলের trigger-ও লাগত। সংজ্ঞা অস্পষ্ট থাকলে একই নামের সংখ্যা দুই রিপোর্টে দুই রকম আসে।

ALTER TABLE customers ADD COLUMN order_count INTEGER NOT NULL DEFAULT 0;
UPDATE customers SET order_count =
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = customers.customer_id);
CREATE TRIGGER ord_ins AFTER INSERT ON orders BEGIN
  UPDATE customers SET order_count = order_count + 1
  WHERE customer_id = NEW.customer_id;
END;
CREATE TRIGGER ord_del AFTER DELETE ON orders BEGIN
  UPDATE customers SET order_count = order_count - 1
  WHERE customer_id = OLD.customer_id;
END;
INSERT INTO orders VALUES (1011, 5, '2025-10-12', 'pending');
INSERT INTO orders VALUES (1012, 1, '2025-10-12', 'pending');
DELETE FROM orders WHERE order_id = 1012;
SELECT customer_id, name, order_count FROM customers WHERE customer_id IN (1, 5);

আগে আর পরে — কোয়েরি কতটা সরল হলো

এখন দুটি কপি একসাথে কাজে লাগান। “সবচেয়ে বেশি কেনাকাটা করা গ্রাহক” প্রশ্নে normalized পথে তিনটি টেবিল, দুটি LEFT JOIN আর COUNT(DISTINCT) লাগে। denormalized পথে order_items ছুঁতেই হয় না। এই এডিটরে orders.total আর customers.order_count দুটিই আগে থেকে ভরা; দ্বিতীয় কোয়েরিটি দুই পথের উত্তর সারি ধরে মিলিয়ে দেখে (পর্ব ২২-এর EXCEPT):

-- পরে: order_items ছোঁয়াই লাগে না
SELECT c.name, c.order_count, SUM(o.total) AS spent
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id ORDER BY spent DESC LIMIT 3;

-- দুই পথের উত্তর কি হুবহু এক? (before = normalized পথ)
WITH before AS (
  SELECT c.customer_id, COUNT(DISTINCT o.order_id) AS n,
         COALESCE(SUM(i.quantity * i.unit_price), 0) AS spent
  FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
  LEFT JOIN order_items i ON i.order_id = o.order_id
  GROUP BY c.customer_id),
after AS (
  SELECT customer_id, order_count AS n,
    (SELECT COALESCE(SUM(total), 0) FROM orders o
     WHERE o.customer_id = c.customer_id) AS spent
  FROM customers c)
SELECT (SELECT COUNT(*) FROM (SELECT * FROM before EXCEPT SELECT * FROM after)) AS only_before,
       (SELECT COUNT(*) FROM (SELECT * FROM after EXCEPT SELECT * FROM before)) AS only_after;

৫ · আরও কৌশল — কপি করা কলাম, summary টেবিল, materialized view আর JSON

কৌশল ২ · Attribute কপি করা

অর্ডারের তালিকায় গ্রাহকের নাম দেখাতে JOIN লাগে, তাই অনেকে orders-এ customer_name রাখেন। কিন্তু কপিটির উদ্দেশ্য কী? যদি এটি চালানে ছাপা নাম হয়, যা আইনত বদলানো যাবে না, তাহলে এটি আসলে ইতিহাস (অংশ ৭)। আর যদি উদ্দেশ্য শুধু JOIN বাঁচানো হয়, তাহলে গ্রাহক নাম বদলালে সব পুরোনো অর্ডারেও বদলাতে হবে — এটিই আসল redundancy, আর এর জন্য customers-এ একটি AFTER UPDATE OF name trigger লাগবে, যা একজন গ্রাহকের হাজারো অর্ডার একসাথে বদলাতে পারে।

কৌশল ৩ · Pre-joined রিপোর্ট টেবিল

প্রতিটি অর্ডার-লাইনের জন্য একটি “চওড়া” সারি — লাইনের তথ্যের পাশে গ্রাহকের শহর, পণ্যের নাম, শ্রেণির নাম, অর্ডারের তারিখ আর status — একটি টেবিলে আগে থেকে JOIN করে রাখা। একে বলে pre-joined টেবিল। বিশ্লেষক JOIN ছাড়াই যেকোনো দিক দিয়ে GROUP BY করেন। সাধারণত রাতে নতুন করে বানানো হয়; দিনের বিক্রি না থাকা রিপোর্টে প্রায়ই চলে।

কৌশল ৪ · Summary টেবিল

আরও এক ধাপ: সারিগুলো শুধু জোড়া নয়, আগে থেকে যোগ করে রাখা — summary (aggregate) টেবিল। অংশ ১-এর sales_city_cat_month এমনই। SQLite-এ materialized view নেই, তাই কাজটি হাতে করা হয়: একটি view-তে হিসাবের সংজ্ঞা, CREATE TABLE AS SELECT দিয়ে জমানো কপি, আর refresh মানে মুছে আবার ভরা:

CREATE VIEW v_cat_sales AS
  SELECT cat.name AS category, SUM(i.quantity * i.unit_price) AS revenue
  FROM order_items i
  JOIN orders o       ON o.order_id = i.order_id
  JOIN products p     ON p.product_id = i.product_id
  JOIN categories cat ON cat.category_id = p.category_id
  WHERE o.status <> 'cancelled'
  GROUP BY cat.name;
CREATE TABLE cat_sales AS SELECT * FROM v_cat_sales;   -- জমানো summary
INSERT INTO order_items VALUES (1006, 106, 1, 2200);  -- নতুন বিক্রি: Panjabi
SELECT 'stored' AS src, revenue FROM cat_sales WHERE category = 'Clothing'
UNION ALL
SELECT 'live', revenue FROM v_cat_sales WHERE category = 'Clothing';
DELETE FROM cat_sales;                                 -- refresh
INSERT INTO cat_sales SELECT * FROM v_cat_sales;
SELECT * FROM cat_sales ORDER BY revenue DESC;

কৌশল ৫ · Materialized view

একই কাজ DBMS নিজে করলে সেটিই পর্ব ২৪-এর materialized view: সংজ্ঞা আর জমানো ফল একসাথে, refresh একটি কমান্ডে। এটি এই পর্বের সূত্রের একটি সুন্দর উদাহরণ — কপি মিলিয়ে রাখার দায়িত্ব অ্যাপ থেকে DBMS-এ সরে গেল। নিচের কোড তুলনার জন্য, এই পাতায় চালানো যায় না:

CREATE MATERIALIZED VIEW cat_sales AS
  SELECT cat.name AS category, SUM(i.quantity * i.unit_price) AS revenue
  FROM order_items i JOIN orders o USING (order_id)
  JOIN products p USING (product_id)
  JOIN categories cat USING (category_id)
  WHERE o.status <> 'cancelled'
  GROUP BY cat.name;
CREATE UNIQUE INDEX ON cat_sales (category);   -- CONCURRENTLY-র জন্য জরুরি

-- রাতে বা প্রতি ঘণ্টায় (cron / pg_cron):
REFRESH MATERIALIZED VIEW CONCURRENTLY cat_sales;  -- চলার সময়ও পড়া যায়

কৌশল ৬ · JSON document-এ child সারি

শেষ কৌশলটি সবচেয়ে চরম: একটি অর্ডার আর তার সব লাইন একটি JSON document-এ — অর্ডারের পাতা খুলতে একটিই সারি পড়া, কোনো JOIN নেই। এটিই document database-এর মূল ভাবনা, যা পর্ব ৫৬–৫৭-এ বিস্তারিত দেখবেন; এখানে পর্ব ৩০-এর JSON ফাংশন দিয়ে SQLite-এ embedding:

CREATE TABLE order_docs AS
SELECT o.order_id,
       json_object('customer', c.name, 'status', o.status,
         'items', json_group_array(json_object(
            'product', p.name, 'qty', i.quantity, 'price', i.unit_price))) AS doc
FROM orders o
JOIN customers c   ON c.customer_id = o.customer_id
JOIN order_items i ON i.order_id = o.order_id
JOIN products p    ON p.product_id = i.product_id
GROUP BY o.order_id;
SELECT doc FROM order_docs WHERE order_id = 1009;
SELECT order_id, doc ->> '$.customer' AS customer,
       json_array_length(doc, '$.items') AS n_items
FROM order_docs WHERE order_id IN (1001, 1010);

৬ · কপি মিলিয়ে রাখা — trigger, dual write, batch, refresh আর CDC

প্রতিটি কপির দুটি প্রশ্ন: কীভাবে হালনাগাদ থাকবে, আর ভুল হলে কীভাবে ধরা পড়বে। প্রথমটির পাঁচটি প্রচলিত উত্তর:

• Trigger: একই transaction-এ, কপি সবসময় তাজা; লেখার খরচ বাড়ে, logic লুকানো (পর্ব ২৯)।
• অ্যাপে dual write: অ্যাপ নিজে দুটি জায়গায় লেখে — order_items-এ লাইন, orders-এ total. সহজ মনে হয়, কিন্তু ব্যর্থতার পথ অনেক (নিচের ডায়াগ্রাম)।
• Batch পুনর্গণনা: নির্ধারিত সময়ে পুরো কপি আবার হিসাব; মাঝে কপি পুরোনো, কিন্তু প্রতিটি রান আগের ভুলও মুছে দেয়।
• Materialized view refresh: batch-এরই DBMS-চালিত রূপ।
• CDC আর outbox: ডেটাবেসের transaction log (WAL বা binlog) পড়ে, বা একই transaction-এ একটি outbox টেবিলে “কী বদলেছে” লিখে, অন্য সিস্টেমে (search index, cache, warehouse) পরিবর্তন পাঠানো। কয়েক সেকেন্ড দেরিতে, কিন্তু নির্ভরযোগ্যভাবে — একে বলে eventual consistency.

Dual write-এর সমস্যাটি দেখতে সহজ, ধরতে কঠিন। অ্যাপ autocommit-এ — প্রতিটি স্টেটমেন্ট আলাদা transaction — দুটি লেখা পাঠাচ্ছে। দুটির মাঝে কিছু ঘটলে কী হয়:

এবার আসল SQLite-এ drift বানান — কোনো trigger ছাড়া, ঠিক উপরের মতো। এই এডিটরে orders.total যোগ আর backfill করা আছে, কিন্তু কোনো trigger নেই। শেষে একটি reconciliation কোয়েরি সংরক্ষিত total আর লাইন থেকে হিসাব করা মান মিলিয়ে দেখে:

-- অ্যাপের লেখা ১: নতুন লাইন — সফল
INSERT INTO order_items VALUES (1008, 104, 1, 650);
-- লেখা ২ পাঠানোর আগেই অ্যাপ crash করল:
-- UPDATE orders SET total = total + 650 WHERE order_id = 1008;

-- অ্যাডমিন হাতে একটি দাম শোধরালেন, total-এর কথা ভাবেননি
UPDATE order_items SET unit_price = 1100
WHERE order_id = 1007 AND product_id = 102;

-- reconciliation: সংরক্ষিত বনাম হিসাব করা
SELECT o.order_id, o.total AS stored,
       COALESCE(SUM(i.quantity * i.unit_price), 0) AS actual
FROM orders o LEFT JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.order_id, o.total
HAVING o.total <> COALESCE(SUM(i.quantity * i.unit_price), 0);

ধরা পড়ার পর শোধরানো। batch পুনর্গণনা সব সারি আবার লিখতে পারে, কিন্তু শুধু অমিলের সারি লিখলে লেখার চাপ কমে আর কয়টি শোধরানো হলো তাও জানা যায়। এই এডিটরে উপরের দুটি অমিল আগে থেকেই তৈরি:

-- রাতের batch: যেখানে অমিল, শুধু সেখানে আবার হিসাব
UPDATE orders
SET total = (SELECT COALESCE(SUM(quantity * unit_price), 0)
             FROM order_items i WHERE i.order_id = orders.order_id)
WHERE total <> (SELECT COALESCE(SUM(quantity * unit_price), 0)
                FROM order_items i WHERE i.order_id = orders.order_id);

-- আবার যাচাই: কয়টি অর্ডারে এখনো অমিল?
SELECT COUNT(*) AS drifted
FROM orders o
WHERE o.total <> (SELECT COALESCE(SUM(quantity * unit_price), 0)
                  FROM order_items i WHERE i.order_id = o.order_id);
SELECT order_id, total FROM orders WHERE order_id IN (1007, 1008);

অ্যাপের কোডে dual write-এর সঠিক রূপ তাহলে কেমন? Python-এর sqlite3-এ with con: ব্লক একটি transaction: ভেতরে ত্রুটি হলে দুটি লেখাই ফিরে যায় (তুলনার জন্য, এখানে চালানো যায় না):

def add_line(con, order_id, product_id, qty, price):
    with con:   # BEGIN … COMMIT, ত্রুটি হলে ROLLBACK
        con.execute("INSERT INTO order_items VALUES (?, ?, ?, ?)",
                    (order_id, product_id, qty, price))
        con.execute("UPDATE orders SET total = total + ? WHERE order_id = ?",
                    (qty * price, order_id))

৭ · ইতিহাস না redundancy — unit_price-এর শিক্ষা

order_items-এ unit_price আছে, products-এ price. একই পণ্যের দাম দুই জায়গায় — তাহলে কি নমুনা ডেটাবেসটি নিজেই denormalized? না — আর পার্থক্যটি বোঝা ডিজাইনের পরিপক্বতার চিহ্ন।

যুক্তিটি functional dependency দিয়ে (পর্ব ৩৫): products-এ product_id → price মানে “এই পণ্যের আজকের দাম”। order_items-এ (order_id, product_id) → unit_price মানে “এই অর্ডারে এই পণ্য কত টাকায় বিক্রি হয়েছিল”। দ্বিতীয়টি product_id একাই নির্ধারণ করে না — একই পণ্য দুই অর্ডারে দুই দামে বিক্রি হতে পারে, যেমন নমুনা ডেটায় Headphone 1001-এ 1200, 1007-এ 1150. তাই এটি কোনো partial বা transitive dependency নয়; টেবিলটি 3NF-এই আছে। এটি একটি আলাদা তথ্য — একটি ঐতিহাসিক snapshot. পরীক্ষাটি এক প্রশ্নে:

এবার আসল ডেটায়। প্রথম কোয়েরি খোঁজে কোন লাইনে বিক্রির দাম আজকের দাম থেকে আলাদা; তারপর Headphone-এর দাম বাড়িয়ে দুই রকম চালান তুলনা:

SELECT i.order_id, p.name, i.unit_price AS sold_at, p.price AS price_now
FROM order_items i JOIN products p ON p.product_id = i.product_id
WHERE i.unit_price <> p.price;

UPDATE products SET price = 1300 WHERE product_id = 102;  -- Headphone-এর দাম বাড়ল

-- সঠিক: বিক্রির মুহূর্তের দাম থেকে চালান
SELECT order_id, SUM(quantity * unit_price) AS invoice
FROM order_items WHERE order_id IN (1001, 1007) GROUP BY order_id;

-- ভুল নকশা: আজকের দাম দিয়ে পুরোনো চালান
SELECT i.order_id, SUM(i.quantity * p.price) AS wrong_invoice
FROM order_items i JOIN products p ON p.product_id = i.product_id
WHERE i.order_id IN (1001, 1007) GROUP BY i.order_id;

৮ · Star schema আর বড় সিস্টেমের প্যাটার্ন

প্রতিদিনের লেনদেনের ডেটাবেস — OLTP, যেখানে প্রতি সেকেন্ডে অসংখ্য ছোট লেখা — normalized থাকাই ভালো। কিন্তু বিশ্লেষণের ডেটাবেস — OLAP, যেখানে লেখা হয় রাতে একবারে আর পড়া হয় বিশাল GROUP BY দিয়ে — সেখানে denormalization ব্যতিক্রম নয়, নিয়ম। সেই নকশার নাম star schema, আর এটি থাকে data warehouse-এ (পর্ব ৫৫)।

মাঝখানে একটি fact টেবিল — প্রতিটি সারি একটি মাপা ঘটনা, যেমন একটি অর্ডার-লাইন (তার grain), সাথে সংখ্যা (quantity, amount) আর চারপাশের টেবিলের key. চারপাশে dimension টেবিল — তারিখ, পণ্য, গ্রাহক, শাখা — যাদের দিয়ে ভাগ আর ফিল্টার করা হয়। ছবিতে তারার মতো দেখায়, তাই নাম। ইচ্ছাকৃত denormalization-টি থাকে dimension-এ: পণ্যের dimension-এ শ্রেণির নাম সরাসরি একটি কলাম, আলাদা categories টেবিল নেই; তারিখের dimension-এ মাস, ত্রৈমাসিক, বছর, ছুটির দিন কি না — সব আগে থেকে লেখা। ফলে যেকোনো রিপোর্ট fact থেকে এক ধাপের JOIN. নিচের কোড তুলনার জন্য:

CREATE TABLE dim_product (
  product_key   INTEGER PRIMARY KEY,   -- warehouse-এর নিজের surrogate key
  product_id    INTEGER,               -- উৎস সিস্টেমের id
  product_name  TEXT,
  category_name TEXT                   -- কপি: categories-এ JOIN লাগে না
);
CREATE TABLE fact_sales (               -- grain: একটি অর্ডার-লাইন
  date_key INTEGER, product_key INTEGER, customer_key INTEGER,
  quantity INTEGER, amount NUMERIC(12,2)
);
SELECT d.month, p.category_name, SUM(f.amount)
FROM fact_sales f
JOIN dim_date d    ON d.date_key = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY d.month, p.category_name;

এখানে denormalization নিরাপদ কেন? কারণ অংশ ৬-এর দুটি প্রশ্নের উত্তর কাঠামোতেই আছে: warehouse-এ কেউ সরাসরি লেখেন না, সব ডেটা একটি নিয়ন্ত্রিত ETL প্রক্রিয়ায় উৎস থেকে আসে; আর dimension ছোট, তাই তাতে কপি করা নাম বদলানো সস্তা। dimension-কে আবার normalized করে ভাঙলে (পণ্য → শ্রেণি → বিভাগ আলাদা টেবিলে) নকশাটির নাম হয় snowflake schema — জায়গা কম, JOIN বেশি।

বড় প্রতিষ্ঠানগুলোতে যে প্যাটার্ন দেখা যায়

বড় সিস্টেমের প্রকাশ্য নকশায় কয়েকটি ধরন বারবার আসে (সাধারণভাবে বলা, কোনো সংখ্যা ছাড়া):

• Counter কলাম: সামাজিক মাধ্যমে লাইক বা অনুসারীর সংখ্যা সাধারণত আলাদা একটি সংখ্যা হিসেবে রাখা হয়। খুব জনপ্রিয় সারি hot row হয়ে গেলে সংখ্যাটি কয়েক ভাগে রাখা হয় (প্রশ্ন ০৫)।
• আগে থেকে সাজানো feed: কেউ পোস্ট করলে প্রত্যেক অনুসারীর timeline তালিকায় একটি রেফারেন্স ঢোকানো — পড়া সস্তা, লেখা বহুগুণ; বিপুল অনুসারীর অ্যাকাউন্টে তাই মিশ্র কৌশল।
• Search index: ই-কমার্সে পণ্যের খোঁজ সাধারণত Elasticsearch-এর মতো search engine থেকে আসে, যেখানে প্রতিটি পণ্য একটি denormalized document; হালনাগাদ আসে CDC বা event দিয়ে।
• কোয়েরি-প্রথম নকশা: Cassandra বা DynamoDB-র মতো ডেটাবেসে JOIN নেই বা সীমিত, তাই তাদের নির্মাতারাই প্রতিটি প্রশ্নের জন্য আলাদা করে ডেটা সাজাতে বলেন।

সবকটির সাধারণ সূত্র: কপি হালনাগাদ হয় লেখার পরে, অন্য প্রক্রিয়ায়, কিছুটা দেরিতে, আর নিয়মিত পুনর্নির্মাণে শোধরানো হয়। মূল সত্য থাকে একটি normalized জায়গায়।

৯ · সিদ্ধান্তের চেকলিস্ট আর খরচের টেবিল

নিয়ম ভাঙার আগে এই প্রশ্নগুলোর লিখিত উত্তর দিন। একটিরও উত্তর না থাকলে এখনো সময় হয়নি:

1. মাপ আছে? কোন কোয়েরি, কত সময়, দিনে কতবার — আর লক্ষ্য কত।
2. সস্তা পথ চেষ্টা হয়েছে? index, কোয়েরি নতুন করে লেখা, অ্যাপের cache, materialized view.
3. পড়া-লেখার অনুপাত? অংশ ৩-এর হিসাবে লাভ হয় কি না।
4. কতটা তাজা চাই? সবসময় নির্ভুল (trigger, একই transaction), না কয়েক মিনিট বা ঘণ্টা পুরোনো চলবে (batch, refresh, CDC).
5. কোন কোন লেখা মানটি বদলায়? প্রতিটি INSERT, DELETE আর প্রাসঙ্গিক UPDATE-এর তালিকা।
6. ভুল ধরা আর শোধরানো? reconciliation কোয়েরি, কত দিন পরপর, অমিলে কে সতর্কবার্তা পাবেন; পুনর্গণনার স্ক্রিপ্ট।
7. ইতিহাস না redundancy? অংশ ৭-এর প্রশ্ন — ইতিহাস হলে সেটি denormalization-ই নয়।
8. লেখা আছে? কপির সংজ্ঞা, উৎস আর মালিক দলের ডকুমেন্টেশনে।

১০ · AI-এর যুগে denormalization

“কোয়েরি ধীর, schema ঠিক করে দাও” — AI সহজেই কলাম কপি করার পরামর্শ দেয়, অথচ আপনার ডেটার মাপ, লেখার হার আর index সে জানে না। সাধারণ ভুল:

AI-এর দেওয়া denormalization চালু করার আগে — চেকলিস্ট

• আগের আর পরের কোয়েরির সময় মেপেছি; EXPLAIN দেখে নিশ্চিত হয়েছি যে index দিয়ে সমস্যাটি মেটে না
• কপিটির মান বদলায় এমন প্রতিটি INSERT, UPDATE, DELETE-এর জন্য হালনাগাদের পথ আছে
• নমুনা ডেটায় পুরোনো আর নতুন পথের ফল EXCEPT দিয়ে মিলিয়েছি — দুই দিকেই শূন্য সারি
• লাইনহীন অর্ডার, NULL আর বাতিল অর্ডার দিয়ে reconciliation test করেছি
• crash আর retry-তে কী হবে ভেবেছি; দুটি লেখা একটি transaction-এ
• ইতিহাসের কলাম (দাম, ঠিকানা, করের হার) “sync” করা হচ্ছে না