Project 2: Retail Store Inventory Management System

Beginner15 min read•Updated: 2026-09-12

Project 2: Retail Store Inventory Management System

In retail, e-commerce, and warehouse operations, profitability hinges on accurate inventory management. A retailer must know when product stock falls below critical thresholds, calculate the total monetary value of warehouse assets, prevent duplicate SKU barcodes, and handle price updates safely.

In this capstone project, you will engineer a complete Retail Store Inventory Management System in MySQL.

1. System Requirements & Schema Design

Our retail inventory system requires:

1
Product Categories (Electronics, Apparel, Groceries, Home Goods).
2
Product Catalog (Unique SKU, title, purchase cost, retail selling price, stock on hand, reorder thresholds).
3
Stock Reorder Alerts & Inventory Valuation Reports.

2. Step 1: DDL Database & Table Setup

1CREATE DATABASE IF NOT EXISTS msk_retail_inventory
2    CHARACTER SET utf8mb4
3    COLLATE utf8mb4_0900_ai_ci;
4 
5USE msk_retail_inventory;
6 
7-- Table 1: Product Categories
8CREATE TABLE IF NOT EXISTS categories (
9    category_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
10    category_name VARCHAR(50) NOT NULL UNIQUE,
11    tax_rate DECIMAL(4, 2) NOT NULL DEFAULT 18.00 -- Standard 18% GST
12) ENGINE = InnoDB;
13 
14-- Table 2: Product Master Inventory
15CREATE TABLE IF NOT EXISTS inventory_items (
16    item_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
17    sku_code VARCHAR(20) NOT NULL UNIQUE,
18    item_name VARCHAR(150) NOT NULL,
19    category_id INT UNSIGNED NOT NULL,
20    cost_price DECIMAL(10, 2) NOT NULL,
21    retail_price DECIMAL(10, 2) NOT NULL,
22    stock_quantity INT UNSIGNED NOT NULL DEFAULT 0,
23    reorder_threshold INT UNSIGNED NOT NULL DEFAULT 10,
24    is_active BOOLEAN NOT NULL DEFAULT TRUE,
25    last_restocked_at TIMESTAMP NULL DEFAULT NULL,
26    
27    -- Constraints
28    CONSTRAINT chk_profit_margin CHECK (retail_price >= cost_price),
29    CONSTRAINT fk_items_category FOREIGN KEY (category_id) REFERENCES categories(category_id)
30) ENGINE = InnoDB;

3. Step 2: DML Stock Ingestion

1-- Populate Categories:
2INSERT INTO categories (category_name, tax_rate) VALUES
3    ('Electronics', 18.00),
4    ('Office Stationery', 12.00),
5    ('Beverages', 5.00),
6    ('Computer Accessories', 18.00);
7 
8-- Populate Inventory Items:
9INSERT INTO inventory_items 
10    (sku_code, item_name, category_id, cost_price, retail_price, stock_quantity, reorder_threshold, last_restocked_at) 
11VALUES
12    ('SKU-ELEC-001', 'Noise-Cancelling Wireless Headphones', 1, 4500.00, 7999.00, 4, 15, '2026-02-10 10:00:00'),
13    ('SKU-ELEC-002', '4K Ultra-HD Monitor 27-inch', 1, 14000.00, 21999.00, 18, 5, '2026-02-15 14:30:00'),
14    ('SKU-ACC-001', 'Ergonomic Optical Mouse', 4, 350.00, 799.00, 65, 20, '2026-01-20 09:00:00'),
15    ('SKU-ACC-002', 'Mechanical Gaming Keyboard', 4, 1800.00, 3499.00, 8, 12, '2026-02-01 16:00:00'),
16    ('SKU-STAT-001', 'A4 Executive Notebook 200 Pages', 2, 60.00, 150.00, 120, 30, '2026-01-10 11:00:00'),
17    ('SKU-BEV-001', 'Organic Roasted Green Tea 250g', 3, 180.00, 350.00, 2, 25, '2026-02-28 08:45:00');

4. Step 3: Production Warehouse Queries

1-- Query 1: Automated Low-Stock Reorder Alert
2-- Find all items where current stock is at or below the reorder threshold:
3SELECT 
4    sku_code,
5    item_name,
6    stock_quantity,
7    reorder_threshold,
8    (reorder_threshold - stock_quantity) AS units_needed_to_reorder
9FROM inventory_items
10WHERE is_active = TRUE 
11  AND stock_quantity <= reorder_threshold
12ORDER BY stock_quantity ASC;
13 
14-- Query 2: Warehouse Asset Valuation Report
15-- Calculate total wholesale asset value vs projected retail revenue per category:
16SELECT 
17    category_id,
18    COUNT(*) AS total_skus,
19    SUM(stock_quantity) AS total_physical_items,
20    SUM(cost_price * stock_quantity) AS total_cost_valuation,
21    SUM(retail_price * stock_quantity) AS total_retail_valuation,
22    SUM((retail_price - cost_price) * stock_quantity) AS projected_gross_profit
23FROM inventory_items
24GROUP BY category_id
25ORDER BY projected_gross_profit DESC;
26 
27-- Query 3: Stock Refill Operation (Restocking)
28-- Restock 'Noise-Cancelling Wireless Headphones' with 50 units:
29UPDATE inventory_items
30SET stock_quantity = stock_quantity + 50,
31    last_restocked_at = CURRENT_TIMESTAMP
32WHERE sku_code = 'SKU-ELEC-001';

5. Capstone Takeaways

This project illustrates how database constraints protect business viability:

The chk_profit_margin constraint ensures products cannot be accidentally listed for less than wholesale cost.
Mathematical aggregations compute live warehouse valuations without manual spreadsheet calculations.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In Query 1, how is the low-stock condition identified?

Question 2

What business protection does the constraint CHECK (retail_price >= cost_price) enforce?

Question 3

How does Query 3 perform a safe relative inventory update?

Question 4

What does SUM((retail_price - cost_price) * stock_quantity) calculate?

Question 5

Why is sku_code marked as UNIQUE in the table definition?

Next Lesson

Project 3: Library Book Lending & Member Tracking System

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Project 1: Student Information System Database	Project 3: Library Book Lending & Member Tracking System

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates