Creating a Supply Chain Tracking System involves several components, including a MySQL database schema, file structure, layout design using Bootstrap 5, and complete PHP scripts for user authentication and management. Below is a comprehensive guide to help you set up your system.

Project Introduction

The Supply Chain Tracking System is designed to enhance the efficiency and transparency of supply chain operations. This system allows users to manage inventory, track orders, and monitor shipments while providing insights through analytics and quality control measures. With a robust MySQL database backend, the platform ensures secure data management and a user-friendly experience for all roles, including administrators, suppliers, manufacturers, distributors, and retailers.

Project Objectives

  • To create a secure user registration and login system for various roles in the supply chain.
  • To manage inventory effectively, including tracking product quantities and reorder levels.
  • To facilitate order management, including order creation, status tracking, and total amount calculation.
  • To maintain supplier information and performance metrics for better supplier management.
  • To track shipments, including shipment status and tracking numbers for orders.
  • To provide analytics on supply chain performance metrics for informed decision-making.
  • To implement quality control measures to ensure product standards are met.
  • To manage financial transactions related to orders, including payment methods and statuses.
  • To ensure compliance with relevant regulations and standards in the supply chain.

Project Modules

  1. User Management: Handles user registration, login, and role-based access for admins, suppliers, manufacturers, distributors, and retailers.
  2. Inventory Management: Manages product inventory, including tracking quantities and reorder levels.
  3. Order Management: Facilitates the creation and tracking of orders, including order status and total amounts.
  4. Supplier Management: Maintains supplier information and performance metrics for effective supplier relationships.
  5. Shipment Tracking: Monitors shipments, including status updates and tracking information for orders.
  6. Analytics Module: Provides insights into supply chain performance through various metrics and reports.
  7. Quality Control: Implements quality control measures to ensure products meet required standards.
  8. Financial Transactions Management: Manages financial transactions related to orders, including payment processing.
  9. Compliance Management: Ensures adherence to relevant regulations and standards in the supply chain.

1. MySQL Database Schema


CREATE DATABASE supply_chain_tracking;
USE supply_chain_tracking;
-- Table for users
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    role ENUM('admin', 'supplier', 'manufacturer', 'distributor', 'retailer') DEFAULT 'retailer',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Table for inventory
CREATE TABLE inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location VARCHAR(255) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    reorder_level INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Table for orders
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    status ENUM('pending', 'completed', 'canceled') DEFAULT 'pending',
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Table for order items
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);
-- Table for suppliers
CREATE TABLE suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    contact_info VARCHAR(255),
    performance_metrics TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Table for shipments
CREATE TABLE shipments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    shipment_date DATETIME NOT NULL,
    status ENUM('in_transit', 'delivered', 'returned') DEFAULT 'in_transit',
    tracking_number VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);
-- Table for analytics
CREATE TABLE analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    metric_name VARCHAR(100) NOT NULL,
    value DECIMAL(10, 2) NOT NULL,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Table for quality control
