🧭 Plan & Database · Lesson 02

Database Design (MySQL Tables)

Think in tables first

Before writing any PHP or Kotlin, decide what data the shop must remember. Look at every screen of the app and ask "where does this come from?":

Every screen tells you which tables you need

The rule of thumb: one thing = one table, and when two things are connected, store the id of the other one (a foreign key) instead of copying its data.

The 14 tables (ER diagram)

Customer side — everything that belongs to one user:

Customer tables: they all have user_id

Shop side — the catalogue and the orders:

Catalogue and order tables (+ admins and banners, not shown)

Table	Stores	Used by
users, user_tokens	customer accounts and login tokens	register, login, every protected call
categories, products, product_images	the catalogue	home, search, product detail
banners	home slider images and where they link	home
cart_items, wishlist	what the user wants to buy / saved	cart, wishlist, hearts
addresses	delivery addresses (one default)	checkout
coupons	discount codes and their rules	checkout
orders, order_items	placed orders and their lines	my orders, admin
reviews	stars + comments	product detail
admins	shop owner logins	admin panel

Keys and rules that protect data

The database itself refuses bad data, so even a bug in PHP cannot break it:

PRIMARY KEY id INT AUTO_INCREMENT — every row gets a unique number.
FOREIGN KEY — cart_items.product_id must exist in products. ON DELETE CASCADE removes a user's cart rows when the user is deleted.
UNIQUE — one email per user, one cart row per (user, product), one review per (product, user). The cart API uses this with INSERT … ON DUPLICATE KEY UPDATE.
INDEX — idx_products_category and idx_products_price make filtering fast when you have thousands of products.

SQL – the database says no

-- Try to put a product that does not exist into a cart
INSERT INTO cart_items (user_id, product_id, qty) VALUES (1, 999, 1);

