ডিজাইন কেস স্টাডি — বিশ্ববিদ্যালয় আর ই-কমার্স ডেটাবেস

১ · ডিজাইন শুরু হয় প্রশ্ন দিয়ে — requirements সংগ্রহ

মডিউল ২-এর আগের চারটি পর্বে আপনি যন্ত্রপাতি শিখেছেন: পর্ব ৭-এ entity, attribute আর relationship, পর্ব ৮-এ cardinality আর weak entity, পর্ব ৯-এ specialization আর aggregation, আর পর্ব ১০-এ ER থেকে টেবিলে রূপান্তরের নিয়ম। এই পর্বে সেই যন্ত্রগুলো দিয়ে দুটি পূর্ণ ডেটাবেস শুরু থেকে শেষ পর্যন্ত বানাব — ঠিক যেভাবে একজন পেশাদার বানান। আর সেই দুটি ডেটাবেসই এই কোর্সের সব লাইভ এডিটরে চলে: db-uni আর db-shop. অর্থাৎ এখানে আপনি জানবেন, পুরো কোর্সে যে টেবিলগুলোতে কোয়েরি চালাবেন, সেগুলো কেন এমন।

কার সাথে কথা বলবেন, কী জিজ্ঞেস করবেন

Requirements সংগ্রহ মানে যাঁরা সিস্টেমটি ব্যবহার করবেন — বিশ্ববিদ্যালয়ের রেজিস্ট্রার অফিস, বিভাগের প্রধান, শিক্ষার্থী; বা দোকানের মালিক, গুদামের কর্মী, হিসাবরক্ষক — তাঁদের কাছ থেকে জেনে নেওয়া কোন তথ্য রাখতে হবে, আর কোন প্রশ্নের উত্তর চাই। পেশাদার ডিজাইনাররা সাধারণত এই প্রশ্নগুলো করেন:

সাথে আরও দুটি প্রশ্ন: পরিমাণ (কয়টি সারি — index বাছাইয়ে লাগে) আর কে কী দেখবেন (পর্ব ৫১-এর privilege). “কখনো কি” প্রশ্নগুলো সবচেয়ে দামি: মানুষ স্বাভাবিক ঘটনার কথা বলেন, ব্যতিক্রম ভুলে যান — আর ব্যতিক্রমই ডিজাইন ভাঙে।

বিশেষ্য-ক্রিয়া কৌশল

সাক্ষাৎকারের পর চাহিদাগুলো সহজ বাক্যে লিখে ফেলুন। তারপর একটি পুরোনো কিন্তু কার্যকর কৌশল: বাক্যের বিশেষ্য (noun) সাধারণত entity বা attribute, ক্রিয়া (verb) সাধারণত relationship, আর “একটি”, “একজন”, “কয়েকটি” ধরনের পরিমাণবাচক শব্দ cardinality-র ইঙ্গিত দেয়। নিচে বিশ্ববিদ্যালয়ের একটি ছোট চাহিদা-বর্ণনায় কৌশলটি ধাপে ধাপে প্রয়োগ করুন:

ব্যবসার নিয়মের তালিকা

সাক্ষাৎকারের সবচেয়ে মূল্যবান ফল হলো নম্বর দেওয়া নিয়মের একটি তালিকা। প্রতিটি নিয়ম পরে কোনো একটি key, foreign key, NOT NULL বা CHECK হয় — অথবা স্পষ্ট লেখা থাকে “এটি ডেটাবেস নিজে পাহারা দিতে পারবে না”। বিশ্ববিদ্যালয়ের জন্য তালিকাটি এমন:

U9 (সংখ্যাটি উদাহরণ মাত্র) এক সারিতে দেখা যায় না বলে CHECK-এ যায় না — পর্ব ৬-এ দেখেছেন। তবু তালিকায় রাখুন, যাতে কেউ ধরে না নেন যে ডেটাবেস এটি পাহারা দিচ্ছে।

২ · কেস A: বিশ্ববিদ্যালয় — ER ডায়াগ্রাম থেকে db-uni

বিশেষ্য-ক্রিয়ার ফল আর নিয়মের তালিকা দিয়ে এবার ER ডায়াগ্রাম আঁকা যায়। আয়তক্ষেত্র entity, রম্বস relationship, ডিম্বাকৃতি relationship-এর attribute, আর রেখার পাশে 1, N, M হলো cardinality:

পর্ব ১০-এর mapping নিয়ম প্রয়োগ করলে এই ডায়াগ্রাম থেকে ঠিক পাঁচটি টেবিল আসে। প্রতিটি strong entity একটি টেবিল; প্রতিটি 1:N relationship “N” দিকের টেবিলে একটি foreign key; আর M:N relationship “ভর্তি” নিজেই একটি টেবিল — junction table — যার key দুই দিকের key আর relationship-এর নিজের key-attribute মিলে। নিচের এডিটরের “নমুনা ডেটা” বাক্সে পুরো db-uni দেখতে পাবেন; এখানে প্রতিটি টেবিলের সিদ্ধান্তের কারণ:

খেয়াল করুন, একই ডেটাবেসে দুই রকম key: students-এ সংখ্যা, courses-এ অর্থবহ কোড — পর্ব ৬-এর মাপকাঠি (স্থির, ছোট, কখনো NULL নয়) প্রতিটি টেবিলে আলাদা করে প্রয়োগ হয়েছে। আর mentor-এর জন্য আলাদা টেবিল লাগেনি: 1:N বলে “N” দিকের সারিতে একটি কলামই যথেষ্ট। নিয়ম “একাধিক mentor” হলে সম্পর্কটি M:N হতো, তখন আলাদা টেবিল (mentor_id, mentee_id) লাগত।

৩ · কেস A-র তিনটি কঠিন সিদ্ধান্ত — আর বাতিল হওয়া বিকল্প

সিদ্ধান্ত ১ — term কেন primary key-র অংশ

enrollments-এর key হিসেবে প্রথমে মনে আসে (student_id, course_id): “এই শিক্ষার্থী এই কোর্সে আছেন”। নিয়ম U7 না জানলে এটিই সঠিক মনে হয়। নিচের ডায়াগ্রামে একই ঘটনা দুটি নকশায় চালিয়ে দেখুন — Fahim Karim (2203) 2023-Spring-এ EEE101-এ F পেয়ে পরের বছর আবার কোর্সটি নিলেন:

এবার আসল db-uni-তে একই পরীক্ষা চালান। Fahim-এর 2023-Spring-এর F সারিটি নমুনা ডেটাতেই আছে:

-- Fahim Karim (2203)-এর বর্তমান রেকর্ড
SELECT * FROM enrollments WHERE student_id = 2203;

-- পরের term-এ retake: term আলাদা, তাই ঢুকবে
INSERT INTO enrollments VALUES (2203, 'EEE101', '2024-Spring', 'B');
SELECT * FROM enrollments WHERE student_id = 2203;

-- একই term-এ আবার: তিনটি key-কলামই মেলে, বাতিল
INSERT INTO enrollments VALUES (2203, 'EEE101', '2024-Spring', 'A');

আরেকটি বিকল্প: surrogate enrollment_id আর পাশে UNIQUE (student_id, course_id, term). এটিও সঠিক, অনেক ORM-ভিত্তিক প্রজেক্টে এভাবেই হয় (পর্ব ৫০) — শুধু UNIQUE-টি ভুলে গেলে একই term-এ দুবার ভর্তি চুপচাপ ঢুকবে। db-uni-তে কোনো টেবিল enrollments-এর দিকে আঙুল তোলে না, তাই আলাদা id লাগেনি।

সিদ্ধান্ত ২ — grade কেন NULL হতে পারে

term চলাকালীন ভর্তি আছে, কিন্তু ফল নেই। Dilruba Khatun (2201)-এর 2024-Fall-এর CSE301 সারিতে grade NULL — অর্থাৎ “এখনো জানা নেই”। বিকল্প ছিল '' বা 'N/A' লেখা। দুটিই বাতিল: '' একটি মান, তাই “grade আছে কি না” বোঝাতে দুই রকম খালি (NULL আর '') আলাদা করতে হয়; আর 'N/A' দেখতে একটি grade-এর মতো, কেউ ভুল করে এটিকে গড়ে ধরতে পারেন। NULL-এর সুবিধা হলো aggregate ফাংশন (AVG, COUNT(grade)) এটিকে নিজে থেকেই বাদ দেয় — পর্ব ১৬ আর ১৮-এ বিস্তারিত।

সতর্কতা: NULL-এর অর্থ একটিই হওয়া চাই। “ফল আসেনি” আর “কোর্স ছেড়েছেন” দুটিই NULL হলে আর আলাদা করা যায় না — তাই W (withdrawn) আসল মান হিসেবে রাখুন। আর db-uni-র grade-এ কোনো CHECK নেই, তাই 'Z'-ও আটকায় না; অংশ ৭-এর রিভিউতে এটি একটি পর্যবেক্ষণ।

সিদ্ধান্ত ৩ — cgpa রাখা, নাকি প্রতিবার হিসাব করা

