Database Design: Table, Relation, Index

Code বদলানো সহজ, database এর গঠন বদলানো কঠিন। একবার লাখ লাখ row জমে গেলে একটা column এর type বদলাতে বা একটা table কে দুই ভাগ করতে ঘণ্টার পর ঘণ্টা লাগে, আর ভুল হলে data হারায়। তাই শুরুতেই ভালো schema design করা backend developer এর সবচেয়ে মূল্যবান দক্ষতার একটা। এই অধ্যায়ে দেখবো table আর সম্পর্ক কীভাবে সাজাতে হয়, normalization কী, আর index কীভাবে query দ্রুত করে।

#ধাপ ১: জিনিসগুলো খুঁজে বের করো

Requirement পড়ে noun (বিশেষ্য) গুলো দাগাও। একটা food delivery app এর জন্য:

"Customer কোনো restaurant এর menu থেকে কয়েকটা item বেছে order দেয়। Order একটা address এ যায়, একজন rider নিয়ে যায়। Customer payment করে আর পরে review দিতে পারে।"

প্রতিটা noun সম্ভাব্য একটা table: customers, restaurants, menu_items, orders, order_items, addresses, riders, payments, reviews।

#ধাপ ২: সম্পর্ক ঠিক করো

#order_items এ দাম কেন আবার রাখি?

menu_items এ তো দাম আছেই। তবু order_items.unit_price রাখি কারণ দাম বদলায়। আজ burger ২৫০ টাকা, আগামী মাসে ২৮০। পুরনো order এর হিসাব যেন বদলে না যায়, তাই order এর সময়ের দাম আলাদা রেখে দাও। একই কারণে delivery address এর একটা copy order এ রাখা হয়: user পরে ঠিকানা বদলালেও পুরনো order এর ঠিকানা ঠিক থাকা উচিত।

এটা একটা গুরুত্বপূর্ণ নীতি: যে data একটা মুহূর্তের রেকর্ড (order, invoice, transaction), সেটা তখনকার মতো করে রাখো।

#Normalization: একই তথ্য একবার

খারাপ design:

সমস্যা:

• Rahim phone বদলালে প্রতিটা order এ বদলাতে হবে। একটায় ভুলে গেলে data অসঙ্গত।
• items একটা লেখা, এখান থেকে "এই মাসে কয়টা Kacchi বিক্রি হলো" বের করা প্রায় অসম্ভব।
• নতুন restaurant যোগ করা যায় না যতক্ষণ না তার কোনো order হয়।

Normalization মানে data কে এমনভাবে ভাগ করা যাতে প্রতিটা তথ্য এক জায়গায় থাকে। সহজ নিয়ম তিনটা (1NF, 2NF, 3NF এর সারাংশ):

1. প্রতিটা ঘরে একটাই value। "Kacchi x2, Borhani x1" না, আলাদা row।
2. প্রতিটা table একটা জিনিস নিয়ে। Customer এর তথ্য customer table এ, order table এ শুধু customer_id।
3. একই তথ্য কয়েক জায়গায় না (উপরের "মুহূর্তের রেকর্ড" ব্যতিক্রম ছাড়া)।

#Denormalization: জেনেবুঝে নিয়ম ভাঙা

পুরোপুরি normalized database এ পড়ার সময় অনেক JOIN লাগে, যা বড় data তে ধীর। তাই কখনো কখনো ইচ্ছা করে কিছু তথ্য দ্বিতীয়বার রাখা হয়:

• restaurants.rating_avg আর rating_count: প্রতিবার সব review গুনে গড় না করে আগে থেকে হিসাব করে রাখা
• posts.like_count: প্রতিবার COUNT(*) না করে

দাম হলো: দুই জায়গা মিলিয়ে রাখার দায়িত্ব এখন তোমার (review যোগ হলে rating আপডেট করা)। নিয়ম: প্রথমে normalized design করো, মেপে দেখো কোথায় ধীর, তারপর সেখানে denormalize করো।

#ধাপ ৩: Column এর type ঠিকভাবে বাছাই

#Id: সংখ্যা নাকি UUID?

• ক্রমিক সংখ্যা (১, ২, ৩...): ছোট, দ্রুত, পড়তে সহজ। কিন্তু URL এ /orders/1042 দেখে বোঝা যায় মোট কত order, আর পাশের সংখ্যা দিয়ে অন্যের order আন্দাজ করা যায় (permission check না থাকলে বিপদ)।
• UUID (7f9c...): অনুমান করা যায় না, client নিজেই আগে থেকে বানাতে পারে (offline-first app এ কাজের)। কিন্তু বড়, আর এলোমেলো UUID index কে কিছুটা ধীর করে। UUIDv7 সময়ের ক্রমে বাড়ে, তাই এই সমস্যা অনেক কম।

প্রচলিত সমাধান: ভেতরে সংখ্যা, বাইরে (API তে) UUID বা অন্য public id।

#Soft delete

User "delete" চাপলে সত্যিই মুছে ফেলবে, নাকি deleted_at column এ সময় লিখে লুকিয়ে রাখবে?