CREATE TABLE quality_control (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    inspection_date DATE NOT NULL,
    result ENUM('pass', 'fail') NOT NULL,
    comments TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Table for financial transactions
CREATE TABLE financial_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    transaction_date DATETIME NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('credit_card', 'bank_transfer', 'cash') NOT NULL,
    status ENUM('completed', 'pending', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);
-- Table for compliance
CREATE TABLE compliance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    regulation VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. File and Folder Structure


supply_chain_tracking_system/
│
├── config/
│   └── db.php
│
├── public/
│   ├── index.php
│   ├── login.php
│   ├── register.php
│   ├── dashboard.php
│   ├── inventory.php
│   ├── orders.php
│   ├── suppliers.php
│   ├── shipments.php
│   ├── analytics.php
│   ├── quality_control.php
│   ├── financial_transactions.php
│   ├── compliance.php
│   └── admin_dashboard.php
│
├── includes/
│   ├── header.php
│   ├── footer.php
│   └── functions.php
│
├── assets/
│   ├── css/
│   │   └── styles.css
│   ├── js/
│   │   └── scripts.js
│   └── images/
│
└── vendor/
    └── (Bootstrap and other libraries)

3. Layout Based Design with Bootstrap 5

Header (includes/header.php)


<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="assets/css/styles.css">
    <title>Supply Chain Tracking System</title>
</head>
<body>
    <div class="container">
        <header class="my-4">
            <h1>Supply Chain Tracking System</h1>
        </header>
        <nav class="navbar navbar-expand-lg navbar-light bg-light">
            <div class="container-fluid">
                <a class="navbar-brand" href="index.php">Home</a>
                <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
                    <span class="navbar-toggler-icon"></span>
                </button>
                <div class="collapse navbar-collapse" id="navbarNav">
                    <ul class="navbar-nav">
                        <li class="nav-item">
                            <a class="nav-link" href="inventory.php">Inventory</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="orders.php">Orders</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="suppliers.php">Suppliers</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="shipments.php">Shipments</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="analytics.php">Analytics</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="quality_control.php">Quality Control</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="financial_transactions.php">Financial Transactions</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="compliance.php">Compliance</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="login.php">Login</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="register.php">Register</a>
                        </li>
                    </ul>
                </div>
            </div>
        </nav>
        <main class="my-4">

Footer (includes/footer.php)


        </main>
        <footer class="text-center my-4">
            <p>© 2023 Supply Chain Tracking System. All rights reserved.</p>
        </footer>
    </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

4. PHP Code for User Authentication

Registration (public/register.php)


<?php
require '../config/db.php';
require '../includes/header.php';
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $username = $_POST['username'];
    $email = $_POST['email'];
    $password = password_hash($_POST['password'], PASSWORD_DEFAULT);
    $role = $_POST['role'];
    $stmt = $conn->prepare("INSERT INTO users (username, email, password, role) VALUES (?, ?, ?, ?)");
    $stmt->bind_param("ssss", $username, $email, $password, $role);
    $stmt->execute();
    $stmt->close();
    header("Location: login.php");
}
?>
<form method="POST" action="">
    <div class="mb-3">
        <label for="username" class="form-label">Username</label>
        <input type="text" class="form-control" id="username" name="username" required>
    </div>
    <div class="mb-3">
        <label for="email" class="form-label">Email</label>
        <input type="email" class="form-control" id="email" name="email" required>
    </div>
    <div class="mb-3">
        <label for="password" class="form-label">Password</label>
        <input type="password" class="form-control" id="password" name="password" required>
    </div>
    <div class="mb-3">
        <label for="role" class="form-label">Role</label>
        <select class="form-select" id="role" name="role">
            <option value="supplier">Supplier</option>
            <option value="manufacturer">Manufacturer</option>
            <option value="distributor">Distributor</option>
            <option value="retailer">Retailer</option>
            <option value="admin">Admin</option>
        </select>
    </div>
    <button type="submit" class="btn btn-primary">Register</button>
</form>
<?php require '../includes/footer.php'; ?>

Login (public/login.php)


<?php
session_start();
require '../config/db.php';
require '../includes/header.php';
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $username = $_POST['username'];
    $password = $_POST['password'];
    $stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");
    $stmt->bind_param("s", $username);
    $stmt->execute();
    $result = $stmt->get_result();
    $user = $result->fetch_assoc();
    if ($user && password_verify($password, $user['password'])) {
        $_SESSION['user_id'] = $user['id'];
        $_SESSION['role'] = $user['role'];
        header("Location: dashboard.php");
    } else {
        echo "Invalid credentials.";
    }
}
?>
<form method="POST" action="">
    <div class="mb-3">
        <label for="username" class="form-label">Username</label>
        <input type="text" class="form-control" id="username" name="username" required>
    </div>
    <div class="mb-3">
        <label for="password" class="form-label">Password</label>
        <input type="password" class="form-control" id="password" name="password" required>
    </div>
    <button type="submit" class="btn btn-primary">Login</button>
</form>
<?php require '../includes/footer.php'; ?>

5. Additional PHP Scripts

Dashboard (public/dashboard.php)


<?php
session_start();
require '../config/db.php';
require '../includes/header.php';
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}
$user_id = $_SESSION['user_id'];
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
?>
<h2>Welcome, <?php echo $user['username']; ?></h2>
<p>Your role: <?php echo ucfirst($user['role']); ?></p>
<?php require '../includes/footer.php'; ?>

Inventory Management (public/inventory.php)