CGPA আসলে enrollments-এর grade আর courses-এর credit থেকে হিসাব করা যায়। এমন attribute-কে বলে derived attribute. db-uni ইচ্ছে করেই এটিকে students-এ রেখেছে — যাতে তালিকা দেখাতে প্রতিবার হিসাব না লাগে, আর SQL শেখার প্রথম পর্বগুলোতে একটি সহজ সংখ্যা-কলাম থাকে। কিন্তু একই সত্য দুই জায়গায় থাকলে প্রশ্ন ওঠে: কোনটি ঠিক? নিচে প্রতিটি grade-কে একটি point-এ বদলে credit দিয়ে ভারিত গড় বের করা হয়েছে (অনেক বাংলাদেশি বিশ্ববিদ্যালয়ে প্রচলিত একটি স্কেল ধরে নেওয়া: A+ = 4.00, A = 3.75, A- = 3.50 … F = 0). CASE আর GROUP BY এখনই না বুঝলেও চলবে — পর্ব ১৬ আর ১৮-এ আসবে; শুধু ফলের দুটি কলাম তুলনা করুন:

SELECT s.student_id, s.name, s.cgpa AS stored,
       ROUND(SUM(c.credits * CASE e.grade
             WHEN 'A+' THEN 4.0  WHEN 'A' THEN 3.75 WHEN 'A-' THEN 3.5
             WHEN 'B+' THEN 3.25 WHEN 'B' THEN 3.0  WHEN 'B-' THEN 2.75
             WHEN 'C+' THEN 2.5  WHEN 'C' THEN 2.25 WHEN 'D'  THEN 2.0
             ELSE 0 END) / SUM(c.credits), 2) AS computed
FROM students s
JOIN enrollments e ON e.student_id = s.student_id AND e.grade IS NOT NULL
JOIN courses c ON c.course_id = e.course_id
GROUP BY s.student_id, s.name, s.cgpa
ORDER BY s.student_id;

তিনটি পথ খোলা, প্রতিটির দাম আলাদা:

• রাখবেন না, দরকারে হিসাব করুন — একটি view (পর্ব ২৪) দিয়ে উপরের কোয়েরিটি নাম দিয়ে রাখুন। সত্যের উৎস একটিই; খরচ হলো প্রতিবার হিসাব।
• রাখুন, কিন্তু স্বয়ংক্রিয়ভাবে হালনাগাদ করুন — grade বদলালে trigger (পর্ব ২৯) বা একই transaction-এ (পর্ব ৪৪) cgpa আবার হিসাব। পড়া দ্রুত, লেখা একটু ধীর।
• রাখুন, আর স্পষ্ট লিখে দিন এটি একটি “স্ন্যাপশট” — যেমন প্রতি term শেষে রেজিস্ট্রার অফিসের প্রকাশ করা অফিসিয়াল cgpa, সাথে cgpa_as_of term. তখন অমিল ভুল নয়, পরিকল্পিত।

যেটিই বাছুন, সিদ্ধান্তটি লিখে রাখুন। ইচ্ছাকৃতভাবে একই তথ্য দুই জায়গায় রাখাকে বলে denormalization — পর্ব ৩৮-এ এর পুরো হিসাব দেখবেন।

৪ · কেস B: অনলাইন দোকান — 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 — ১০টি অর্ডার) আর order_items (order_id, product_id, quantity, unit_price — ১৬টি লাইন)। orders আর products-এর মধ্যে M:N সম্পর্ক (“একটি অর্ডারে অনেক পণ্য, একটি পণ্য অনেক অর্ডারে”), আর order_items হলো তার junction table — ঠিক enrollments-এর মতো। পুরো ডেটা এডিটরের নিচের “নমুনা ডেটা” বাক্সে।

কেন unit_price কপি করা হয় — ঐতিহাসিক দাম

প্রথম দেখায় order_items.unit_price বাড়তি মনে হয়: দাম তো products.price-এই আছে, JOIN করলেই পাওয়া যায়। কিন্তু দুটি কলাম আলাদা সত্য ধরে রাখে। products.price হলো “আজকের দাম”, যা মালিক যেকোনো দিন বদলান। unit_price হলো “ওই অর্ডারে গ্রাহক কত দিয়ে কিনেছিলেন” — একবার লেখা হলে কখনো বদলায় না। মুদি দোকানের খাতার কথা ভাবুন: গত মাসে চালের কেজি যত টাকা ছিল, সেই হিসাবে বাকি লেখা আছে; আজ দাম বাড়লে পুরোনো বাকির অঙ্ক বদলায় না।

-- কোন লাইনে বিক্রির দাম আজকের দাম থেকে আলাদা?
SELECT oi.order_id, p.name, oi.unit_price AS sold_at, p.price AS price_now
FROM order_items oi JOIN products p ON p.product_id = oi.product_id
WHERE oi.unit_price <> p.price;

