## 3 Materials and Methods
### Design of Storage Scheme for Manufacturing Data  
With the transformation of traditional factories to intelligent ones, the amount of data in factories has exploded, and the demand for real-time data processing has increased. It is particularly important to choose a suitable database optimization strategy. A reasonable design of the structure of the database table can improve the performance and efficiency of the database, reduce the association operation of the table, and improve the system response speed. On the other hand, different data types can be selected according to the characteristics of different data, and the use of overly large or small data types can be minimized to reduce the waste of storage space and improve query efficiency. At the same time, appropriate primary keys and foreign keys can be selected for each table to ensure the integrity and consistency of the data and avoid using too many or too long primary keys and foreign keys to reduce the storage and maintenance costs of indexes.  
This study is designed to support the data collection architecture of the entire production workshop, because there are many devices in the production workshop. Equipment with the same specifications will be distributed in different stations in different production and manufacturing units. The information of each equipment to be detected includes the equipment number, node ID, UUID, station ID (PLC_ID), URL, manufacturing unit ID, name, and location, etc. Therefore, this study uses the information of the whole workshop to split into a number of smaller tables for decentralized storage, carries out a reasonable table sharding design according to the characteristics of the data, and divides each equipment to be detected into three tables of different dimensions for storage and classification, which is sorted in a tree shape, as shown in Figure 10.  
The bottom-level table named app01_node contains some basic information of the device, such as the node value and the UUID sequence that uniquely identifies the device. The middle-level table app01_plc contains the station information of the device, such as the station number and the URL for the OPC UA client connection. The top-level ta  
Figure 10: E-R diagram of database tables.  
Figure 9: Multithreaded parallel consumption.  
ble app01_machine contains the name, number, and location of the manufacturing unit to which the station belongs. Tables app01_machine and app01_plc select the id column as the primary key. Tables app01_plc and app01_node select the parent column as the foreign key and then associate the foreign key with the primary key of the previous layer so as to associate all tables together to reduce the amount of data and the number of indexes of a single table, thereby improving query efficiency.  
The database side also integrates the program responsible for processing and writing message data into the table, covering the following key links:  
1. Start Kafka on the local database side, then connect to the database and query the device information in a table, and obtain the node information to be detected, including the node ID, the unique identifier UUID, the PLC_ID corresponding to the node, the connection address URL, and the machine ID.
2. Process and classify each of the above information, divide them by URL, integrate the nodes under the same connection address into the form of a dictionary in Python, optimize the data structure, and improve the PDC retrieval efficiency.
3. Convert the above integrated messages into JSON and send them to the Topic named \"query\". At the same time, different Topics will be created based on different PLC_IDs to optimize the production and consumption rate of messages.
4. Subscribe to Topics with different PLC_IDs. When consuming new messages, the current time will be automatically obtained, and then the current time will be written together with the message into the specified data table to achieve storage and backup of factory production data.