Design and implementation of a new intelligent warehouse management system based on MySQL database technology

Ying Zhang, Feng PanInformatica 2022 · DOI

Abstract

In order to improve the material management level of enterprises, based on MySQL database technology, this paper makes an overall design of the warehouse management system, builds a MySQL database, and realizes the design and application of a new intelligent warehouse management system. Through the operation and test of the system, the results show that the system mainly realizes the five necessary functional modules of warehouse management: basic information management, system management, procurement management, warehousing management and inventory management. In the test, the system runs normally, the unit test and integration test can meet the expected requirements, realize the functions required by the user, and get the desired results within the user's acceptable response time (within 3S). It can provide automatic and comprehensive records for the whole process of material management of the enterprise, and provide real-time and correct information for all warehouse activities, resources and inventory levels.

Introduction

In modern industrial environments, efficient material management is critical for maintaining supply chain fluidity. Many enterprises suffer from a "full shelf" paradox, where warehouses appear physically congested despite having unutilized capacity, primarily due to disorganized racking and poor information tracking. This inefficiency forces personnel to spend excessive time locating items, increasing labor costs and market risks.

This paper addresses the performance gap in the manufacturing sector by proposing an Intelligent Warehouse Management System (WMS). The goal is to replace manual, error-prone processes with a digital solution that provides real-time data on inventory levels and warehouse resources, specifically tailored for small-to-medium enterprises requiring cost-effective yet robust technology.

System Architecture

The researchers implemented a Browser/Server (B/S) architecture utilizing a classic three-tier model to ensure scalability and ease of maintenance. This structure separates the user interface from the underlying business logic and data storage.

The three-tier architecture of the proposed WMS, including presentation, application, and data layers.
  1. Presentation Layer: A web-based interface developed with Ajax and Bootstrap. This layer handles data display and user interaction, ensuring a smooth experience through asynchronous requests that prevent unnecessary page refreshes.
  2. Application Layer: Built on the SSH Framework (Struts 2, Spring, and Hibernate). This layer manages the core business systems, including warehousing and outbound management.
  3. Data Layer: Powered by MySQL, this layer handles data exchange between the database and the application. MySQL was selected for its high performance, open-source nature, and low total cost of ownership.

Functional Design and Implementation

The system is organized into several core modules designed to cover the entire lifecycle of material management within a warehouse.

Overview of the core functional modules implemented in the warehouse management system.

Key functional modules include:

  • Purchase and Warehousing: Managing the intake of new materials.
  • Material Ex-Warehouse: Handling outbound deliveries and tracking material movement.
  • Material Handling: Managing internal movements and racking arrangements.
  • Query Statistics: Providing real-time reporting on inventory levels and activity logs.
  • Basic Information & System Management: Maintaining records of staff, departments, and system permissions.

A significant technical implementation detail is the use of database stored procedures for ID generation. To ensure data consistency and prevent collisions during high-concurrency operations, the system automatically generates unique material IDs formatted as "WZ + Year + 4-digit serial."

Performance and Results

The system underwent rigorous unit testing via JUnit and integration testing to ensure module compatibility. The results demonstrated that the system consistently met user requirements with a response time of under 3 seconds, a critical benchmark for operational efficiency in industrial settings. The deployment proved effective in both local development environments (using Tomcat v9.0) and networked production servers, confirming its readiness for enterprise use.

Conclusion and Future Work

The implemented WMS successfully automates material tracking and provides a stable, low-latency platform for warehouse operations. While the current iteration meets all primary functional requirements, future work will focus on integrating Artificial Intelligence (AI) for predictive inventory management and warehouse layout optimization. Additionally, deeper integration with Internet of Things (IoT) hardware, such as RFID tags and automated guided vehicles (AGVs), will be explored to further reduce manual labor and enhance automation.

Summary updated

Abstract

In order to improve the material management level of enterprises, based on MySQL database technology, this paper makes an overall design of the warehouse management system, builds a MySQL database, and realizes the design and application of a new intelligent warehouse management system. Through the operation and test of the system, the results show that the system mainly realizes the five necessary functional modules of warehouse management: basic information management, system management, procurement management, warehousing management and inventory management. In the test, the system runs normally, the unit test and integration test can meet the expected requirements, realize the functions required by the user, and get the desired results within the user's acceptable response time (within 3S). It can provide automatic and comprehensive records for the whole process of material management of the enterprise, and provide real-time and correct information for all warehouse activities, resources and inventory levels.
BibTeX
@inproceedings{zhang2022design,
  author = {Ying Zhang and Feng Pan},
  title = {{Design and implementation of a new intelligent warehouse management system based on MySQL database technology}},
  booktitle = {Informatica},
  year = {2022},
  doi = {10.31449/inf.v46i3.3968},
}
Figure