-- মোবাইলের দাম বাড়ল
UPDATE products SET price = 16000 WHERE product_id = 101;

-- অর্ডার 1001-এর চালান: কপি করা দামে, আর দাম কপি না করলে যা হতো
SELECT order_id, SUM(quantity * unit_price) AS invoice_total
FROM order_items WHERE order_id = 1001 GROUP BY order_id;
SELECT oi.order_id, SUM(oi.quantity * p.price) AS if_price_not_copied
FROM order_items oi JOIN products p ON p.product_id = oi.product_id
WHERE oi.order_id = 1001 GROUP BY oi.order_id;

এটি redundancy নয়: unit_price নির্ভর করে (order_id, product_id)-এর ওপর, শুধু product_id-এর ওপর নয় (ভাবনার প্রশ্ন ২)। একই কারণে ডেলিভারি ঠিকানা, ভ্যাটের হার আর ছাড়ও অর্ডারের সময়কার মান হিসেবে কপি হয়।

status-এর CHECK আর stock-এর CHECK

status: মুক্ত লেখা হলে কেউ লিখবেন 'Delivered', কেউ 'delivred', কেউ 'done' — তারপর “কয়টি অর্ডার পৌঁছেছে?” প্রশ্নের উত্তর তিনটি বানানে ভাগ হয়ে যাবে। CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')) শব্দগুলোকে চারটিতে বেঁধে দেয়; SQLite-এ তুলনাটি ছোট-বড় হাতের অক্ষর আলাদা ধরে, তাই 'Delivered'-ও আটকায়। বিকল্প ছিল একটি order_statuses টেবিল আর তার দিকে FK — তালিকা ঘন ঘন বদলালে সেটি ভালো। কিন্তু অবস্থা বদলের ক্রম (“cancelled থেকে delivered নয়”) কোনোটিই পারে না; আগের আর নতুন মান দুটোই দেখতে trigger লাগে (পর্ব ২৯)।

stock: CHECK (stock >= 0) শেষ পাহারাদার। একই মুহূর্তে দুজন ক্রেতা শেষ Smart Watch-টি কিনতে গেলে দুজনই “১টি আছে” দেখতে পারেন; UPDATE যদি stock = stock - 1 ধরনে লেখা হয়, তবে দ্বিতীয় UPDATE-এ স্টক −1 হতে গেলে CHECK সেটি বাতিল করবে। (অ্যাপ যদি আগে পড়া মান থেকে হিসাব করে stock = 0 লেখে, দুটি লেখাই 0 বসাবে আর CHECK কিছুই ধরবে না — এজন্যই আপেক্ষিক UPDATE বা lock লাগে।) একসাথে-আসা অনুরোধের পুরো গল্প পর্ব ৪৬ আর ৪৭-এ।

৫ · নতুন চাহিদা এলে — ডিজাইন ধাপে ধাপে বাড়ানো

দোকান চালু হওয়ার ছয় মাস পর মালিক চারটি নতুন জিনিস চাইলেন: পণ্যের রিভিউ, গ্রাহকের একাধিক ঠিকানা, bKash/Nagad/কার্ড/ক্যাশ-অন-ডেলিভারিতে পেমেন্ট (ফেরতসহ), আর কুপন। পেশাদারের কাজ এখানে প্রতিটি চাহিদার জন্য আবার অংশ ১-এর প্রশ্নগুলো করা — বিশেষ করে “কখনো কি…?”। নিচে পুরো যাত্রাটি এক নজরে, তারপর প্রতিটি আলাদা করে:

৫.১ · রিভিউ — composite primary key দিয়ে “একটিই”

CREATE TABLE reviews (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  product_id  INTEGER NOT NULL REFERENCES products(product_id),
  rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  body        TEXT,                    -- শুধু তারকা দিলেও চলে
  created_at  TEXT NOT NULL,
  PRIMARY KEY (customer_id, product_id)   -- এক গ্রাহক, এক পণ্য, একটি রিভিউ
);

নিচের এডিটর db-shop-এর সাথে এই টেবিল আর পাঁচটি রিভিউ আগে থেকে তৈরি করে নেয়: Rahim (1) → Mobile Phone 5★, Karima (2) → DSA Book 4★ আর Headphone 3★, Nusrat (4) → Panjabi 5★, Sabbir (3) → Smart Watch 1★।

-- প্রতিটি পণ্যের গড় রেটিং
SELECT p.name, COUNT(r.rating) AS reviews, AVG(r.rating) AS avg_rating
FROM products p JOIN reviews r ON r.product_id = p.product_id
GROUP BY p.product_id, p.name ORDER BY p.product_id;