-- Try to register the same email twice
INSERT INTO users (name, email, password_hash) VALUES ('X', 'test@karteasy.in', 'x');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`karteasy`.`cart_items`, CONSTRAINT `fk_cart_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE) ERROR 1062 (23000): Duplicate entry 'test@karteasy.in' for key 'email'

Choosing data types

Data	Type	Why
Price, MRP, totals	DECIMAL(10,2)	Exact paise. FLOAT would turn 0.1 + 0.2 into 0.30000000000000004.
Status	ENUM('placed','confirmed','shipped','delivered','cancelled')	Only these values are allowed.
Flags	TINYINT(1)	0 / 1 for is_active, is_default, is_featured.
Pincode	CHAR(6)	Always exactly 6 digits and never used in maths, so it is stored as text.
Phone	VARCHAR(15)	A phone number is text, not a number you add up.
Token	CHAR(64)	SHA-256 hash in hex is always 64 characters.

Why orders copy the address and price

If a customer orders a watch for ₹1,899 today and the admin changes the price to ₹2,199 next week, the old order must still say ₹1,899. So order_items stores a copy of name, image and price, and orders stores a copy of the shipping address. This is called a snapshot.

Live data (can change)

products.price = ₹2,199 now
addresses row edited or deleted
product renamed

Order snapshot (never changes)

order_items.price = ₹1,899
orders.ship_address = full text
order_items.name = old name

Bills must never change after they are made

The complete database.sql

This is the exact file from backend/database.sql. The first part creates the tables, the second part adds sample data. The password hashes are made with PHP password_hash().

SQL – backend/database.sql

-- KartEasy e-commerce database (MySQL 5.7+ / MariaDB 10.3+)
-- Import this file in phpMyAdmin (or: mysql -u user -p karteasy < database.sql)

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS reviews, order_items, orders, coupons, addresses, wishlist, cart_items,
  product_images, products, banners, categories, user_tokens, users, admins;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  phone VARCHAR(15) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,          -- sha256 of the token, never the token itself
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  CONSTRAINT fk_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  email VARCHAR(120) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  slug VARCHAR(70) NOT NULL UNIQUE,
  image VARCHAR(160) DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE banners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  subtitle VARCHAR(150) DEFAULT NULL,
  image VARCHAR(160) NOT NULL,
  link_type ENUM('none','category','product') NOT NULL DEFAULT 'none',
  link_id INT DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(170) NOT NULL UNIQUE,
  short_desc VARCHAR(255) DEFAULT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  mrp DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  image VARCHAR(160) DEFAULT NULL,
  rating_avg DECIMAL(2,1) NOT NULL DEFAULT 0.0,
  rating_count INT NOT NULL DEFAULT 0,
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_products_category (category_id),
  KEY idx_products_price (price),
  CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_images (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  image VARCHAR(160) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  CONSTRAINT fk_pimages_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cart_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  qty INT NOT NULL DEFAULT 1,
  UNIQUE KEY uq_cart (user_id, product_id),
  CONSTRAINT fk_cart_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_cart_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wishlist (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_wishlist (user_id, product_id),
  CONSTRAINT fk_wish_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_wish_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE addresses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  name VARCHAR(80) NOT NULL,
  phone VARCHAR(15) NOT NULL,
  line1 VARCHAR(150) NOT NULL,
  line2 VARCHAR(150) DEFAULT NULL,
  city VARCHAR(60) NOT NULL,
  state VARCHAR(60) NOT NULL,
  pincode CHAR(6) NOT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  CONSTRAINT fk_addr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE coupons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  type ENUM('flat','percent') NOT NULL,
  value DECIMAL(10,2) NOT NULL,
  min_order DECIMAL(10,2) NOT NULL DEFAULT 0,
  max_discount DECIMAL(10,2) DEFAULT NULL,
  expires_at DATETIME DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(20) NOT NULL UNIQUE,
  user_id INT NOT NULL,
  ship_name VARCHAR(80) NOT NULL,
  ship_phone VARCHAR(15) NOT NULL,
  ship_address VARCHAR(400) NOT NULL,
  subtotal DECIMAL(10,2) NOT NULL,
  discount DECIMAL(10,2) NOT NULL DEFAULT 0,
  delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 0,
  total DECIMAL(10,2) NOT NULL,
  coupon_code VARCHAR(30) DEFAULT NULL,
  payment_method VARCHAR(20) NOT NULL DEFAULT 'COD',
  status ENUM('placed','confirmed','shipped','delivered','cancelled') NOT NULL DEFAULT 'placed',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_orders_user (user_id),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,                  -- copy: the product may change later
  image VARCHAR(160) DEFAULT NULL,
  price DECIMAL(10,2) NOT NULL,
  qty INT NOT NULL,
  CONSTRAINT fk_oitems_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  user_id INT NOT NULL,
  rating TINYINT NOT NULL,
  comment VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_review (product_id, user_id),
  CONSTRAINT fk_reviews_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_reviews_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------- sample data
-- Admin panel login: admin@karteasy.in / Admin@123   (change it after the first login!)
INSERT INTO admins (name, email, password_hash) VALUES
('Admin', 'admin@karteasy.in', '$2y$10$2bFUQcMtv1/RBgjWL9smLeZ2beV1G.W/kmefM88hbcDErx1P.w6au');

-- Demo customer: test@karteasy.in / Test@123
INSERT INTO users (name, email, phone, password_hash) VALUES
('Riya Sharma', 'test@karteasy.in', '9876543210', '$2y$10$uH03CdamJqTh78xX6h8anO2bK5SYInjgY9DUD8F0J54uvIpC.vxje');

INSERT INTO categories (id, name, slug, image, sort_order) VALUES
(1, 'Mobiles', 'mobiles', 'uploads/categories/mobiles.png', 1),
(2, 'Fashion', 'fashion', 'uploads/categories/fashion.png', 2),
(3, 'Electronics', 'electronics', 'uploads/categories/electronics.png', 3),
(4, 'Home & Kitchen', 'home-kitchen', 'uploads/categories/home-kitchen.png', 4),
(5, 'Books', 'books', 'uploads/categories/books.png', 5),
(6, 'Sports', 'sports', 'uploads/categories/sports.png', 6);

INSERT INTO products (id, category_id, name, slug, short_desc, description, price, mrp, stock, image, rating_avg, rating_count, is_featured) VALUES
(1, 1, 'Nova 5G Smartphone (8GB/128GB)', 'nova-5g-smartphone', '6.6" AMOLED, 50MP camera, 5000 mAh', '6.6 inch 120Hz AMOLED display, 50 MP dual camera, 5000 mAh battery with 33W fast charging and 5G support.', 14999, 18999, 25, 'uploads/products/p1.png', 4.4, 212, 1),
(2, 1, 'Pulse Lite Smartphone (4GB/64GB)', 'pulse-lite-smartphone', 'Big battery budget phone', '6.5 inch HD+ display, 13 MP camera and a 6000 mAh battery that lasts two days.', 7999, 9999, 40, 'uploads/products/p2.png', 4.1, 98, 0),
(3, 1, 'Fast Charger 33W Type-C', 'fast-charger-33w', 'Charge 0–50% in 25 minutes', '33W fast charger with Type-C cable, overheating protection and BIS certification.', 699, 1199, 120, 'uploads/products/p3.png', 4.3, 451, 0),
(4, 2, 'Men Running Sneakers', 'men-running-sneakers', 'Lightweight mesh, sizes 6–11', 'Breathable mesh upper, cushioned sole and anti-slip grip for daily running and walking.', 1299, 2499, 60, 'uploads/products/p4.png', 4.2, 330, 1),
(5, 2, 'Women Cotton Kurta', 'women-cotton-kurta', 'Pure cotton, machine washable', 'Printed straight kurta in soft breathable cotton, perfect for office and festive wear.', 899, 1599, 45, 'uploads/products/p5.png', 4.5, 187, 1),
(6, 2, 'Classic Denim Jacket', 'classic-denim-jacket', 'Unisex, regular fit', 'Stone-washed denim jacket with metal buttons and two chest pockets.', 1599, 2999, 18, 'uploads/products/p6.png', 4.0, 76, 0),
(7, 3, 'Smart Watch Pro', 'smart-watch-pro', 'AMOLED, SpO2, 7-day battery', '1.43 inch AMOLED screen, heart-rate and SpO2 tracking, 100+ sports modes and Bluetooth calling.', 1899, 4999, 35, 'uploads/products/p7.png', 4.5, 128, 1),
(8, 3, 'Wireless Earbuds X2', 'wireless-earbuds-x2', '30 hrs playback, ENC mic', 'Bluetooth 5.3 earbuds with environmental noise cancellation and 30 hours total playback.', 1099, 2999, 80, 'uploads/products/p8.png', 4.2, 640, 1),
(9, 3, 'Bluetooth Speaker Boom', 'bluetooth-speaker-boom', '20W, waterproof IPX7', '20W stereo sound, deep bass, IPX7 waterproof body and 12 hours battery.', 2499, 3999, 22, 'uploads/products/p9.png', 4.4, 205, 0),
(10, 3, 'Power Bank 20000 mAh', 'power-bank-20000', 'Dual USB + Type-C, 22.5W', 'Slim 20000 mAh power bank with 22.5W fast charging and three outputs.', 1499, 2499, 50, 'uploads/products/p10.png', 4.3, 512, 0),
(11, 4, 'Non-Stick Cookware Set (3 pcs)', 'non-stick-cookware-set', 'Induction friendly', 'Kadai, fry pan and tawa with a durable non-stick coating that works on gas and induction.', 1799, 3299, 30, 'uploads/products/p11.png', 4.1, 143, 1),
(12, 4, 'Steel Water Bottle 1L', 'steel-water-bottle', 'Hot 12h / cold 24h', 'Double-wall vacuum insulated stainless steel bottle, leak-proof lid.', 549, 899, 150, 'uploads/products/p12.png', 4.6, 820, 0),
(13, 5, 'Learn Kotlin in 30 Days', 'learn-kotlin-30-days', 'Beginner friendly, Hindi + English', 'Step-by-step Kotlin book with exercises, perfect before starting Android development.', 349, 499, 70, 'uploads/products/p13.png', 4.7, 66, 1),
(14, 5, 'PHP & MySQL Projects', 'php-mysql-projects', '10 real projects', 'Build ten complete PHP and MySQL projects including an e-commerce API and admin panel.', 399, 599, 55, 'uploads/products/p14.png', 4.6, 41, 0),
(15, 6, 'Yoga Mat 6mm', 'yoga-mat-6mm', 'Anti-skid, with carry strap', 'Thick 6 mm TPE yoga mat with anti-skid texture and a free carry strap.', 699, 1299, 65, 'uploads/products/p15.png', 4.3, 377, 0),
(16, 6, 'Cricket Bat English Willow', 'cricket-bat-english-willow', 'Full size, with cover', 'Grade 3 English willow bat with a cane handle and protective cover.', 3499, 4999, 12, 'uploads/products/p16.png', 4.4, 58, 1);

INSERT INTO product_images (product_id, image, sort_order) VALUES
(1, 'uploads/products/p1.png', 1), (1, 'uploads/products/p1b.png', 2),
(7, 'uploads/products/p7.png', 1), (7, 'uploads/products/p7b.png', 2),
(4, 'uploads/products/p4.png', 1), (4, 'uploads/products/p4b.png', 2);

INSERT INTO banners (title, subtitle, image, link_type, link_id, sort_order) VALUES
('Festive Sale', 'Up to 60% off on electronics', 'uploads/banners/b1.png', 'category', 3, 1),
('New Fashion', 'Kurtas, sneakers & more', 'uploads/banners/b2.png', 'category', 2, 2),
('Smart Watch Pro', 'Now at just ₹1,899', 'uploads/banners/b3.png', 'product', 7, 3);

INSERT INTO coupons (code, type, value, min_order, max_discount, expires_at) VALUES
('WELCOME50', 'flat', 50, 499, NULL, '2027-12-31 23:59:59'),
('SAVE10', 'percent', 10, 999, 300, '2027-12-31 23:59:59');

INSERT INTO reviews (product_id, user_id, rating, comment) VALUES
(7, 1, 5, 'Battery easily lasts a week. Great value!'),
(4, 1, 4, 'Comfortable for daily walks.');

Import it (phpMyAdmin or terminal)

1Open phpMyAdmin (XAMPP: http://localhost/phpmyadmin, Hostinger: hPanel → Databases → phpMyAdmin)
2Create a database named karteasy with collation utf8mb4_unicode_ci (on Hostinger the name gets a prefix like u123_karteasy)
3Select it → Import tab → choose database.sql → Go
4You should see 14 tables; open products → Browse to see 16 products

Import with phpMyAdmin

Terminal

# Create the database (utf8mb4 so ₹, Hindi and emoji work)
mysql -u root -p -e "CREATE DATABASE karteasy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"

# Import tables + sample data
mysql -u root -p karteasy < database.sql

# Check
mysql -u root -p karteasy -e "SHOW TABLES"

Try some queries

SQL

-- How many products in each category, and the cheapest one?
SELECT c.name AS category, COUNT(p.id) AS products, MIN(p.price) AS cheapest
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
GROUP BY c.id
ORDER BY c.sort_order;

+----------------+----------+----------+ | category | products | cheapest | +----------------+----------+----------+ | Mobiles | 3 | 699.00 | | Fashion | 3 | 899.00 | | Electronics | 4 | 1099.00 | | Home & Kitchen | 2 | 549.00 | | Books | 2 | 349.00 | | Sports | 2 | 699.00 | +----------------+----------+----------+

SQL

-- Top deals: biggest discount first (this is what the Home "Top deals" row uses)
SELECT id, name, price, mrp, ROUND((mrp - price) * 100 / mrp) AS off_pct
FROM products
ORDER BY off_pct DESC
LIMIT 4;

+----+----------------------+---------+---------+---------+ | id | name | price | mrp | off_pct | +----+----------------------+---------+---------+---------+ | 8 | Wireless Earbuds X2 | 1099.00 | 2999.00 | 63 | | 7 | Smart Watch Pro | 1899.00 | 4999.00 | 62 | | 4 | Men Running Sneakers | 1299.00 | 2499.00 | 48 | | 6 | Classic Denim Jacket | 1599.00 | 2999.00 | 47 | +----+----------------------+---------+---------+---------+

💡 Tip

Write the query in phpMyAdmin first. When it gives the right rows, copy it into PHP. That is exactly how every API endpoint in this course was written.

Common mistakes

Using FLOAT for money → totals off by a paisa.
Saving plain passwords → always password_hash().
Storing the full product inside the cart row → prices go stale; store only product_id and qty.
Forgetting utf8mb4 → "₹" and Hindi names turn into "???".
Deleting products that appear in orders → use is_active = 0 instead.

Frequently Asked Questions

Why not store the cart only in the phone?

A server cart works on every device the user logs in from, and the server can check stock and prices before checkout. The app still shows it instantly because every cart API call returns the full updated cart.

Why InnoDB and utf8mb4?

InnoDB supports foreign keys and transactions, which the checkout needs. utf8mb4 stores every character including ₹, Hindi text and emoji; the older utf8 cannot store emoji.

Should I delete products from the database?

No. Old orders point to them. KartEasy uses an is_active flag: the admin "hides" a product and it disappears from the app, but order history stays correct.

← E-commerce App Tutorial

Database Design (MySQL Tables)

Think in tables first

The 14 tables (ER diagram)

Keys and rules that protect data

Choosing data types

Why orders copy the address and price

Live data (can change)

Order snapshot (never changes)

The complete database.sql

Import it (phpMyAdmin or terminal)

Try some queries

Common mistakes

Frequently Asked Questions

How was this lesson?

Reviews from readers

Learn with video lessons in easy Hindi