Quick Overview

Development and implementation of a relational database system to replace a manual inventory system for an air conditioning equipment company with multiple project sites and branches.

The Problem

Inefficient manual, paper-based inventory management system leading to a lack of real-time visibility, inventory inaccuracies, complex unit management, information silos, inefficient coordination, scalability limitations, reporting deficiencies, and access control issues across multiple project sites and branches.

The Solution

  • Designed and implemented a relational database using Microsoft Access to manage over 25,000 inventory items across 13 categories with diverse units of measure.
  • Enabled the identification of item quantities with a click of a button.
  • Facilitated the reservation of products for specific projects and tracked pending orders and deliveries.
  • Implemented specific tracking for heavy equipment delivered directly to project sites.
  • Provided real-time location and delivery status of inventory items.
  • Ensured consistent recording of quantities and their corresponding units of measure for products, spares, and heavy equipment.
  • Developed a system to provide inventory database access to employees across all branches and project locations.
  • Implemented role-based access control for security.
  • Supported the generation of comprehensive inventory reports.

The Solution

A relational database solution was implemented with the following key aspects:

  • Database Design: A well-normalized database schema was created using Microsoft Access. The database manages inventory data, including product details, supplier information, and storage locations. The database handles over 25,000 distinct items, categorized into 13 distinct types, including product categories such as HVAC Equipment, Pumps, Controls, Insulation, Piping systems, HVAC duct, tools, safety equipment, electrical products, and wires. The database allowed products to be reserved for specific projects, and also showed pending orders and deliveries. Inventory data was made accessible to all branches.

    • Direct Delivery Tracking: Specific tables and fields were implemented to record details of heavy equipment delivered directly to project sites.

    • Real-time Tracking: The system provides real-time location and delivery status of inventory items.

    • Data Handling: The system is designed to handle distinct data requirements for products, spares, and heavy equipment. The database ensures consistent recording of quantities and their corresponding units of measure.

    • Accessibility: A system was developed to provide employees across all branches and project locations with access to the inventory database.

    • Role-Based Access Control: A security system with role-based access control was implemented.

    • Reporting Capabilities: The database supports the generation of comprehensive inventory reports.

Key Features and Highlights

The implemented solution delivered the following key features and benefits:

  • Centralized Digital Inventory Data: A single source of truth for all inventory-related data.

  • Real-time Visibility: Access to up-to-date stock levels and delivery status.

  • Efficient Inventory Management: Improved accuracy and reduced logistical complexities.

  • Improved Allocation and Transfer: More efficient allocation of materials to projects.

  • Reduced Delays and Downtime: Minimized project delays due to material unavailability.

  • Data-Driven Decisions: Robust reporting capabilities for informed decision-making.

Results & Impact

The implementation of this centralized relational database for inventory management between 2004 and 2006 across multiple projects and complex storage/delivery scenarios within Loujien Air Conditioning Equipment’s operations, replacing the inefficient manual and paper-based system, resulted in significant improvements, including:

  • 46% reduction in time spent locating inventory items due to the digital search and location tracking.

  • 10% decrease in instances of project delays attributed to material unavailability due to improved digital visibility.

  • A 15% reduction in inventory-related errors due to the elimination of manual data entry.

  • Significantly improved efficiency and accuracy in inventory management across all branches.

  • Enhanced data-driven decision-making capabilities due to readily available and accurate digital reports.

Lessons Learned

  • This project, developed between 2004 and 2006 for Loujien Air Conditioning Equipment, was the company’s first step in digitalizing its business processes.

  • It highlighted the transformative impact of moving from a completely manual and paper-based system to a centralized digital database for complex inventory management.

  • The project demonstrated to everyone involved the value of digital data, particularly how it could be leveraged and used to build valuable information.

  • This realization encouraged stakeholders to request the development of other systems to further digitize and improve other business functions.