-- মত বদলালে: নতুন সারি নয়, UPDATE
UPDATE reviews SET rating = 4, body = 'Good, battery ok'
WHERE customer_id = 1 AND product_id = 101;
SELECT * FROM reviews WHERE customer_id = 1;

-- একই পণ্যে দ্বিতীয় রিভিউ: বাতিল
INSERT INTO reviews VALUES (1, 101, 2, 'Second review', '2025-10-12');

৫.২ · একাধিক ঠিকানা — আর “একটিই default”

customers-এ address1, address2, address3 যোগ করা প্রলোভনের মতো, কিন্তু চতুর্থ ঠিকানায় আবার schema বদলাতে হবে। সঠিক পথ customers থেকে 1:N একটি টেবিল। “একজনের সর্বোচ্চ একটি default” নিয়মের জন্য UNIQUE (customer_id) চলবে না (তাহলে একটিই ঠিকানা রাখা যেত), আর UNIQUE (customer_id, is_default)-ও চলবে না (তাহলে non-default ঠিকানাও একটির বেশি রাখা যেত না)। সমাধান হলো partial unique index — শুধু is_default = 1 সারিগুলোর ওপর অনন্যতা:

CREATE TABLE addresses (
  address_id  INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id) ON DELETE CASCADE,
  label       TEXT NOT NULL,           -- 'Home', 'Office' …
  line1       TEXT NOT NULL,
  district    TEXT NOT NULL,
  is_default  INTEGER NOT NULL DEFAULT 0 CHECK (is_default IN (0, 1))
);
CREATE UNIQUE INDEX one_default_address ON addresses(customer_id) WHERE is_default = 1;

এখানে ON DELETE CASCADE ইচ্ছাকৃত: গ্রাহক না থাকলে তাঁর ঠিকানার কোনো আলাদা অর্থ নেই — পর্ব ৬-এর মাপকাঠি। এডিটরটি টেবিল, index আর তিনটি ঠিকানা আগে বানিয়ে নেয়: Rahim (1)-এর Home (default) আর Office, Karima (2)-এর Home (default).

-- তৃতীয় ঠিকানা, default নয়: চলবে
INSERT INTO addresses VALUES (4, 1, 'Village', 'Puthia', 'Rajshahi', 0);

SELECT customer_id, COUNT(*) AS addresses, SUM(is_default) AS defaults
FROM addresses GROUP BY customer_id;

-- Rahim-এর দ্বিতীয় default: index আটকাবে
INSERT INTO addresses VALUES (5, 1, 'Hostel', 'Shahbag', 'Dhaka', 1);

৫.৩ · পেমেন্ট আর আংশিক ফেরত

বাংলাদেশের অনলাইন দোকানে পেমেন্টের পথ অনেক: bKash, Nagad, কার্ড, আর ক্যাশ-অন-ডেলিভারি (cod). মূল প্রশ্নগুলোর উত্তর থেকে ডিজাইন: একটি অর্ডারে একাধিক পেমেন্ট হতে পারে (1:N); ফেরত আংশিক হতে পারে আর একটি নির্দিষ্ট পেমেন্টের বিপরীতে হয়; গেটওয়ে যে transaction ID দেয়, সেটি কখনো দুবার আসবে না; আর টাকা রাখা হবে পয়সায় পূর্ণসংখ্যা হিসেবে (কেন, তা অংশ ৭-এ)।

CREATE TABLE payments (
  payment_id   INTEGER PRIMARY KEY,
  order_id     INTEGER NOT NULL REFERENCES orders(order_id),     -- CASCADE নয়
  method       TEXT NOT NULL CHECK (method IN ('bkash', 'nagad', 'card', 'cod')),
  kind         TEXT NOT NULL CHECK (kind IN ('charge', 'refund')),
  amount_paisa INTEGER NOT NULL CHECK (amount_paisa > 0),          -- 1 টাকা = 100
  refund_of    INTEGER REFERENCES payments(payment_id),
  provider_ref TEXT UNIQUE,            -- গেটওয়ের TrxID; cod-এ NULL
  paid_at      TEXT NOT NULL,          -- UTC, যেমন '2025-09-01T10:15:00Z'
  CHECK ((kind = 'refund') = (refund_of IS NOT NULL))
);

শেষ CHECK-টি খেয়াল করুন: “refund হলে refund_of থাকতেই হবে, charge হলে থাকবে না” — দুই কলামের সম্পর্ক এক সারির ভেতরে, তাই CHECK-এ লেখা যায়। আর amount সবসময় ধনাত্মক; দিক বোঝায় kind. এতে “−1200” আর “1200 refund” — একই জিনিসের দুই রকম লেখা তৈরি হয় না। অর্ডার 1001-এর পেমেন্টের ইতিহাস ধাপে ধাপে:

-- প্রতিটি অর্ডারের মোট দাম, পরিশোধ আর ফেরত (টাকায়)
SELECT o.order_id, o.status,
  (SELECT SUM(quantity * unit_price) FROM order_items oi
    WHERE oi.order_id = o.order_id) AS total_taka,
  COALESCE(SUM(CASE p.kind WHEN 'charge' THEN p.amount_paisa END), 0) / 100.0 AS charged,
  COALESCE(SUM(CASE p.kind WHEN 'refund' THEN p.amount_paisa END), 0) / 100.0 AS refunded
FROM orders o LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.order_id IN (1001, 1003, 1009)
GROUP BY o.order_id, o.status;

৫.৪ · কুপন

কুপনের প্রশ্ন: “এক অর্ডারে কি একাধিক কুপন চলে?” (না), “একটি কুপন কতবার ব্যবহার করা যায়?” (সীমা আছে), “মেয়াদ?” (শুরু আর শেষের তারিখ)। কুপনের হার পরে বদলাতে পারে, তাই প্রতিটি অর্ডারে কত টাকা ছাড় হয়েছিল তা কপি হয় — আবার সেই unit_price-এর যুক্তি:

CREATE TABLE coupons (
  code        TEXT PRIMARY KEY,                     -- 'EID25'
  percent_off INTEGER NOT NULL CHECK (percent_off BETWEEN 1 AND 90),
  valid_from  TEXT NOT NULL,
  valid_to    TEXT NOT NULL,
  max_uses    INTEGER CHECK (max_uses > 0),          -- NULL = সীমাহীন
  CHECK (valid_from <= valid_to)
);
CREATE TABLE order_coupons (
  order_id       INTEGER PRIMARY KEY REFERENCES orders(order_id),  -- এক অর্ডারে একটি
  code           TEXT NOT NULL REFERENCES coupons(code),
  discount_paisa INTEGER NOT NULL CHECK (discount_paisa > 0)
);

order_coupons-এ order_id নিজেই primary key — এটিই “এক অর্ডারে একটি কুপন”; নিয়ম বদলালে key হবে (order_id, code). orders-এ একটি coupon_code কলামও চলত — এটি রুচির কাছাকাছি সিদ্ধান্ত। আর “max_uses অতিক্রম নয়” অনেক সারি গোনার নিয়ম, CHECK-এ যায় না।

৬ · নমুনা ডেটায় ডিজাইন যাচাই — ব্যবসার প্রশ্নের উত্তর মেলে কি?

কাগজে নিখুঁত ডিজাইনও আসল প্রশ্নে আটকে যেতে পারে। তাই সাক্ষাৎকারের প্রশ্নগুলোর জন্য বাস্তবসম্মত নমুনা ডেটায় কোয়েরি লিখে দেখুন। নিয়মটি সহজ: যে প্রশ্নের উত্তর দিতে কোয়েরিতে কসরত লাগে বা ডেটাই নেই, সেখানে ডিজাইনে ফাঁক। দোকানের মালিকের দুটি প্রশ্ন: “শ্রেণি অনুযায়ী আয় কত?” আর “কোন পণ্য কখনো বিক্রি হয়নি?”

-- শ্রেণিভিত্তিক আয় (বাতিল অর্ডার বাদে)
SELECT c.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o     ON o.order_id = oi.order_id AND o.status <> 'cancelled'
JOIN products p   ON p.product_id = oi.product_id
JOIN categories c ON c.category_id = p.category_id
GROUP BY c.name ORDER BY revenue DESC;

-- কখনো বিক্রি হয়নি এমন পণ্য
SELECT product_id, name, stock FROM products
WHERE product_id NOT IN (SELECT product_id FROM order_items);

এবার অংশ ৫.১-এর বাকি প্রশ্ন: “শুধু ক্রেতারাই রিভিউ দেবেন” — ডিজাইন কি এটি পাহারা দেয়? reviews-এর FK শুধু বলে গ্রাহক আর পণ্য আছে; কেনা হয়েছিল কি না বলে না, আর রিভিউতে order_id নেই বলে order_items-এর দিকে FK-ও বসে না। তাই কোয়েরি দিয়ে যাচাই:

