Building an Inventory Management System with SQL: Step-by-Step Tutorial
Learn how to create a simple and effective inventory management system using SQL in this beginner-friendly step-by-step tutorial.
Inventory management is essential for businesses to keep track of their products, monitor stock levels, and ensure smooth operations. In this tutorial, you'll learn how to build a basic inventory management system using SQL. We will cover creating tables, inserting data, and running queries to manage and track inventory effectively.
Step 1: Creating the Database Tables. We need tables to store product information, categories, suppliers, and stock transactions.
-- Create table to store product categories
CREATE TABLE Categories (
CategoryID INT PRIMARY KEY AUTO_INCREMENT,
CategoryName VARCHAR(100) NOT NULL
);
-- Create table for suppliers
CREATE TABLE Suppliers (
SupplierID INT PRIMARY KEY AUTO_INCREMENT,
SupplierName VARCHAR(100) NOT NULL,
ContactEmail VARCHAR(100)
);
-- Create table for products
CREATE TABLE Products (
ProductID INT PRIMARY KEY AUTO_INCREMENT,
ProductName VARCHAR(100) NOT NULL,
CategoryID INT,
SupplierID INT,
UnitPrice DECIMAL(10, 2) NOT NULL,
QuantityInStock INT DEFAULT 0,
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID),
FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
);
-- Create table for stock transactions
CREATE TABLE StockTransactions (
TransactionID INT PRIMARY KEY AUTO_INCREMENT,
ProductID INT NOT NULL,
TransactionType ENUM('IN', 'OUT') NOT NULL,
Quantity INT NOT NULL,
TransactionDate DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);Step 2: Inserting Sample Data. Let's add some categories, suppliers, and products to begin.
-- Inserting categories
INSERT INTO Categories (CategoryName) VALUES ('Electronics'), ('Books'), ('Clothing');
-- Inserting suppliers
INSERT INTO Suppliers (SupplierName, ContactEmail) VALUES ('Tech Supplies Co.', 'contact@techsupplies.com'), ('Book World', 'sales@bookworld.com');
-- Inserting products
INSERT INTO Products (ProductName, CategoryID, SupplierID, UnitPrice, QuantityInStock) VALUES
('Laptop', 1, 1, 999.99, 10),
('Science Fiction Novel', 2, 2, 19.99, 50),
('T-Shirt', 3, NULL, 9.99, 100);Step 3: Recording Stock Transactions. Whenever stock arrives or is sold, you update transactions and adjust stock counts.
-- Add new stock for Laptop
INSERT INTO StockTransactions (ProductID, TransactionType, Quantity) VALUES (1, 'IN', 5);
-- Update stock for Laptop
UPDATE Products SET QuantityInStock = QuantityInStock + 5 WHERE ProductID = 1;
-- Sale of Science Fiction Novel
INSERT INTO StockTransactions (ProductID, TransactionType, Quantity) VALUES (2, 'OUT', 3);
-- Update stock after sale
UPDATE Products SET QuantityInStock = QuantityInStock - 3 WHERE ProductID = 2;Step 4: Querying Your Inventory. Let's check current stock levels and view product information.
-- View all products with their category and current stock
SELECT p.ProductID, p.ProductName, c.CategoryName, p.QuantityInStock, p.UnitPrice
FROM Products p
LEFT JOIN Categories c ON p.CategoryID = c.CategoryID;
-- Find products with low stock (less than 10)
SELECT ProductName, QuantityInStock
FROM Products
WHERE QuantityInStock < 10;Step 5: Tracking Transactions by Date. It's helpful to monitor stock movement over time.
-- View recent stock transactions
SELECT t.TransactionID, p.ProductName, t.TransactionType, t.Quantity, t.TransactionDate
FROM StockTransactions t
JOIN Products p ON t.ProductID = p.ProductID
ORDER BY t.TransactionDate DESC
LIMIT 10;This simple inventory management system in SQL can be expanded with additional features like user roles, reorder alerts, or integration with other business tools. Practice these basics, and you'll create a reliable backend for managing your inventory efficiently.