MySql - Inventory Management System

Inventory Management System Using MySQL

An Inventory Management System (IMS) is an essential part of any business dealing with stock, products, and supply chains. It helps businesses track product quantities, manage reordering, monitor suppliers, and generate insightful reports. MySQL is a perfect backend database choice for implementing a robust and scalable inventory system.

This guide presents a complete walkthrough of designing and implementing an Inventory Management System using MySQL. We will cover the database schema design, table creation, normalization, indexing, stored procedures, views, triggers, and reporting functionalities.

1. Functional Requirements

• Product registration and classification
• Stock quantity tracking (in/out)
• Supplier and category management
• Purchase and sales management
• Threshold-based low-stock alerts
• Audit trail for stock movements

2. Database Design and Schema

The following are the main entities in our Inventory Management System:

• Products
• Categories
• Suppliers
• Inventory
• Purchases
• Sales
• Stock Movements

ERD Relationships

• Each product belongs to one category and one supplier.
• Products are purchased and sold, which affects inventory.
• Stock movements log each adjustment (addition or subtraction).

3. Table Definitions

Categories Table

CREATE TABLE categories (
  category_id INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(100) UNIQUE NOT NULL,
  description TEXT
);

Suppliers Table

CREATE TABLE suppliers (
  supplier_id INT AUTO_INCREMENT PRIMARY KEY,
  supplier_name VARCHAR(100) NOT NULL,
  contact_name VARCHAR(100),
  phone VARCHAR(20),
  email VARCHAR(100),
  address TEXT
);

Products Table

CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  sku VARCHAR(50) UNIQUE,
  category_id INT,
  supplier_id INT,
  purchase_price DECIMAL(10, 2),
  sale_price DECIMAL(10, 2),
  min_stock INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(category_id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

Inventory Table

CREATE TABLE inventory (
  inventory_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  quantity INT DEFAULT 0,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Purchases Table

CREATE TABLE purchases (
  purchase_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  quantity INT,
  purchase_price DECIMAL(10,2),
  purchase_date DATE,
  supplier_id INT,
  FOREIGN KEY (product_id) REFERENCES products(product_id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

Sales Table

CREATE TABLE sales (
  sale_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  quantity INT,
  sale_price DECIMAL(10,2),
  sale_date DATE,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Stock Movements Table

CREATE TABLE stock_movements (
  movement_id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  movement_type ENUM('IN', 'OUT'),
  quantity INT,
  movement_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  description TEXT,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

4. Sample Data Insertion

Categories

INSERT INTO categories (category_name, description)
VALUES ('Electronics', 'Electronic gadgets and appliances'),
       ('Office Supplies', 'General office materials');

Suppliers

INSERT INTO suppliers (supplier_name, contact_name, phone, email, address)
VALUES ('TechCorp', 'Alice Johnson', '555-1234', 'alice@techcorp.com', '123 Tech Street');

Products

INSERT INTO products (product_name, sku, category_id, supplier_id, purchase_price, sale_price, min_stock)
VALUES ('Laptop', 'LPT123', 1, 1, 500.00, 700.00, 5);

5. Inventory and Transactions

Insert New Purchase

INSERT INTO purchases (product_id, quantity, purchase_price, purchase_date, supplier_id)
VALUES (1, 20, 500.00, '2025-07-01', 1);

Update Inventory after Purchase

UPDATE inventory
SET quantity = quantity + 20
WHERE product_id = 1;

Record Stock Movement (IN)

INSERT INTO stock_movements (product_id, movement_type, quantity, description)
VALUES (1, 'IN', 20, 'Purchase Order #1001');

Insert Sale

INSERT INTO sales (product_id, quantity, sale_price, sale_date)
VALUES (1, 2, 700.00, '2025-07-05');

Update Inventory after Sale

UPDATE inventory
SET quantity = quantity - 2
WHERE product_id = 1;

Record Stock Movement (OUT)

INSERT INTO stock_movements (product_id, movement_type, quantity, description)
VALUES (1, 'OUT', 2, 'Sale Invoice #5001');

6. Views for Reports

View for Current Stock

CREATE VIEW current_stock AS
SELECT p.product_name, i.quantity, p.min_stock
FROM products p
JOIN inventory i ON p.product_id = i.product_id;

Low Stock Report

SELECT * FROM current_stock
WHERE quantity < min_stock;

7. Triggers for Inventory Automation

Trigger after Purchase

CREATE TRIGGER trg_after_purchase
AFTER INSERT ON purchases
FOR EACH ROW
BEGIN
  UPDATE inventory SET quantity = quantity + NEW.quantity
  WHERE product_id = NEW.product_id;

  INSERT INTO stock_movements (product_id, movement_type, quantity, description)
  VALUES (NEW.product_id, 'IN', NEW.quantity, CONCAT('Purchase ID: ', NEW.purchase_id));
END;

Trigger after Sale

CREATE TRIGGER trg_after_sale
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
  UPDATE inventory SET quantity = quantity - NEW.quantity
  WHERE product_id = NEW.product_id;

  INSERT INTO stock_movements (product_id, movement_type, quantity, description)
  VALUES (NEW.product_id, 'OUT', NEW.quantity, CONCAT('Sale ID: ', NEW.sale_id));
END;

8. Stored Procedures

Procedure to Add New Product

DELIMITER //

CREATE PROCEDURE AddProduct(
  IN pname VARCHAR(100),
  IN sku_code VARCHAR(50),
  IN cat_id INT,
  IN supp_id INT,
  IN pp DECIMAL(10,2),
  IN sp DECIMAL(10,2),
  IN minqty INT
)
BEGIN
  INSERT INTO products(product_name, sku, category_id, supplier_id, purchase_price, sale_price, min_stock)
  VALUES (pname, sku_code, cat_id, supp_id, pp, sp, minqty);

  DECLARE pid INT;
  SET pid = LAST_INSERT_ID();

  INSERT INTO inventory(product_id, quantity) VALUES (pid, 0);
END //

DELIMITER ;

9. Backup and Restore

Database Backup

mysqldump -u root -p inventory_db > inventory_backup.sql

Database Restore

mysql -u root -p inventory_db < inventory_backup.sql

10. Security and Access Control

Create User and Grant Access

CREATE USER 'inventory_user'@'localhost' IDENTIFIED BY 'strongpass';

GRANT SELECT, INSERT, UPDATE, DELETE ON inventory_db.* TO 'inventory_user'@'localhost';

11. Reporting and Analytics Queries

Total Sales for a Month

SELECT SUM(quantity * sale_price) AS total_sales
FROM sales
WHERE sale_date BETWEEN '2025-07-01' AND '2025-07-31';

Total Purchase Value for a Month

SELECT SUM(quantity * purchase_price) AS total_purchases
FROM purchases
WHERE purchase_date BETWEEN '2025-07-01' AND '2025-07-31';

Stock Movement Summary

SELECT product_id,
  SUM(CASE WHEN movement_type = 'IN' THEN quantity ELSE 0 END) AS total_in,
  SUM(CASE WHEN movement_type = 'OUT' THEN quantity ELSE 0 END) AS total_out
FROM stock_movements
GROUP BY product_id;

The Inventory Management System using MySQL provides a powerful and flexible solution to control stock, monitor sales and purchases, track suppliers, and maintain product integrity. With proper indexing, normalization, and automation through triggers and procedures, the system remains scalable even with large datasets. This solution can be easily extended to interface with web and mobile applications or integrated into ERP solutions.