An industrial ERP / MES-oriented low-redundancy minimal data table design method and system

Abstract

The application discloses a low-redundancy minimal data table design method and system for industrial ERP / MES, and belongs to the technical field of industrial digitization and database architecture. The application realizes low redundancy, low association, high performance and easy maintenance of the data table through full-service field collection, default single-table aggregation, controllable split constraint, weak association mapping, unified data entry and minimal query control. The application forces single-table priority, splits at most into two tables, and performs at most one association in regular query, thereby solving the problems of excessive split, complex association, poor performance and difficult maintenance in traditional design from the root, and has the advantages of minimalism, high efficiency, stability and easy expansion, and is suitable for various ERP / MES / WMS industrial management systems and has been applied in actual production environment.

Description

Technical Field

[0001] This invention relates to the fields of industrial digitalization, database architecture design, enterprise-level software development, and integrated ERP / MES / WMS systems. Specifically, it relates to a data table design method and system that is oriented towards industrial production and operation scenarios, reduces data redundancy, simplifies table structure, and improves performance and maintainability. Background Technology

[0002] In the database design of industrial ERP, MES, WMS and other management systems, traditional solutions generally follow the design approach of "over-splitting, multi-layered association, and complex constraints," resulting in a series of difficult-to-solve technical defects in the system: The data tables are excessively fragmented. A single business document is split into multiple physical tables, including a main table, detail table, extended table, status table, log table, and relational mapping table, resulting in severe structural redundancy. Business queries must rely on a large number of JOINs, subqueries, and nested associations, resulting in complex SQL, poor performance, and a high risk of lag when dealing with large amounts of data. With strong inter-table dependencies and numerous foreign key constraints, data insertion, update, and deletion are prone to triggering cascading exceptions, resulting in extremely high data consistency maintenance costs. The development, debugging, and maintenance are difficult, and R&D personnel spend a lot of their energy on handling table relationships rather than implementing business logic. The system has poor scalability; changes in requirements require simultaneous modifications to multiple tables, multiple relationships, and multiple logics, resulting in high transformation costs and a high bug rate. Data from multiple systems (ERP / MES / WMS) is difficult to integrate, cross-module interoperability is complex, and natural integration cannot be achieved; In production environments with large data volumes and high concurrency, complex table structures lead to difficulties in index maintenance, slow backup and recovery, and high operation and maintenance costs.

[0003] The aforementioned problems have long existed in the industrial software field. The industry regards "splitting, associating, and connecting" as the default standard, resulting in meaningless technical redundancy and severely restricting the stability, efficiency, and delivery speed of industrial digital systems. Summary of the Invention

[0004] The purpose of this invention is to overcome the shortcomings of traditional database design and provide a low-redundancy, minimalist data table design method and system for industrial ERP / MES. By adopting strategies such as single-table priority, controllable partitioning, weak association, unified entry, and minimalist query, redundancy is reduced from the root, the structure is simplified, performance is improved, and maintenance costs are reduced, thereby achieving a minimalist, stable, efficient, and easily scalable data layer for industrial software. I. Overall Technical Solution

[0005] A low-redundancy, minimalist data table design method for industrial ERP / MES includes: S100: Business-wide Field Collection For business objects such as production work orders, purchase contracts, sales orders, inbound and outbound slips, and quality inspection slips, conduct a full lifecycle field analysis, collect all relevant fields such as status, process, amount, quantity, personnel, equipment, time, and attachments, and form a complete and comprehensive set of business fields.

[0006] S200: Default single-table aggregation strategy The highest priority design principle is to use a single table to carry out the entire business. All fields are aggregated into a main business table without any meaningless physical splitting, so that a single table can complete all business read and write operations without the need for joins.

[0007] S300: Controllable splitting threshold constraint Splitting is only permitted when explicit optimization goals are met, including: separation of hot and cold data, separation of large fields (text / JSON / images), read / write performance optimization, and historical data archiving requirements.

[0008] Mandatory constraints: The number of physical tables after splitting shall not exceed 2, and only the structure of main table + extension table is allowed. Splitting of 3 or more tables is prohibited.

[0009] S400: Redundant, Weakly Associated Design The split tables are only weakly mapped using unique IDs, without physical foreign keys, cascading updates, or cascading deletes. Strong constraints are not used to ensure consistency; data correctness is ensured through business logic and stored procedures.

[0010] S500: Unified Entry Packaging By using stored procedures, views, table-valued functions, or unified data access components, a single data entry point can be provided to the upper layer, so that the front-end and service layers do not need to know whether the underlying layer is a single table or a dual table, thus achieving structural transparency.

[0011] S600: Minimalist Query Rule Control Regular business queries are subject to mandatory constraints: zero or at most one join, two or more levels of nested joins are strictly prohibited, and multi-table chained joins are strictly prohibited, ensuring that the SQL is extremely simple, efficient, stable and controllable.

[0012] S700: Cross-database integrated compatibility support It supports single-database, dual-database, and multi-database architectures on the same server, and achieves data access through direct cross-database SQL connections, without using intermediate databases, message queues, APIs, or ETL synchronization, maintaining a minimalist and integrated architecture. II. System Composition

[0013] A low-redundancy, minimalist data table design system for industrial ERP / MES includes: Business Field Acquisition Module: Used for collecting, organizing, and classifying business fields throughout their entire lifecycle; Single-table aggregation configuration module: Executes the single-table priority strategy and automatically generates the complete main table structure; Controllable splitting constraint module: Determines whether to split based on a threshold, and strictly limits it to 1 to 2 tables; Weak association mapping module: Establishes ID mapping between tables without foreign keys or mandatory constraints; Unified data entry module: Provides encapsulation of views, stored procedures, and data components; Minimalist query control module: Limits the number of joins to ensure efficient and concise queries. III. Beneficial Effects

[0014] Extremely simple structure: single table priority, eliminating excessive splitting at the root, and significantly reducing the number of tables; Performance improvements: Queries with no or only one join are simpler, with more efficient indexes and stronger concurrency. Extremely easy to maintain: Reduces the complexity of table relationships, significantly decreasing development / debugging / modification costs; High stability: No strong foreign keys or chaining constraints, making data operations safer and reducing anomalies; Flexible and adaptable: The structure is transparent, changes in requirements do not affect the upper-level system, and it can be adapted and iterated quickly. Integrated and naturally supported: suitable for ERP / MES / WMS integration, and easy cross-module data transfer; Highly engineered: It has been implemented in industrial production systems and is adapted to big data, high concurrency, and multi-production line scenarios. Detailed Implementation

[0015] Using "production work orders" as the business object, collect all fields such as work order status, materials, equipment, processes, plans, actual, quality inspection, quantity, weight, personnel, time, and remarks; It generates a single-table structure by default, containing all fields, and supports direct CRUD operations without the need for joins; If there are large field comments or JSON extended information, the splitting rule will be triggered, splitting the work order into: a main work order table + an extended work order table, for a total of 2 tables; The two tables are only weakly linked by the work order ID, and no foreign keys are set. Create a view or stored procedure to encapsulate the two tables, presenting a single data entry point to the outside world; Business queries should use only 0 or 1 JOIN operations, and complex associations are prohibited. It supports direct cross-database connections, does not use middleware, and maintains a minimalist architecture.

Claims

1. A low-redundancy, minimalist data table design method for industrial ERP / MES, characterized in that, Includes the following steps: S1: Collect all lifecycle fields of the target business object to form a complete set of business fields; S2: Use single-table aggregation as the default strategy to integrate all business fields into a single main business table; S3: Only when the preset splitting conditions are met, data can be split into no more than two physical tables, and only splitting can be done according to non-business dimensions; S4: The split physical tables are only weakly linked through unique keys, without setting physical foreign keys or mandatory constraints; S5: Provides a unified data entry point to the upper layer through stored procedures or views, and ensures that regular business queries involve at most one table join.

2. The method according to claim 1, characterized in that, The target business objects include production work orders, purchase orders, sales orders, contracts, warehouse receipts, warehouse releases, quality inspection reports, equipment records, and material files.

3. The method according to claim 1, characterized in that, The preset splitting conditions include: hot and cold data separation, large field separation, read and write performance optimization, historical data archiving, and separation of high-frequency and low-frequency fields.

4. The method according to claim 1, characterized in that, The non-business dimensions include: data access frequency, update frequency, storage volume, field type, and historical migration strategy.

5. The method according to claim 1, characterized in that, The weak associations are established only through primary keys or unique keys and do not include cascading updates, cascading deletions, or foreign key constraints.

6. The method according to claim 1, characterized in that, The at most one table join includes any one of LEFT JOIN, INNER JOIN, and RIGHT JOIN.

7. The method according to claim 1, characterized in that, The method supports deployment of single, dual, and multiple databases on the same server, and achieves data access through direct cross-database SQL connections without relying on middleware, interfaces, or data synchronization components.

8. A low-redundancy, minimalist data table design system for industrial ERP / MES, characterized in that, include: Business field acquisition module, single table aggregation configuration module, controllable splitting constraint module, weak association mapping module, unified data access entry module, and minimalist query control module; The system is used to perform the method described in any one of claims 1 to 7.