<?php
session_start();
require '../config/db.php';
require '../includes/header.php';
if (!isset($_SESSION['user_id']) || $_SESSION['role'] !== 'admin') {
    header("Location: login.php");
    exit();
}
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $product_name = $_POST['product_name'];
    $quantity = $_POST['quantity'];
    $reorder_level = $_POST['reorder_level'];
    $stmt = $conn->prepare("INSERT INTO inventory (product_name, quantity, reorder_level) VALUES (?, ?, ?)");
    $stmt->bind_param("ssi", $product_name, $quantity, $reorder_level);
    $stmt->execute();
    $stmt->close();
    header("Location: inventory.php");
}
?>
<h2>Manage Inventory</h2>
<form method="POST" action="">
    <div class="mb-3">
        <label for="product_name" class="form-label">Product Name</label>
        <input type="text" class="form-control" id="product_name" name="product_name" required>
    </div>
    <div class="mb-3">
        <label for="quantity" class="form-label">Quantity</label>
        <input type="number" class="form-control" id="quantity" name="quantity" required>
    </div>
    <div class="mb-3">
        <label for="reorder_level" class="form-label">Reorder Level</label>
        <input type="number" class="form-control" id="reorder_level" name="reorder_level" required>
    </div>
    <button type="submit" class="btn btn-primary">Add Product</button>
</form>
<?php require '../includes/footer.php'; ?>

Order Management (public/orders.php)


<?php
session_start();
require '../config/db.php';
require '../includes/header.php';
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $user_id = $_SESSION['user_id'];
    $product_name = $_POST['product_name'];
    $quantity = $_POST['quantity'];
    $stmt = $conn->prepare("INSERT INTO orders (user_id, product_name, quantity) VALUES (?, ?, ?)");
    $stmt->bind_param("isi", $user_id, $product_name, $quantity);
    $stmt->execute();
    $stmt->close();
    header("Location: orders.php");
}
?>
<h2>Manage Orders</h2>
<form method="POST" action="">
    <div class="mb-3">
        <label for="product_name" class="form-label">Product Name</label>
        <input type="text" class="form-control" id="product_name" name="product_name" required>
    </div>
    <div class="mb-3">
        <label for="quantity" class="form-label">Quantity</label>
        <input type="number" class="form-control" id="quantity" name="quantity" required>
    </div>
    <button type="submit" class="btn btn-primary">Place Order</button>
</form>
<?php require '../includes/footer.php'; ?>

Supplier Management (public/suppliers.php)


<?php
session_start();
require '../config/db.php';
require '../includes/header.php';
if (!isset($_SESSION['user_id']) || $_SESSION['role'] !== 'admin') {
    header("Location: login.php");
    exit();
}
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $name = $_POST['name'];
    $contact_info = $_POST['contact_info'];
    $performance_metrics = $_POST['performance_metrics'];
    $stmt = $conn->prepare("INSERT INTO suppliers (name, contact_info, performance_metrics) VALUES (?, ?, ?)");
    $stmt->bind_param("sss", $name, $contact_info, $performance_metrics);
    $stmt->execute();
    $stmt->close();
    header("Location: suppliers.php");
}
?>
<h2>Manage Suppliers</h2>
<form method="POST" action="">
    <div class="mb-3">
        <label for="name" class="form-label">Supplier Name</label>
        <input type="text" class="form-control" id="name" name="name" required>
    </div>
    <div class="mb-3">
        <label for="contact_info" class="form-label">Contact Information</label>
        <input type="text" class="form-control" id="contact_info" name="contact_info" required>
    </div>
    <div class="mb-3">
        <label for="performance_metrics" class="form-label">Performance Metrics</label>
        <textarea class="form-control" id="performance_metrics" name="performance_metrics"></textarea>
    </div>
    <button type="submit" class="btn btn-primary">Add Supplier</button>
</form>
<?php require '../includes/footer.php'; ?>

Shipment Tracking (public/shipments.php)


<?php
session_start();
require '../config/db.php';
require '../includes/header.php';
if (!isset($_SESSION['user_id']) || $_SESSION['role'] !== 'admin') {
    header("Location: login.php");
    exit();
}
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $order_id = $_POST['order_id'];
    $shipment_date = $_POST['shipment_date'];
    $status = $_POST['status'];
    $tracking_number = $_POST['tracking_number'];
    $stmt = $conn->prepare("INSERT INTO shipments (order_id, shipment_date, status, tracking_number) VALUES (?, ?, ?, ?)");
    $stmt->bind_param("isss", $order_id, $shipment_date, $status, $tracking_number);
    $stmt->execute();
    $stmt->close();
    header("Location: shipments.php");
}
?>
<h2>Manage Shipments</h2>
<form method="POST" action="">
    <div class="mb-3">
        <label for="order_id" class="form-label">Order ID</label>
        <input type="number" class="form-control" id="order_id" name="order_id" required>
    </div>
    <div class="mb-3">
        <label for="shipment_date" class="form-label">Shipment Date</label>
        <input type="date" class="form-control" id="shipment_date" name="shipment_date" required>
    </div>
    <div class="mb-3">
        <label for="status" class="form-label">Status</label>
        <select class="form-select" id="status" name="status" required>
            <option value="in_transit">In Transit</option>
            <option value="delivered">Delivered</option>
            <option value="returned">Returned</option>
        </select>
    </div>
    <div class="mb-3">
        <label for="tracking_number" class="form-label">Tracking Number</label>
        <input type="text" class="form-control" id="tracking_number" name="tracking_number" required>
    </div>
    <button type="submit" class="btn btn-primary">Add Shipment</button>
