Warehouse Inventory System Model Upgrade and Performance Optimization

The company manages multiple material types and inventory values, initially integrating packaging consumables and product data. The goal is to manage inbound/outbound prices, quantities, and aging for traceability, improve financial control, and support planning and procurement.

After adding product inventory, data volume grew exponentially, exposing limitations in the original data model.

Problems identified: data hand‑off crossing core P0 links causing technical risk, redundant daily records in cost and ledger tables, and severe query latency for dashboard and ledger reports.

Solutions: 1) Directly ingest document‑pool data into the inventory application, eliminating the risky hand‑off. 2) Redesign storage: store only rows that change (e.g., cost price updates) instead of daily snapshots, reducing redundancy. 3) Sync transformed data to a data‑warehouse for daily report generation, then retrieve reports via a reporting platform.

The redesign saves approximately 30 hours of monthly accounting and audit time (≈30% of total), improves data accuracy, and provides new analytical capabilities such as turnover and channel analysis.

Technical gains include first‑time online TiDB switch‑over experience, decoupling high‑traffic warehouse data from settlement services, and upskilling the team on data‑warehouse and reporting tools.