Data processing system
By automatically adjusting the table field types through the expandable database table, the problems of labor burden and high maintenance costs under large-scale data changes are solved, and automated table structure adjustment and merging are achieved.
Patent Information
- Application Number
- CN202410295104.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-03-14
- Publication Date
- 2025-09-16
AI Technical Summary
When dealing with large-scale data changes, existing technologies require manual adjustment of the data table structure, which leads to high manpower burden and maintenance costs, and may also lead to the complexity of the data structure and the risk of program modification.
A data processing system is provided that automatically adjusts the data table field type through an expandable database data table, automatically adds or adjusts fields according to the content of uploaded data, and merges data tables at appropriate times.
It realizes the automatic adjustment of data table structure, reduces manpower requirements, lowers maintenance costs, and avoids the risks of data structure complexity and program modification.
Smart Images
Figure CN120653643A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to a data processing system, and more particularly to a data processing system capable of automatically adjusting a data table structure according to data content. Background Art
[0002] With the digitization of information and the development of digital applications, digital transformation is imminent, and database access is a necessity across all sectors. To meet the diverse needs of data applications, database types have also flourished. Among them, relational databases such as Oracle, MySQL, and SQL Server remain the mainstream in the industry. Relational databases typically store data in multiple tables, with predefined and rigorous structured relationships between the data within and between tables.
[0003] In the past, before users wanted to store data in a database, they often needed to confirm their needs with database technicians. Database technicians would plan the data table structure to create a data table that meets the user's needs, including fields, data types, and associations. Once the data table is set up and a certain amount of data is stored, if there is a subsequent need to change the data table structure, it is still necessary to rely on database technicians to adjust the data structure. However, with the development of digital applications, data providers are often unable to predict the final data storage content and type. When the frequency of data changes increases, database technicians will face considerable manpower burdens and database change costs. This problem is particularly serious when using a relational database.
[0004] In this situation, database technicians typically respond to changes in data structure by resetting data tables or adding new tables. However, resetting data tables is not suitable for situations with large amounts of data, while adding new tables may complicate the data structure and require corresponding modifications to pre-existing programs, resulting in increased maintenance costs. Therefore, there is an urgent need to establish a method that can automatically adjust existing data tables based on the data content uploaded by users, thereby saving manpower and reducing costs. Summary of the Invention
[0005] Therefore, the main purpose of the present invention is to provide a data processing system that can identify data content and automatically adjust the data table field type to improve the shortcomings of the conventional technology.
[0006] An embodiment of the present invention discloses a data processing system connected to a database for processing uploaded data, the uploaded data including data content and a data name. The data processing system includes a processing unit and a storage unit. The processing unit is configured to execute a program code; the storage unit is coupled to the processing unit and configured to store the program code to instruct the processing unit to execute a data processing method. The data processing method includes querying an extended database data table based on the data name to obtain first data based on a first serial number; comparing the field type of the data content with the first data to obtain a comparison result; in response to the comparison result indicating that the field type of the data content matches the first data, obtaining a first data table corresponding to the first data; in response to the comparison result indicating that the field type of the data content does not match the first data, creating the first data table based on the field type of the data content and a second serial number; storing second data corresponding to the first data table and the second serial number in the extended database data table; storing the data content in the first data table; and merging data tables based on the data name. BRIEF DESCRIPTION OF THE DRAWINGS
[0007] Figure 1 FIG. 1 is a schematic diagram of a data processing system processing uploaded data according to an embodiment of the present invention.
[0008] Figure 2 FIG. 4 is a schematic diagram of a data processing flow according to an embodiment of the present invention.
[0009] Figure 3 FIG. 4 is a schematic diagram of a data table merging process according to an embodiment of the present invention.
[0010] Figure 4 FIG. 4 is a schematic diagram of a data processing system according to an embodiment of the present invention.
[0011] Component number description
[0012] 10Data processing system
[0013] 12 users
[0014] 14Database
[0015] 16Upload data
[0016] 20Data processing flow
[0017] Steps 200-214
[0018] 30 Data table merging process
[0019] Steps 300-318
[0020] 400 processing units
[0021] 410 storage units
[0022] 412 program code
[0023] 420 Database DETAILED DESCRIPTION
[0024] Certain terms are used throughout this specification and the claims that follow to refer to specific components. A person skilled in the art will understand that hardware manufacturers may use different terms to refer to the same component. This specification and the claims that follow do not distinguish components by name, but rather by their functional differences. Throughout this specification and the claims that follow, the term "including" is an open-ended term and should be interpreted as meaning "including, but not limited to."
[0025] Please refer to Table 1, which shows the field type description of a customer data table (customer). The customer data table initially contains only three fields: the customer's last name (lastName), gender (gender), and identity identifier (id).
[0026] Table 1
[0027] Field Type Null Key Default Extra lastName varchar(4) YES NULL gender varchar(1) YES NULL id char(10) YES NULL
[0028] According to the field type description in Table 1, the customer data table can be used to store customer data as shown in Table 2 below.
[0029] Table 2
[0030] lastName gender id Chen M A111111111 An F B222222222
[0031] However, as business needs change, users may need to store information related to the customer's age. In this case, database technicians need to adjust the fields of the customer data table according to the new requirements, as shown in Table 3.
[0032] Table 3
[0033]
[0034]
[0035] Based on the field type descriptions in Table 3, users can store customer data including their age. However, as business expands, users may need to store data related to foreign customers. In this case, the last name field in the customer data table in Table 3 becomes insufficient in length. Database technicians must adjust the fields in the customer data table to meet these new requirements, as shown in Table 4.
[0036] Table 4
[0037] Field Type Null Key Default Extra lastName varchar(10) YES NULL gender varchar(1) YES NULL id char(10) YES NULL [[ID=4 5]]age float(3,1) YES NULL
[0038] Based on this, users can store foreign customer information as shown in Table 5 below in the modified customer data table.
[0039] Table 5
[0040] lastName gender id age Aniston M E299912343 55.0 Geller F K199987654 66.0
[0041] Since changes to table field types usually occur when the table already contains data, database technicians usually need to export existing data, create a new customer data table, add missing field information, and re-import the data to complete the change.
[0042] Furthermore, when the amount of data is large, database technicians may create new tables to accommodate changes in table field types. For example, when adding a new customer age field, as described above, database technicians could create a separate CustomerAge table (see the field type description in Table 6 below) and then join it with the Customer table using the identity identifier (id) as the primary key. However, this approach may increase the complexity of the relationships between tables and also require modifications to the relevant stored procedures, which risks compromising the integrity of the data structure.
[0043] Table 6
[0044] Field Type Null Key Default Extra id char(10) YES NULL age float(3,1) YES NULL
[0045] Therefore, an embodiment of the present invention provides a data processing system that can analyze the structure of user uploaded data and automatically adjust the field type of the data table accordingly. Please refer to Figure 1, which is a schematic diagram of a data processing system 10 processing uploaded data according to an embodiment of the present invention. As shown in Figure 1, a user 12 can access a database 14 through the data processing system 10 and transmit an uploaded data 16 to the data processing system 10 for processing. In an embodiment of the present invention, the database 14 can be any relational database such as Oracle, MySQL, SQL Server, etc., but is not limited thereto. The uploaded data 16 includes a data name and a data content, wherein the data name is a data table name of the data table to be accessed, and the data content is structured data. It should be noted that in an embodiment of the present invention, the data processing system 10 must have the authority to access the database 14, including the authority to create and delete data tables.
[0046] In an embodiment of the present invention, the data processing system 10 must pre-establish an extended database table, wherein the field type description may be as shown in Table 7 below. The fields of the extended database table must at least include the table name (table_name), the field name (column_name), the field data type (hereinafter referred to as the field type) (type), and the sequence number (seq). It should be noted that Table 7 only lists the necessary fields required to implement the embodiment of the present invention. The field name, number of fields, field type, etc. can be adjusted according to actual needs and are not limited to this.
[0047] Table 7
[0048]
[0049]
[0050] In an embodiment of the present invention, the data processing system 10 can obtain the table name, all fields contained in each table, and the field type of each field from the database 14, and store them in an extended database table according to the field type description shown in Table 7. Taking the customer table in Table 1 as an example, the data processing system 10 can store the data shown in Table 8 in the extended database table, with the serial number starting at 0. Based on the extended database table, the data processing system 10 can obtain the table name, field name, field type, and corresponding serial number for all tables in the database 14, and use this information to determine whether it is necessary to adjust the table's field type based on the uploaded data 16.
[0051] Table 8
[0052] table_name column_name type seq customer lastName varchar(4) 0 customer gender varchar(1) 0 customer id char(10) 0
[0053] As previously mentioned, the embodiment of the present invention can be used to process uploaded data 16 and automatically adjust the field types of the relevant data tables in the database 14 based on the uploaded data 16. As shown in FIG. 2 , the data processing method of the embodiment of the present invention can be summarized as a data processing flow 20, which includes the following steps:
[0054] Step 200: Start.
[0055] Step 202: Search the extended database table according to the data name to obtain a first data according to a first serial number.
[0056] Step 204: Compare the field type of the data content with the first data to obtain a comparison result. If the comparison result indicates that the field type of the data content matches the first data, execute step 206; otherwise, execute step 208.
[0057] Step 206: Obtain a first data table corresponding to the first data.
[0058] Step 208: Create a first data table according to the field type of the data content and a second serial number, and store a second data corresponding to the first data table and the second serial number in the extended database data table.
[0059] Step 210: Store the data content in the first data table.
[0060] Step 212: Merge the data tables based on the data names.
[0061] Step 214: End.
[0062] According to the data processing process 20, after receiving the uploaded data 16 from the user 12, the data processing system 10 first searches the extended database table based on the data name of the uploaded data 16. Among the data whose data names match the data table name, the data processing system 10 selects a first data with the largest serial number (step 202). The first data may include a data table name, a first serial number (e.g., N), at least one domain name, and its corresponding field type. Next, the data processing system 10 compares the field types of the data content of the uploaded data 16 with the first data and obtains a comparison result (step 204). If the comparison result indicates that the field types of the data content of the uploaded data 16 match those of the first data, the data processing system 10 may determine to use the data table corresponding to the data table name in the first data as the first data table (step 206). When the comparison result indicates that the field type of the data content of the uploaded data 16 does not match the first data, the data processing system 10 can create a first data table based on the field type of the data content of the uploaded data 16 and a second serial number, and add the data table name, second serial number, each domain name and field type of the first data table to the expandable database data table (step 208). The second serial number can be obtained by incrementing the first serial number (for example, N+1). Finally, the data processing system 10 can store the data content of the uploaded data 16 in the first data table (step 210) and merge the data tables according to the data name at an appropriate time (step 212). Accordingly, users can upload data content with newly added fields or modified field types in real time without having to wait for database technicians to manually modify the data table or complete data integration before uploading the data.
[0063] Specifically, in step 202, the data processing system 10 searches the extended database data table based on the data name to obtain the first data based on the first serial number. When searching the extended database data table, the data processing system 10 searches for data whose table name prefix in the data table name field matches the data name of the uploaded data 16. The largest value in the corresponding serial number field is selected as the first serial number, and the data corresponding to the first serial number is designated as the first data. The first data describes the most recent data table in the data table with the corresponding data name, and has the most fields or the longest data length (e.g., string length).
[0064] In step 204, the data processing system 10 compares the field types of the data content of the uploaded data 16 with the first data to obtain a comparison result. In this step, the data processing system 10 compares the field types of the data content of the uploaded data 16 one by one to determine whether the data content of the uploaded data 16 is compatible with the data table corresponding to the first data. If the comparison result indicates that the field types of the data content of the uploaded data 16 are compatible with the first data, it means that the data content of the uploaded data 16 can be directly stored in the data table corresponding to the first data. Therefore, the data processing system 10 can determine in step 206 that the data table corresponding to the first data is the first data table. If the comparison result indicates that the field types of the data content of the uploaded data 16 are not compatible with the first data, it means that the data content of the uploaded data 16 may include a newly added field or that the field types of existing fields have been changed. In this case, the data processing system 10 needs to create an extended data table to store the data content of the uploaded data 16 . Therefore, in step 208 , a first data table for storing the data content of the uploaded data 16 can be created according to the field type of the data content of the uploaded data 16 .
[0065] Specifically, in step 208, the data processing system 10 obtains a second serial number based on the first serial number. The second serial number may be incremented (e.g., by 1) based on the first serial number. Accordingly, the data processing system 10 determines the table name of the first data table using the data name of the uploaded data 16 as a prefix and the second serial number as a suffix. Taking the customer data table in Table 8 as an example, if the field type of the data content of the uploaded data 16 does not match the first data (e.g., a newly added field for customer age), the data processing system 10 may obtain the second serial number as 1 in step 208 and, accordingly, create a first data table named "customer_expand_1." The second data corresponding to the first data table and the second serial number is stored in the expansion database table, as shown in Table 9 below.
[0066] Table 9
[0067] table_name column_name type seq customer_expand_1 lastName varchar(4) 1 customer_expand_1 gender varchar(1) 1 customer_expand_1 id char(10) 1 customer_expand_1 age float(3,1) 1
[0068] Next, in step 210, the data processing system 10 may store the data content of the uploaded data 16 in the first data table determined in steps 204 to 208. Accordingly, the user 12 may store the uploaded data 16 without modifying the existing data.
[0069] Finally, in step 212, the data processing system 10 may merge data tables based on the data name at an appropriate time. In other words, the data processing system 10 merges all the extended data tables created in step 208. Appropriate times include merging data tables when the data processing system 10 receives a user request to obtain (GET), update (Put), or delete (Delete) the content of a data table associated with the data name, or merging data tables based on a predetermined time or period (e.g., non-working days). The data table merging method can be summarized as a data table merging process 30 as shown in Figure 3. The data table merging process 30 includes the following steps:
[0070] Step 300: Start.
[0071] Step 302: Search the extended database table according to a data name to obtain a third data according to a third serial number, wherein the third serial number is a maximum serial number.
[0072] Step 304: Obtain a fourth serial number, wherein the fourth serial number is incremented according to the third serial number.
[0073] Step 306: Create a second data table according to the third data and the fourth serial number.
[0074] Step 308: Import the data contents of the data tables corresponding to the serial numbers into the second data table in order from small to large, and record the number of data entries in each data table.
[0075] Step 310: Determine whether the number of records in the second data table is equal to the sum of the number of records in each data table. If so, execute step 314; otherwise, execute step 312.
[0076] Step 312: Delete the second data table and execute step 306.
[0077] Step 314: Delete the data table corresponding to the serial number less than the fourth serial number, and modify the data table name of the second data table to the data name.
[0078] Step 316: Update the extended database table.
[0079] Step 318: End.
[0080] According to the data table merging process 30 , the data processing system 10 merges previously created extended data tables.
[0081] Specifically, in step 302, the data processing system 10 searches the extended database table based on the specified data name and obtains a third serial number with the largest serial number from the data whose data name matches the data table name, as well as a third data corresponding to the third serial number. In this embodiment, "the data name matches the data table name" refers to the case where a prefix of the data table name in the data table name field of the extended database table is identical to the specified data name. In other words, based on the data name, the data processing system 10 can search the original data table with the same data table name as the data name, as well as all extended data tables created in step 208, and select the data with the largest serial number as the third data. The third data includes the data table name of the latest extended data table, the third serial number, at least one domain name, and its corresponding field type.
[0082] In steps 304-306, the data processing system 10 obtains a fourth serial number based on the third serial number and creates a second data table for merging based on the third data and the fourth serial number. The fourth serial number is incremented by the third serial number, i.e., the fourth serial number is calculated by adding 1 to the third serial number. The data processing system 10 determines the data table name for the second data table using the data name as a prefix and the fourth serial number as a suffix, and creates the second data table based on the information recorded in the third data.
[0083] In step 308, the data processing system 10 imports the data contents of the data tables corresponding to the serial numbers into the second data table in order from small to large according to the serial numbers, and records the number of data entries in each data table. In this embodiment, the data processing system 10 imports data in order from small to large according to the serial numbers, which can avoid the efficiency of subsequent data access being affected by the mixing of new and old data. Recording the number of data entries in each imported data table is to ensure that all data is completely imported into the second data table. In steps 310 to 312, the data processing system checks whether the number of data entries in the second data table is equal to the sum of the number of data entries in each imported data table. If the number of data entries does not match, the data processing system can delete the second data table and re-execute step 306.
[0084] After the data is successfully imported in step 308, in step 314, the data processing system 10 deletes the data tables corresponding to serial numbers less than the fourth serial number and changes the table name of the second data table to the data name. In other words, the data processing system 10 first deletes all data tables that have successfully imported data into the second data table and then changes the table name of the second data table to the data name.
[0085] Finally, in step 316, the data processing system 10 updates the extended database table. Specifically, the data processing system 10 first deletes the data in the extended database table whose table name prefix matches the data name and whose corresponding serial number is less than the third serial number. The system then modifies the data corresponding to the third serial number by changing the table name to the data name and changing the serial number field to 0.
[0086] Based on this, the data processing system 10 can merge data scattered across multiple data tables. Taking the aforementioned customer data table as an example, after adding the age field and modifying the last name field according to the data processing process 20, the extended database data table will record the data shown in Table 10.
[0087] Table 10
[0088]
[0089]
[0090] The data with serial number 0 in the serial number field (seq) corresponds to the original customer data table (customer), which includes a last name field (lastName) of type "varchar(4)", a gender field (gender) of type "varchar(1)", and an identifier field (lastName) of type "char(10)". The data with serial number 1 in the serial number field (seq) corresponds to the expanded customer data table (customer_expand_1) with the newly added age field, which includes a last name field (lastName) of type "varchar(4)", a gender field (gender) of type "varchar(1)", an identifier field (lastName) of type "char(10)", and an age field (age) of type "float(3,1)". The record with serial number 2 in the serial number field (seq) corresponds to the expanded customer data table (customer_expand_2) with the changed last name field. This table contains a last name field (lastName) of type "varchar(10)", a gender field (gender) of type "varchar(1)", an identifier field (lastName) of type "char(10)", and an age field (age) of type "float(3,1)". In other words, the expanded database records the table names of all tables, each field name, its corresponding field type, and the serial number used to distinguish between old and new tables.
[0091] The data processing system 10 can merge the data tables customer, customer_expand_1, and customer_expand_2 into a new customer data table at the appropriate time. According to the data table merging process 30, the data processing system 10 can specify the data name "customer" in step 302 and query the expanded database data table based on the specified data name. Based on this, the 11 related data (belonging to the three data tables) and the corresponding serial numbers 0, 1, and 2 can be queried as shown in Table 10. The data processing system 10 uses the largest serial number 2 as the third serial number and uses the four data corresponding to serial number 2 (corresponding to the data table name "customer_expand_2") as the third data. Then, the data processing system 10 increments the third serial number (2) to obtain the fourth serial number (3) in step 304, and creates a second data table named "customer_expand_3" in step 306. In step 308, the data processing system 10 imports the data contents of the data tables customer, customer_expand_1, and customer_expand_2 into the second data table in order of serial numbers from small to large. After the data is successfully imported, the data processing system 10 first deletes the data tables customer, customer_expand_1, and customer_expand_2 in step 314, and then changes the data table name of the second data table from "customer_expand_3" to "customer". Finally, the data processing system 10 deletes the data corresponding to the data tables customer and customer_expand_1 in the extended database data table, and modifies the data corresponding to the third serial number (2) as shown in Table 11 below. Among them, the data table name corresponding to the data table customer_expand_2 is changed to "customer", and the serial number (seq) is reset to 0. Accordingly, the data processing system 10 merges the data tables customer, customer_expand_1, and customer_expand_2 into a new customer data table.
[0092] Table 11
[0093] table_name column_name type seq customer lastName varchar(10) 0 customer gender varchar(1) 0 customer id char(10) 0 customer age float(3,1) 0
[0094] Further, please refer to FIG. 4 , which is a schematic diagram of a data processing system 10 according to an embodiment of the present invention. As shown in FIG. 4 , the data processing system 10 may include a processing unit 400, a storage unit 410, and a database 420. The processing unit 400 may be a general-purpose processor, a microprocessor, or an application-specific integrated circuit (ASIC). The storage unit 410 may be any data storage device for storing a program code 412, and for reading and executing the program code 412 by the processing unit 400. For example, the storage unit 410 may be a read-only memory (ROM), a flash memory, a random access memory (RAM), a hard disk, an optical data storage device, or a non-volatile storage unit, but is not limited thereto. The database 420 may be used to implement the database 14 and may be any relational database such as Oracle, MySQL, SQL Server, etc., but is not limited thereto. It should be noted that although FIG. 4 shows the database 420 in the data processing system 10 , the present invention is not limited thereto. As long as the data processing system 10 has the necessary permissions to access the database 420 , the automatic expansion of the data table can be achieved according to the embodiment of the present invention.
[0095] Data processing system 10 represents the necessary components required to implement the embodiments of the present invention. Persons skilled in the art will readily be able to make various modifications and adjustments accordingly, without limitation. For example, data processing process 20 and data table merging process 30 may be compiled into program code 412 and stored in storage unit 410, allowing processing unit 400 to execute the data processing method of the embodiments of the present invention. Furthermore, storage unit 410 is also used to store data required during the execution of the data processing method, without limitation.
[0096] In summary, the present invention provides a data processing method and system that can automatically add fields to a data table and adjust the field types of a data table according to the content of uploaded data, thereby improving the shortcomings of conventional technologies.
[0097] The above description is only a preferred embodiment of the present invention. All equivalent changes and modifications made according to the scope of the patent application of the present invention should fall within the scope of the present invention.
Claims
1. A data processing system, connected to a database, for processing uploaded data, characterized in that: The uploaded data report includes data content and a data name, and the data processing system includes: a processing unit for executing a program code; and a storage unit coupled to the processing unit, for storing the program code to instruct the processing unit to execute a data processing method, wherein the data processing method comprises: Searching an extended database data table according to the data name to obtain a first data according to a first serial number; comparing the field type of the data content with the first data to obtain a comparison result; In response to the comparison result indicating that the field type of the data content matches the first data, obtaining a first data table corresponding to the first data; In response to the comparison result indicating that the field type of the data content does not match the first data, establishing the first data table according to the field type of the data content and a second serial number, and storing second data corresponding to the first data table and the second serial number in the extended database data table; Storing the data content in the first data table; and Merge data tables based on the data names.
2. The data processing system according to claim 1, wherein: The database is a relational database, and the data content of the uploaded data is structured data.
3. The data processing system according to claim 1, wherein: The expandable database data table stores the corresponding relationship among the names of all data tables in the database, each domain name, each field type and the serial number.
4. The data processing system according to claim 1, wherein: The step of querying the extended database data table according to the data name to obtain the first data according to the first serial number is to query the extended database data table for all data whose table name has a prefix that matches the data name, and determine that the data corresponding to a maximum serial number is the first data, and the first serial number is the maximum serial number.
5. The data processing system according to claim 4, wherein: The second serial number is incremented according to the first serial number.
6. The data processing system according to claim 1, wherein: In the step of creating the first data table according to the field type of the data content and the second serial number, a prefix of a data table name of the first data table is the data name, and a suffix of the data table name is the second serial number.
7. The data processing system according to claim 1, wherein: The step of merging the data tables according to the data name and serial number is in response to the data processing system receiving a request for obtaining, updating, or deleting.
8. The data processing system according to claim 1, wherein: The step of merging the data tables according to the data names and serial numbers is performed according to a predetermined time or regularly.
9. The data processing system according to claim 1, wherein: The step of merging data tables according to the data names includes: Searching the data table of the extended database according to the data name to obtain a third data according to a third serial number, wherein the third serial number is a maximum serial number; Obtaining a fourth serial number, wherein the fourth serial number is incremented based on the third serial number; Creating a second data table based on the third data and the fourth serial number; According to the serial numbers from small to large, the data contents of the data tables corresponding to the serial numbers are sequentially imported into the second data table; Deleting the data table corresponding to the serial number less than the fourth serial number; Modify a data table name of the second data table to the data name; and The extended database table is updated.
10. The data processing system according to claim 9, wherein: The step of updating the data table of the expansion database includes: Deleting data whose data table name has a prefix that matches the data name and whose corresponding serial number is less than the third serial number; and Modify the data table name corresponding to the third serial number to the data name, and modify the third serial number to 0.