</form>
<?php require '../includes/footer.php'; ?>

Analytics Management (public/analytics.php)


<?php
session_start();
require '../config/db.php';
require '../includes/header.php';
if (!isset($_SESSION['user_id']) || $_SESSION['role'] !== 'admin') {
    header("Location: login.php");
    exit();
}
// Fetch analytics data
$stmt = $conn->prepare("SELECT * FROM analytics");
$stmt->execute();
$result = $stmt->get_result();
$analytics = $result->fetch_all(MYSQLI_ASSOC);
?>
<h2>Analytics Dashboard</h2>
<table class="table">
    <thead>
        <tr>
            <th>Metric Name</th>
            <th>Value</th>
            <th>Date</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($analytics as $data): ?>
        <tr>
            <td><?php echo $data['metric_name']; ?></td>
            <td><?php echo $data['value']; ?></td>
            <td><?php echo $data['recorded_at']; ?></td>
        </tr>
        <?php endforeach; ?>
    </tbody>
</table>
<?php require '../includes/footer.php'; ?>

Quality Control Management (public/quality_control.php)


<?php
session_start();
require '../config/db.php';
require '../includes/header.php';
if (!isset($_SESSION['user_id']) || $_SESSION['role'] !== 'admin') {
    header("Location: login.php");
    exit();
}
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $product_name = $_POST['product_name'];
    $inspection_date = $_POST['inspection_date'];
    $result = $_POST['result'];
    $comments = $_POST['comments'];
    $stmt = $conn->prepare("INSERT INTO quality_control (product_name, inspection_date, result, comments) VALUES (?, ?, ?, ?)");
    $stmt->bind_param("ssss", $product_name, $inspection_date, $result, $comments);
    $stmt->execute();
    $stmt->close();
    header("Location: quality_control.php");
}
?>
<h2>Manage Quality Control</h2>
<form method="POST" action="">
    <div class="mb-3">
        <label for="product_name" class="form-label">Product Name</label>
        <input type="text" class="form-control" id="product_name" name="product_name" required>
    </div>
    <div class="mb-3">
        <label for="inspection_date" class="form-label">Inspection Date</label>
        <input type="date" class="form-control" id="inspection_date" name="inspection_date" required>
    </div>
    <div class="mb-3">
        <label for="result" class="form-label">Result</label>
        <select class="form-select" id="result" name="result" required>
            <option value="pass">Pass</option>
            <option value="fail">Fail</option>
        </select>
    </div>
    <div class="mb-3">
        <label for="comments" class="form-label">Comments</label>
        <textarea class="form-control" id="comments" name="comments"></textarea>
    </div>
    <button type="submit" class="btn btn-primary">Add Quality Control Record</button>
</form>
<?php require '../includes/footer.php'; ?>

6. Additional Features to Consider

User Profiles: Allow users to edit their profiles and manage their settings.

Advanced Reporting: Implement more sophisticated reporting features for better insights.

Mobile Responsiveness: Ensure the application is fully responsive for mobile users.

7. Security Measures

Input Validation: Ensure all user inputs are validated to prevent SQL injection and XSS attacks.

Password Security: Use strong hashing algorithms for storing passwords.

Session Security: Implement secure session management practices to protect user sessions.

8. Testing and Deployment

Unit Testing: Conduct unit tests for individual components to ensure they function correctly.

Integration Testing: Test the integration of different modules to ensure they work together seamlessly.

Deployment: Choose a reliable hosting provider and deploy the application, ensuring all configurations are optimized for performance.

9. Documentation

User Documentation: Create a user manual to guide users through the platform's features and functionalities.

Developer Documentation: Document the codebase and architecture for future reference and maintenance.

10. Future Enhancements

AI-Powered Insights: Implement machine learning algorithms to provide insights into supply chain performance.

Mobile Application: Develop a mobile application for users to access the platform on the go.

Integration with Other Systems: Consider integrating with existing ERP or CRM systems for a more comprehensive solution.

This structured approach will help you build a comprehensive Supply Chain Tracking System that meets user needs and adapts to future requirements.