-- প্রতিটি রিভিউ: গ্রাহক কি পণ্যটি delivered অর্ডারে পেয়েছিলেন?
SELECT r.customer_id, r.product_id, r.rating,
       EXISTS (SELECT 1 FROM orders o
               JOIN order_items oi ON oi.order_id = o.order_id
               WHERE o.customer_id = r.customer_id
                 AND oi.product_id = r.product_id
                 AND o.status = 'delivered') AS verified
FROM reviews r ORDER BY r.customer_id, r.product_id;

৭ · ডিজাইন রিভিউ চেকলিস্ট — কোড রিভিউর মতো schema রিভিউ

পেশাদার দলে schema-ও কোডের মতো রিভিউ হয়। নিচের তালিকাটি db-shop আর db-uni-তে প্রয়োগ করা হলো — নমুনা ডেটাবেস দুটি শেখানোর জন্য সরল রাখা, তাই কিছু পর্যবেক্ষণ আসবেই।

পর্যবেক্ষণ ১ — composite primary key-তে NULL (SQLite-এর ফাঁদ)

পর্ব ৬-এ দেখেছেন, SQLite-এ INTEGER PRIMARY KEY ছাড়া অন্য primary key-তে NULL ঢুকে যেতে পারে। enrollments-এর key composite, আর student_id ও course_id-এ আলাদা NOT NULL লেখা নেই। FK-ও আটকাবে না, কারণ NULL কোনো কিছুর দিকে আঙুল তোলে না:

-- শিক্ষার্থী ছাড়া একটি "ভর্তি" — দুবার!
INSERT INTO enrollments VALUES (NULL, 'CSE101', '2024-Fall', NULL);
INSERT INTO enrollments VALUES (NULL, 'CSE101', '2024-Fall', NULL);

SELECT student_id, course_id, term FROM enrollments
WHERE student_id IS NULL;

পর্যবেক্ষণ ২ — foreign key কলামে index

Foreign key-তে সব DBMS নিজে index বানায় না (MySQL InnoDB বানায়; PostgreSQL আর SQLite না)। index না থাকলে “Rahim-এর সব অর্ডার” খুঁজতে বা গ্রাহক মোছার আগের যাচাইয়ে পুরো orders টেবিল পড়তে হয় — এক কোটি সারিতে টের পাবেন। সুপারিশ:

CREATE INDEX idx_orders_customer     ON orders(customer_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);  -- PK-র প্রথম কলাম order_id, তাই এটি আলাদা লাগে
CREATE INDEX idx_products_category   ON products(category_id);
CREATE INDEX idx_enrollments_course  ON enrollments(course_id);
CREATE INDEX idx_payments_order      ON payments(order_id);

order_items(order_id)-এর আলাদা index লাগে না: composite primary key (order_id, product_id)-এর index প্রথম কলাম দিয়ে খোঁজায় কাজে লাগে। কিন্তু product_id দিয়ে খোঁজায় লাগে না — ফোনবুক নামের প্রথম অক্ষরে সাজানো থাকলে শুধু পদবি দিয়ে খোঁজার মতো। index-এর পুরো হিসাব পর্ব ৪০ আর ৪১-এ।

পর্যবেক্ষণ ৩ — টাকা কেন REAL-এ রাখা ঝুঁকিপূর্ণ

REAL (অন্য DBMS-এ FLOAT বা DOUBLE) হলো binary floating-point সংখ্যা। কম্পিউটার এটিকে ২-এর ঘাতে রাখে, তাই ০.১-এর মতো দশমিক ভগ্নাংশ ঠিকঠাক রাখতে পারে না — যেমন দশমিকে ১/৩ = ০.৩৩৩… কোথাও না কোথাও কেটে ফেলতে হয়। টাকার হিসাবে পয়সার এই ছোট ভুলগুলো জমে, তুলনায় গোলমাল করে, আর পয়সায় রূপান্তরের সময় এক পয়সা হারায়:

-- ০.১ টাকা + ০.২ টাকা কি ০.৩ টাকা?
SELECT 0.1 + 0.2 AS sum_real, 0.1 + 0.2 = 0.3 AS is_equal;

-- ১.১৫ টাকাকে পয়সায় বদলানো
SELECT 1.15 * 100 AS x,
       CAST(1.15 * 100 AS INTEGER) AS paisa_truncated,
       CAST(ROUND(1.15 * 100) AS INTEGER) AS paisa_rounded;

-- পয়সায় পূর্ণসংখ্যা: কোনো ভুল নেই
SELECT 10 + 20 AS paisa_sum, (10 + 20) = 30 AS is_equal;

