Building Centralized Multi-Branch Inventory Tracking Systems in PHP
Retail and wholesale businesses expanding across Nepal—such as electronics chains, apparel brands, and pharmaceutical distributors—frequently struggle with decentralized inventory tracking. A business with a central distribution hub in Kathmandu and retail outlets in Pokhara, Butwal, and Narayangarh often suffers from stockouts in one branch while excess inventory sits unsold in another. Off-the-shelf software is often too rigid or expensive, making custom PHP/MySQL multi-branch inventory systems an ideal solution.
Strategic Executive Summary
- Core Insight: When multiple branch cashiers sell items simultaneously, unmanaged database reads cause negative inventory balances. Always use database row-level locking ( SELECT ... FOR UPDATE ) within atomic transactions.
- Production Quality: Battle-tested engineering techniques designed specifically for Nepal's network infrastructure and business environment.
- Direct Implementation: Copy-paste ready code architectures with security safeguards against race conditions, data corruption, and unauthorized access.
Table of Contents
1. Relational Schema for Multi-Branch Inventory
Stock quantities must be isolated per branch rather than stored as a single global attribute on the product table:
CREATE TABLE branches (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
branch_name VARCHAR(100) NOT NULL, -- e.g. "Butwal Main Outlet"
location VARCHAR(150) NOT NULL,
is_warehouse TINYINT(1) DEFAULT 0
);
CREATE TABLE products (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(50) NOT NULL UNIQUE,
product_name VARCHAR(200) NOT NULL,
selling_price DECIMAL(10,2) NOT NULL
);
CREATE TABLE branch_inventory (
branch_id INT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
stock_quantity INT NOT NULL DEFAULT 0,
reorder_threshold INT NOT NULL DEFAULT 5,
PRIMARY KEY (branch_id, product_id),
FOREIGN KEY (branch_id) REFERENCES branches(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);2. Managing Inter-Branch Stock Transfers Safely
Transferring goods between branches requires a multi-stage status workflow: PENDING > IN_TRANSIT > RECEIVED.
<?php
function executeBranchTransfer(PDO $pdo, int $fromBranch, int $toBranch, int $productId, int $qty): bool {
$pdo->beginTransaction();
try {
// Lock source branch row for update
$stmt = $pdo->prepare("SELECT stock_quantity FROM branch_inventory
WHERE branch_id = :b AND product_id = :p FOR UPDATE");
$stmt->execute(['b' => $fromBranch, 'p' => $productId]);
$currentStock = $stmt->fetchColumn();
if ($currentStock < $qty) {
throw new Exception("Insufficient stock at originating branch.");
}
// Deduct from source branch
$deduct = $pdo->prepare("UPDATE branch_inventory SET stock_quantity = stock_quantity - :qty
WHERE branch_id = :b AND product_id = :p");
$deduct->execute(['qty' => $qty, 'b' => $fromBranch, 'p' => $productId]);
// Add to destination branch (or insert if first shipment)
$add = $pdo->prepare("INSERT INTO branch_inventory (branch_id, product_id, stock_quantity)
VALUES (:b, :p, :qty)
ON DUPLICATE KEY UPDATE stock_quantity = stock_quantity + :qty");
$add->execute(['qty' => $qty, 'b' => $toBranch, 'p' => $productId]);
// Record immutable transfer audit log
$log = $pdo->prepare("INSERT INTO stock_transfer_logs (from_branch, to_branch, product_id, quantity)
VALUES (?, ?, ?, ?)");
$log->execute([$fromBranch, $toBranch, $productId, $qty]);
$pdo->commit();
return true;
} catch (Exception $e) {
$pdo->rollBack();
return false;
}
}3. Automated Low-Stock Alerts via SMS & Email
Set up a daily cron job that scans branch_inventory where stock_quantity <= reorder_threshold and dispatches aggregated stock alerts to warehouse procurement officers, preventing lost sales.
Official Technical Documentation & Reference Sources:
