Mid-term Project — Design & Build a Real Schema Milestone
মিডটার্ম প্রোজেক্ট — একটি বাস্তব schema design
1. The Project — Why It Matters
You have spent twenty-three modules learning the parts: tables, joins, transactions, constraints, indexes. Now you assemble them into a real, runnable e-commerce database — the kind that powers Daraz, Pickaboo, or AjkerDeal — built from blank schema to seeded data to working queries, in this single page. Every block in this module runs live in your browser. By the end, you will have shipped your first real schema.
We will build an e-commerce schema together because it touches every concept (joins, many-to-many, money, audit, indexes). At the end you'll find practice problems that ask you to design library and university versions on your own.
2. Step 1 — Gather Requirements
Schema design always starts with a conversation, not CREATE TABLE. Imagine you are sitting
with the founder of "DhakaCart" — a hypothetical Bangladeshi e-commerce startup. The conversation
produces this list of facts:
- Customers register with a phone number and an email; phone is mandatory, email optional.
- A customer has many shipping addresses (e.g. home, office).
- Products belong to one category. A product has a name, price, stock, and is "active" or not.
- An order has many line items. Each line item is one product × quantity × the price at the time of purchase.
- Order status flows: pending → paid → shipped → delivered (or cancelled).
- Payments can be by bKash, Nagad, card, or cash-on-delivery.
- Customers leave reviews (1–5 stars) on products they bought.
From this list you can already see most of the entities (customers, products, orders, payments, reviews) and their relationships. The next step is to draw an ER diagram.
3. Step 2 — ER & Relational Mapping
Below is the ER sketch for DhakaCart. Each box is a relation (a future CREATE TABLE); each
line marks a foreign key. The "many" end is shown by the crow's foot notation.
The mapping rules from ER → relational are familiar: each entity becomes a table, each "1-to-many" relationship becomes a foreign key on the "many" side, and any many-to-many would become a join table. We do not have a true many-to-many here — order_items already serves that role between orders and products.
4. Step 3 — Full CREATE TABLE Listing
Below is the entire DhakaCart schema. Notice the use of every constraint we learned: PRIMARY KEY,
NOT NULL, UNIQUE, CHECK, DEFAULT, REFERENCES,
and a thoughtfully placed ON DELETE CASCADE on the dependent tables.
CREATE TABLE set-এ আপনি দেখবেন আগের সব lesson একসাথে ব্যবহৃত হচ্ছে — PRIMARY KEY,
NOT NULL, UNIQUE, CHECK, DEFAULT, FOREIGN KEY, এবং নির্বাচিত জায়গায় ON DELETE CASCADE। এটি একটি real
schema-এর pattern।
PRAGMA foreign_keys = ON;
CREATE TABLE customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
phone TEXT NOT NULL UNIQUE CHECK (length(phone) = 11),
email TEXT UNIQUE,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE addresses (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
label TEXT NOT NULL CHECK (label IN('home','office','other')),
line1 TEXT NOT NULL,
city TEXT NOT NULL
);
CREATE TABLE categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
category_id INTEGER REFERENCES categories(id),
price INTEGER NOT NULL CHECK (price > 0),
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
is_active INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL REFERENCES customers(id),
address_id INTEGER REFERENCES addresses(id),
status TEXT NOT NULL DEFAULT 'pending'
CHECK (status IN('pending','paid','shipped','delivered','cancelled')),
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id INTEGER NOT NULL REFERENCES products(id),
qty INTEGER NOT NULL CHECK (qty > 0),
unit_price INTEGER NOT NULL CHECK (unit_price >= 0)
);
CREATE TABLE payments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
method TEXT NOT NULL CHECK (method IN('bkash','nagad','card','cod')),
amount INTEGER NOT NULL CHECK (amount > 0),
paid_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE reviews (
id INTEGER PRIMARY KEY AUTOINCREMENT,
product_id INTEGER NOT NULL REFERENCES products(id),
customer_id INTEGER NOT NULL REFERENCES customers(id),
stars INTEGER NOT NULL CHECK (stars BETWEEN 1 AND 5),
body TEXT,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE(product_id, customer_id) -- one review per customer per product
);
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
Run it — you should see eight tables come back. Note data-shared-db="dhakacart" on the block:
every block on this page that uses the same key will share the same database, so the seed in the next
block populates these tables.
5. Step 4 — Seed Realistic Data
Realistic data matters. Five customers, four categories, ten products, several orders, payments and reviews — enough to make every query meaningful.
INSERT INTO customers(name,phone,email) VALUES
('Arif Hossain', '01711111111', 'arif@example.com'),
('Nusrat Jahan', '01722222222', 'nusrat@example.com'),
('Rahim Ahmed', '01733333333', NULL),
('Sumi Akter', '01744444444', 'sumi@example.com'),
('Karim Uddin', '01755555555', NULL);
INSERT INTO addresses(customer_id,label,line1,city) VALUES
(1,'home','House 12, Road 4','Dhaka'),
(2,'home','Flat 3B, Mirpur 1','Dhaka'),
(3,'home','Lalmatia C-Block','Dhaka'),
(4,'office','Gulshan 2','Dhaka'),
(5,'home','Agrabad','Chattogram');
INSERT INTO categories(name) VALUES
('Electronics'),('Books'),('Fashion'),('Groceries');
INSERT INTO products(name,category_id,price,stock) VALUES
('Walton Refrigerator 8cft', 1, 42000, 12),
('Symphony Z40', 1, 8500, 40),
('Padma Nadir Majhi', 2, 350, 100),
('Pather Panchali', 2, 280, 85),
('Aarong Punjabi', 3, 2200, 25),
('Daraz Tee', 3, 450, 200),
('PRAN Mango Juice 1L', 4, 120, 300),
('Mishti Doi 500g', 4, 180, 50),
('iPhone 13', 1, 95000, 5),
('Tagore Rachanaboli', 2, 1200, 30);
INSERT INTO orders(customer_id,address_id,status) VALUES
(1,1,'paid'),
(2,2,'delivered'),
(2,2,'pending'),
(3,3,'shipped'),
(4,4,'paid'),
(5,5,'cancelled');
INSERT INTO order_items(order_id,product_id,qty,unit_price) VALUES
(1,2,1,8500),
(1,3,2,350),
(2,5,1,2200),
(2,7,5,120),
(3,9,1,95000),
(4,1,1,42000),
(5,4,3,280),
(5,10,1,1200),
(6,6,2,450);
INSERT INTO payments(order_id,method,amount) VALUES
(1,'bkash',9200),
(2,'cod',2800),
(4,'card',42000),
(5,'nagad',2040);
INSERT INTO reviews(product_id,customer_id,stars,body) VALUES
(2,1,5,'Excellent phone for the price'),
(5,2,4,'Nice fabric'),
(3,1,5,'A timeless classic'),
(9,2,5,'Worth every taka'),
(7,2,3,'OK');
SELECT (SELECT COUNT(*) FROM customers) AS cust,
(SELECT COUNT(*) FROM products) AS prod,
(SELECT COUNT(*) FROM orders) AS ord,
(SELECT COUNT(*) FROM order_items) AS items;6. Step 5 — Eighteen Useful Queries
What follows is a tour of the kinds of queries a real business actually runs. Each one is short and runs against the seeded data in the same in-page database. Read each, predict the output, then run it.
SELECT id, name, phone FROM customers ORDER BY id;SELECT p.id, p.name, p.price
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE c.name = 'Books'
ORDER BY p.price DESC;SELECT o.id AS order_id, SUM(oi.qty * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id
ORDER BY total DESC;SELECT p.name, SUM(oi.qty * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.id
ORDER BY revenue DESC
LIMIT 3;SELECT c.name,
COALESCE(SUM(oi.qty * oi.unit_price), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id
ORDER BY lifetime_value DESC;SELECT o.id, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
LEFT JOIN payments p ON p.order_id = o.id
WHERE p.id IS NULL;SELECT p.name, ROUND(AVG(r.stars), 2) AS avg_stars, COUNT(*) AS n
FROM reviews r
JOIN products p ON p.id = r.product_id
GROUP BY p.id
ORDER BY avg_stars DESC;SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM reviews r WHERE r.customer_id = c.id);SELECT o.id,
SUM(oi.qty * oi.unit_price) AS order_total,
SUM(SUM(oi.qty * oi.unit_price)) OVER (ORDER BY o.id) AS running_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;SELECT c.name AS category, p.name, p.price,
RANK() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS rk
FROM products p JOIN categories c ON c.id = p.category_id;SELECT method, COUNT(*) AS n, SUM(amount) AS total
FROM payments
GROUP BY method
ORDER BY total DESC;SELECT name, stock
FROM products
WHERE stock < 15 AND is_active = 1
ORDER BY stock;SELECT a.city, COUNT(*) AS orders
FROM orders o JOIN addresses a ON a.id = o.address_id
GROUP BY a.city;SELECT c.name, COUNT(o.id) AS orders
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
HAVING COUNT(o.id) >= 2;SELECT p.name
FROM products p
WHERE p.id NOT IN (SELECT DISTINCT product_id FROM order_items);SELECT c.name, ROUND(AVG(r.stars), 2) AS avg_stars
FROM reviews r
JOIN products p ON p.id = r.product_id
JOIN categories c ON c.id = p.category_id
GROUP BY c.id
ORDER BY avg_stars DESC;SELECT substr(created_at, 1, 10) AS day, COUNT(*) AS n
FROM orders
GROUP BY day
ORDER BY day;SELECT p.name, oi.qty, oi.unit_price,
(oi.qty * oi.unit_price) AS line_total
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = 1;7. Step 6 — Recommended Indexes
Looking at the queries above, the hot paths emerge: filter by customer_id on orders, by
order_id on order_items, by product_id on reviews, by status on
orders. Below is the index set we would ship to production.
CREATE INDEX idx_orders_cust ON orders(customer_id, created_at DESC);
CREATE INDEX idx_orders_status ON orders(status) WHERE status = 'pending';
CREATE INDEX idx_oi_order ON order_items(order_id);
CREATE INDEX idx_oi_product ON order_items(product_id);
CREATE INDEX idx_reviews_prod ON reviews(product_id);
CREATE INDEX idx_payments_order ON payments(order_id);
CREATE INDEX idx_products_cat ON products(category_id);
SELECT name FROM sqlite_master WHERE type = 'index' AND name LIKE 'idx_%';
Re-run any of the eighteen queries with EXPLAIN QUERY PLAN in front and you will see
SEARCH … USING INDEX instead of SCAN.
8. Practice — Your Turn
These problems extend the DhakaCart schema or ask you to design a similar one. Most answers are runnable against the seeded database from above.
-
Find the customer who has spent the most money (excluding cancelled orders).সবচেয়ে বেশি ব্যয় করা customer-কে খুঁজুন (cancelled order বাদে)।
✨ Show Answer
ans1.sqlSELECT c.name, SUM(oi.qty*oi.unit_price) AS spent FROM customers c JOIN orders o ON o.customer_id=c.id AND o.status<>'cancelled' JOIN order_items oi ON oi.order_id=o.id GROUP BY c.id ORDER BY spent DESC LIMIT 1; -
For each category, find the most expensive product.প্রতিটি category-তে সবচেয়ে দামি product খুঁজুন।
✨ Show Answer
ans2.sqlWITH ranked AS ( SELECT p.*, RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS rk FROM products p ) SELECT c.name AS category, r.name, r.price FROM ranked r JOIN categories c ON c.id=r.category_id WHERE r.rk = 1; -
List the products that have been reviewed by customers who never bought them (a possible fraud signal).এমন review খুঁজুন যেখানে customer সেই product কেনেননি — fraud-signal হতে পারে।
✨ Show Answer
ans3.sqlSELECT r.id, p.name AS product, c.name AS customer, r.stars FROM reviews r JOIN products p ON p.id=r.product_id JOIN customers c ON c.id=r.customer_id WHERE NOT EXISTS ( SELECT 1 FROM orders o JOIN order_items oi ON oi.order_id=o.id WHERE o.customer_id = r.customer_id AND oi.product_id = r.product_id ); -
Sketch (in your head — write it out) the same DhakaCart schema for a library: members, books, copies, loans. List the tables, key columns, and which constraints you'd add.একই pattern-এ একটি library-এর schema design করুন (members, books, copies, loans) — table, key, constraint-এর তালিকা লিখুন।
✨ Show Answer
Answer:
members(id PK, name, phone NN UNIQUE, joined_at)books(id PK, title NN, author NN, isbn UNIQUE)copies(id PK, book_id FK, status CHECK in('available','loaned','lost'))loans(id PK, copy_id FK, member_id FK, loaned_at, due_at, returned_at NULL)- Indexes:
(member_id, returned_at)on loans for "active loans of a member";(book_id, status)on copies; partial index onloans WHERE returned_at IS NULL.
প্রতিটি member একাধিক loan নিতে পারে; প্রতিটি book-এর একাধিক copy থাকে; loan-এর active row হলো যেগুলোর returned_at NULL — সেগুলোর জন্য partial index।
-
Add a "soft delete" pattern: a
deleted_atcolumn onproducts, plus a partial index that ignores deleted rows. Show thatSELECT … WHERE deleted_at IS NULLuses the index.products-এ soft-delete column যোগ করুন এবং deleted_at IS NULL-এর জন্য partial index ব্যবহার দেখান।✨ Show Answer
ans5.sqlALTER TABLE products ADD COLUMN deleted_at TEXT; CREATE INDEX idx_products_active ON products(name) WHERE deleted_at IS NULL; EXPLAIN QUERY PLAN SELECT name FROM products WHERE deleted_at IS NULL AND name LIKE 'Walton%';
Summary — Module 24 (Milestone)
You designed a real e-commerce schema from a conversation, mapped it to relational tables, wrote every constraint that mattered, seeded believable Bangladeshi data, exercised it with eighteen real-world queries, and finished with a production-grade index set. Every concept from Modules 1–23 came together on this page. This is what database engineering looks like in practice.