• Soft delete (deleted_at TIMESTAMPTZ NULL): ভুল করে মুছলে ফেরানো যায়, পুরনো রিপোর্ট ঠিক থাকে। কিন্তু প্রতিটা query তে WHERE deleted_at IS NULL মনে রাখতে হয়।
• আইনগত কারণে (user তার data মুছতে চাইলে) সত্যিকারের মুছে ফেলার ব্যবস্থাও রাখতে হয়।

#প্রতিটা table এ যা রাখা ভালো

created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

কোনো bug খুঁজতে গেলে "এটা কখন তৈরি বা বদলেছিল" প্রায় সবসময় লাগে।

#Index: query দ্রুত করার চাবি

Index ছাড়া "phone = 01711111111 এর customer খোঁজো" মানে database কে প্রতিটা row একটা একটা দেখতে হবে (sequential scan)। ১ কোটি row তে সেকেন্ড লেগে যায়। Index হলো বইয়ের শেষের নির্ঘণ্ট (index): শব্দ খুঁজে সরাসরি পৃষ্ঠা নম্বর।

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);
CREATE UNIQUE INDEX idx_customers_phone ON customers (phone);

#কোথায় index দেবে

• Foreign key column (orders.customer_id): JOIN আর "এই customer এর order" query তে লাগে। PostgreSQL এটা নিজে বানায় না!
• WHERE এ বারবার আসা column
• ORDER BY এর column, বিশেষ করে LIMIT সহ (সর্বশেষ ২০টা order)
• Unique হতে হবে এমন column (phone, email)

#Composite index এর ক্রম

(status, created_at) index কাজে লাগে:

• WHERE status = 'pending' ✓
• WHERE status = 'pending' ORDER BY created_at DESC ✓
• WHERE created_at > ... (শুধু) ✗ বেশিরভাগ ক্ষেত্রে কাজে লাগে না

ফোনবুকের মতো: আগে পদবি, তারপর নাম দিয়ে সাজানো। পদবি জানলে দ্রুত পাবে, শুধু নাম জানলে পুরো বই ঘাঁটতে হবে। বাম থেকে ডানে ব্যবহার হয়।

#Index এর দাম

• প্রতিটা INSERT/UPDATE এ index ও আপডেট হয়, তাই লেখা ধীর হয়
• Disk এর জায়গা লাগে
• তাই "সব column এ index" ভুল। যে query আসলে চলে, তার জন্য index।

Index ভেতরে কীভাবে কাজ করে (B-Tree) সেটা System Design এর Index অধ্যায়ে।

#EXPLAIN: database কী ভাবছে দেখো

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Index এর আগে:

Limit  (actual time=812.4..812.5 rows=20)
  -> Sort  (actual time=812.4..812.4 rows=20)
        -> Seq Scan on orders  (actual time=0.03..790.1 rows=1840 loops=1)
              Filter: (customer_id = 42)
              Rows Removed by Filter: 4998160
Execution Time: 812.6 ms

Seq Scan আর Rows Removed by Filter: 4998160 মানে ৫০ লাখ row পড়ে বাদ দিয়েছে। Index দাও:

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);

Limit  (actual time=0.05..0.09 rows=20)
  -> Index Scan using idx_orders_customer_created on orders  (actual time=0.04..0.08 rows=20)
        Index Cond: (customer_id = 42)
Execution Time: 0.12 ms

৮১২ ms থেকে ০.১২ ms। প্রায় ৭০০০ গুণ দ্রুত, এক লাইনে।

#পুরো schema: food delivery

CREATE TABLE customers (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    phone       TEXT NOT NULL UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE restaurants (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name          TEXT NOT NULL,
    area          TEXT NOT NULL,
    rating_avg    NUMERIC(3,2) NOT NULL DEFAULT 0,      -- denormalized
    rating_count  INT NOT NULL DEFAULT 0,
    is_open       BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE INDEX idx_restaurants_area ON restaurants (area) WHERE is_open;   -- partial index

CREATE TABLE menu_items (
    id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    restaurant_id  BIGINT NOT NULL REFERENCES restaurants(id),
    name           TEXT NOT NULL,
    price          BIGINT NOT NULL CHECK (price > 0),
    is_available   BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE INDEX idx_menu_restaurant ON menu_items (restaurant_id);

CREATE TABLE orders (
    id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id         UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    customer_id       BIGINT NOT NULL REFERENCES customers(id),
    restaurant_id     BIGINT NOT NULL REFERENCES restaurants(id),
    status            TEXT NOT NULL DEFAULT 'pending'
                      CHECK (status IN ('pending','confirmed','preparing','on_the_way','delivered','cancelled')),
    delivery_address  TEXT NOT NULL,                   -- order এর সময়ের copy
    subtotal          BIGINT NOT NULL,
    delivery_fee      BIGINT NOT NULL,
    total             BIGINT NOT NULL,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_customer ON orders (customer_id, created_at DESC);
CREATE INDEX idx_orders_restaurant_status ON orders (restaurant_id, status);

CREATE TABLE order_items (
    order_id      BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    menu_item_id  BIGINT NOT NULL REFERENCES menu_items(id),
    qty           INT NOT NULL CHECK (qty > 0),
    unit_price    BIGINT NOT NULL,                     -- order এর সময়ের দাম
    PRIMARY KEY (order_id, menu_item_id)
);

#অনুশীলন

#নিজেকে যাচাই করো