CREATE TABLE products (
  product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  price      NUMERIC(12, 2) NOT NULL CHECK (price > 0),   -- হুবহু দশমিক
  stock      INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

db-shop তাহলে REAL কেন? শুধু সরলতার জন্য: প্রথম পর্বগুলোতে 15000-এর মতো সংখ্যা পড়া সহজ, আর এর সব দাম পূর্ণ টাকায়, যা REAL-এ হুবহু থাকে। কিন্তু বাস্তবে ভ্যাট বা শতাংশ-ছাড় হিসাব শুরু হলেই পয়সা আসবে — তখন REAL নয়।

পর্যবেক্ষণ ৪ — তারিখ, সময় আর টাইমজোন

বাংলাদেশের সময় UTC-এর চেয়ে ৬ ঘণ্টা এগিয়ে। সার্ভার UTC-তে চললে আর সময়গুলো মিশে গেলে রাত ১২টা থেকে সকাল ৬টার অর্ডার “আগের দিনের” রিপোর্টে চলে যায়। পেশাদার নিয়ম: মুহূর্ত রাখুন UTC-তে, দেখানোর সময় স্থানীয় সময়ে বদলান — PostgreSQL-এ TIMESTAMPTZ, SQLite-এ ISO লেখা '2025-09-01T10:15:00Z' (Z মানে UTC). কিন্তু শুধু তারিখ — জন্মদিন, joined_on, term — টাইমজোনে বদলাবেন না। কারণগুলোর বিস্তারিত ভাবনার প্রশ্ন ৫-এ।

৮ · ডিজাইনের চারটি পরিচিত “দুর্গন্ধ”

সফটওয়্যারে “code smell” মানে এমন লক্ষণ, যা দেখলেই সন্দেহ হয় ভেতরে সমস্যা আছে। ডেটাবেস ডিজাইনেও এমন কিছু পরিচিত anti-pattern আছে — শুরুতে সহজ মনে হয়, পরে চড়া দাম নেয়। চারটি সবচেয়ে প্রচলিত:

আনুষ্ঠানিক যন্ত্র: normalization

এই পর্বের সিদ্ধান্তগুলো এসেছে অভিজ্ঞতা আর প্রশ্ন থেকে। কিন্তু “কোনো তথ্য কি দুই জায়গায় আছে?” — এর গাণিতিক উত্তরও আছে। পর্ব ৩৫-এ শিখবেন functional dependency (কোন কলাম জানলে কোন কলাম নির্ধারিত হয়), আর পর্ব ৩৬-এ normalization — 1NF, 2NF, 3NF — যা দিয়ে উপরের “এক বিশাল টেবিল” কেন খারাপ আর কীভাবে ভাঙতে হবে, তা প্রমাণসহ দেখানো যায়। পর্ব ৩৭-এ BCNF আর ভাঙার পর তথ্য না হারানোর (lossless) পরীক্ষা, আর পর্ব ৩৮-এ কখন cgpa বা unit_price-এর মতো ইচ্ছাকৃত নকল রাখা যুক্তিসংগত। ER দিয়ে আঁকা ভালো ডিজাইন সাধারণত শুরু থেকেই 3NF-এর কাছাকাছি থাকে; normalization সেটিকে যাচাই করার আনুষ্ঠানিক যন্ত্র।

৯ · AI-এর যুগে ডেটাবেস ডিজাইন

AI কয়েক সেকেন্ডে দশটি সুন্দর CREATE TABLE দেয়, আর সেগুলো চলবেও। কিন্তু AI আপনার দোকানের মালিকের সাথে কথা বলেনি — “কখনো কি…?” প্রশ্নের উত্তর না জেনে গড়পড়তা ধরে নেয়। প্রায়ই যা পাবেন:

AI-এর দেওয়া schema হাতে পেলে — যাচাইয়ের চেকলিস্ট

• নিজের ব্যবসার নিয়মের তালিকার প্রতিটি নিয়ম schema-র কোন অংশে রক্ষিত, তা চিহ্নিত করেছি
• যা বদলায় (দাম, ঠিকানা, ছাড়), তার লেনদেনের সময়কার মান কপি হচ্ছে
• টাকা INTEGER পয়সায় বা DECIMAL-এ; সময় UTC-তে
• প্রতিটি composite key-র কলামে NOT NULL, প্রতিটি FK কলামে index
• নমুনা ডেটা ঢুকিয়ে ব্যবসার প্রতিটি রিপোর্টের কোয়েরি চালিয়েছি, সারির সংখ্যা হাতে মিলিয়েছি
• প্রতিটি constraint ভাঙার একটি পরীক্ষা চালিয়েছি — ত্রুটি না এলে constraint কাজ করছে না
• কমা-দেওয়া তালিকা, EAV, এক বিশাল টেবিল আর polymorphic FK খুঁজে দেখেছি