A method, system, device and medium for generating a database table

Abstract

The present application is applicable to the field of computer technology, and provides a method, system, device and medium for generating a database table, the method comprising: using an entity recognition algorithm to analyze each entity object in a database, using a semantic similarity calculation method to determine the association relationship and relationship type between different entity objects; based on the relationship type, using a relational model design algorithm to generate a basic table structure; judging the attribute type of each entity object, based on the attribute type, using an attribute mapping rule to map the attribute of the entity object to the basic table structure, and generating a database table; using triggers and cascade operation methods to maintain the association relationship between entity objects in the database table and the consistency and integrity of the data; using a time series prediction algorithm to predict the access trend of each entity object within a preset time, and generating a database table optimization plan. The present application can accurately analyze and efficiently reconstruct the original data structure in the database, and generate a new database table that meets the requirements.

Description

Technical Field

[0001] The present application belongs to the field of computer technology, and in particular, relates to a method, system, device and medium for generating a database table. Background Art

[0002] In the field of database technology, with the rise of big data and cloud computing, although traditional database management systems (DBMS) have made remarkable achievements in data storage, query and management, such as relational database management systems (RDBMS) that achieve efficient management and access of data through the SQL language, their performance bottlenecks and scalability issues are becoming increasingly prominent when faced with massive data, high concurrent access and complex business logic.

[0003] In recent years, in order to meet these challenges, the industry has proposed a variety of advanced database technologies and architectures, such as NoSQL databases, distributed databases, and new database systems based on column storage or graph storage. These technologies have significantly improved the performance, scalability, and flexibility of database systems by introducing distributed architectures, optimizing data storage formats, and improving concurrent processing capabilities.

[0004] However, even with the support of the above database technologies, enterprises still face problems such as unreasonable data structure design, low data migration efficiency, and difficulty in adapting business logic when upgrading or migrating databases. Especially when the original database system cannot meet new business needs, how to efficiently redesign the database structure, optimize the data storage method, and ensure the consistency and integrity of the data has become an urgent problem to be solved. Summary of the invention

[0005] The embodiments of the present application provide a method, system, device and medium for generating a database table, which can solve one of the above-mentioned problems in the prior art.

[0006] In a first aspect, an embodiment of the present application provides a method for generating a database table, comprising:

[0007] Analyze each entity object in the database using an entity recognition algorithm, and determine the association relationship and relationship type between different entity objects using a semantic similarity calculation method, wherein the relationship types include one-to-one, one-to-many, and many-to-many;

[0008] Based on the relationship type, a relationship model design algorithm is used to generate a basic table structure, wherein the basic table structure includes a table name, a field name, a data type and a field type, wherein the field type includes a primary key field and a foreign key field;

[0009] Determine the attribute type of each entity object, and based on the attribute type, use attribute mapping rules to map the attributes of the entity object to the basic table structure to generate a database table;

[0010] Using triggers and cascade operation methods to maintain the association relationship between entity objects in the database table and the consistency and integrity of data;

[0011] Use time series prediction algorithms to predict the access trends of each entity object within a preset time and generate database table optimization plans.

[0012] Furthermore, the entity recognition algorithm is used to analyze each entity object in the database, and the semantic similarity calculation method is used to determine the association relationship and relationship type between different entity objects. The relationship types include one-to-one, one-to-many and many-to-many, including:

[0013] Acquire predefined entity objects and attribute information corresponding to the entity objects, use an entity recognition algorithm to analyze the attribute information of each entity object, and form an entity data table, wherein the entity data table includes the entity name and attribute information of each entity object, and the attribute information includes the attribute name and data type;

[0014] Based on the entity data table, a semantic similarity calculation method is used to calculate the similarity of entity names and attribute names between entity objects respectively;

[0015] Calculating the total similarity between the similarity of the entity name and the similarity of the attribute name;

[0016] By comparing the total similarity with a preset similarity threshold, marking two entity objects having an association relationship as entity-associated objects;

[0017] The relationship type of the entity objects is determined by analyzing the uniqueness of the attributes between two entity objects in each group of entity association objects.

[0018] Furthermore, based on the relationship type, a relationship model design algorithm is used to generate a basic table structure, wherein the basic table structure includes a table name, a field name, a data type and a field type, wherein the field type includes a primary key field and a foreign key field, including:

[0019] Normalize each of the entity objects and the attribute information corresponding to the entity objects to obtain a basic relationship model;

[0020] Based on the basic relationship model, generate a basic table structure for each entity object;

[0021] In the basic table structure, primary key fields, foreign key fields and preset index statements are set.

[0022] Furthermore, based on the attribute type, the attribute mapping rule is adopted to map the attribute of the entity object to the basic table structure, including:

[0023] Mapping basic attributes of entity objects to fields of the basic data table structure one by one;

[0024] For complex attributes in entity objects, by determining the information volume and change frequency of the attribute value corresponding to the complex attribute, a mapping method of the complex attribute to the basic table structure is obtained, wherein the mapping method includes a serialization method and a sub-table establishment method;

[0025] For multi-valued attributes in entity objects, a sub-table is created for each multi-valued attribute, and the multi-valued attribute is mapped to the basic table structure through a foreign key;

[0026] For the derived attributes in the entity object, the calculation method of the derived attributes is recorded, and the derived attributes are mapped to the basic table structure.

[0027] Furthermore, the trigger and cascade operation method are used to maintain the association relationship between entity objects in the database table and the consistency and integrity of the data;

[0028] Based on the preset trigger definition syntax, determine the execution time of the trigger, write SQL statements and logical conditions inside the trigger, and synchronously modify the entity objects with associated relationships;

[0029] Acquire entity-related objects in a database, and determine restriction rules for data fields between different entity objects based on association relationships and integrity constraints of the entity-related objects, wherein the entity-related objects are two entity objects with an association relationship;

[0030] When the data in the main table of the database changes, the cascade operation method is used to obtain the changes in the sub-table data;

[0031] Based on the changes, a corresponding cascade operation is called through a trigger;

[0032] By defining transaction boundaries in triggers, all data operations on the database are processed atomically;

[0033] A trigger management mechanism is used to configure and adjust the trigger to dynamically adjust the behavior of the trigger. The configuration adjustment includes modifying the trigger definition, disabling the trigger, and enabling the trigger.

[0034] Furthermore, the time series prediction algorithm is used to predict the access trend of each entity object within a preset time and generate a database table optimization plan, including:

[0035] Collect query logs of each database table through monitoring tools, wherein the query logs include query statements, query time and query users;

[0036] Based on the query log, calculate the access frequency and query complexity of each of the database tables to obtain access pattern data of the database tables;

[0037] Using a time series prediction algorithm to analyze the access pattern data to obtain access trends of each database table within a preset time;

[0038] Based on the access trend, obtaining preliminary suggestions for optimizing the database table, the preliminary suggestions including an index optimization plan, a column storage adjustment plan, and a partition adjustment plan;

[0039] Each suggestion plan in the preliminary suggestion is rated for change risk to obtain a risk level for each suggestion plan, and a database table optimization plan is determined based on the risk level.

[0040] Furthermore, the method for generating the database table also includes:

[0041] Constructing a data lineage analysis model, and using the data lineage analysis model to track the flow path of data between different database tables and fields;

[0042] By analyzing SQL statements and data operation logic, a mapping relationship between data and business processes is established, and a data flow diagram is drawn based on the mapping relationship;

[0043] According to the data flow diagram, the dependency and influence relationship between different data are displayed, and the correlation between the data is determined.

[0044] In a second aspect, an embodiment of the present application provides a system for generating a database table, including:

[0045] The first processing module is used to analyze each entity object in the database using an entity recognition algorithm, and to determine the association relationship and relationship type between different entity objects using a semantic similarity calculation method, wherein the relationship types include one-to-one, one-to-many, and many-to-many;

[0046] The second processing module is used to generate a basic table structure based on the relationship type by using a relationship model design algorithm, wherein the basic table structure includes a table name, a field name, a data type and a field type, and the field type includes a primary key field and a foreign key field;

[0047] The third processing module is used to determine the attribute type of each entity object, and based on the attribute type, use the attribute mapping rule to map the attribute of the entity object to the basic table structure to generate a database table;

[0048] The fourth processing module is used to maintain the association relationship between entity objects in the database table and the consistency and integrity of data by using triggers and cascade operation methods;

[0049] The fifth processing module is used to use a time series prediction algorithm to predict the access trend of each entity object within a preset time and generate a database table optimization plan.

[0050] In a third aspect, an embodiment of the present application provides a terminal device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the above-mentioned method for generating a database table when executing the computer program.

[0051] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, including a computer-readable storage medium storing a computer program, and when the computer program is executed by a processor, the method for generating the database table described above is implemented.

[0052] Compared with the prior art, the embodiments of the present invention have the following beneficial effects:

[0053] This application proposes a method for generating a database table, which is used to redesign and optimize the original data structure in the database. Based on the existing database technology, this method introduces an entity recognition algorithm to achieve accurate analysis and efficient reconstruction of the original data structure in the database. This method can not only generate new database tables that meet the requirements according to business needs and data characteristics, but also ensure the consistency and integrity of the data migration process, thereby providing a more efficient and reliable solution for the upgrade and migration of enterprise database systems. BRIEF DESCRIPTION OF THE DRAWINGS

[0054] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0055] Figure 1 It is a flowchart of a method for generating a database table provided by an embodiment of the present invention;

[0056] Figure 2 It is a structural schematic diagram of a database table generation system provided by an embodiment of the present invention;

[0057] Figure 3 It is a schematic diagram of the structure of the terminal device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0058] In the following description, specific details such as specific system structures, technologies, etc. are provided for the purpose of illustration rather than limitation, so as to provide a thorough understanding of the embodiments of the present application. However, it should be clear to those skilled in the art that the present application may also be implemented in other embodiments without these specific details. In other cases, detailed descriptions of well-known systems, devices, circuits, and methods are omitted to prevent unnecessary details from obstructing the description of the present application.

[0059] It should be understood that when used in the present specification and the appended claims, the term "comprising" indicates the presence of described features, wholes, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, wholes, steps, operations, elements, components and / or combinations thereof.

[0060] It should also be understood that the term “and / or” used in the specification and appended claims refers to any and all possible combinations of one or more of the associated listed items, and includes these combinations.

[0061] As used in the specification and appended claims of this application, the term "if" can be interpreted as "when" or "uponce" or "in response to determining" or "in response to detecting", depending on the context. Similarly, the phrase "if it is determined" or "if [described condition or event] is detected" can be interpreted as meaning "uponce it is determined" or "in response to determining" or "uponce [described condition or event] is detected" or "in response to detecting [described condition or event]", depending on the context.

[0062] In addition, in the description of the present application specification and the appended claims, the terms "first", "second", "third", etc. are only used to distinguish the descriptions and cannot be understood as indicating or implying relative importance.

[0063] References to "one embodiment" or "some embodiments" etc. described in the specification of this application mean that one or more embodiments of the present application include specific features, structures or characteristics described in conjunction with the embodiment. Therefore, the statements "in one embodiment", "in some embodiments", "in some other embodiments", "in some other embodiments", etc. that appear in different places in this specification do not necessarily refer to the same embodiment, but mean "one or more but not all embodiments", unless otherwise specifically emphasized in other ways. The terms "including", "comprising", "having" and their variations all mean "including but not limited to", unless otherwise specifically emphasized in other ways.

[0064] See also Figure 1As shown, the present invention is a method for generating a database table, comprising the following steps:

[0065] S100, using an entity recognition algorithm to analyze each entity object in the database, and using a semantic similarity calculation method to determine the association relationship and relationship type between different entity objects, wherein the relationship type includes one-to-one, one-to-many, and many-to-many;

[0066] A database table generation method of the present application can be used to redesign and optimize the original data structure in the database, that is, to generate new database tables that meet the requirements according to business needs and data characteristics, to ensure that they can efficiently store and quickly retrieve data, and thus effectively respond to the challenges of the original database posed by the growing amount of data and increasingly complex business logic, thereby ensuring the stability, scalability and flexibility of the database system, and laying a solid foundation for the digital transformation and business development of the enterprise.

[0067] Specifically, an entity recognition algorithm is used to identify each entity object in the database, and different entity objects are analyzed at the same time. The data in the original database is migrated to generate new database tables and ER diagrams to meet the impact of the growing amount of data and complex business logic on the original database.

[0068] In some embodiments, the above step S100 includes:

[0069] Acquire predefined entity objects and attribute information corresponding to the entity objects, use an entity recognition algorithm to analyze the attribute information of each entity object, and form an entity data table, wherein the entity data table includes the entity name and attribute information of each entity object, and the attribute information includes the attribute name and data type;

[0070] Based on the entity data table, a semantic similarity calculation method is used to calculate the similarity of entity names and attribute names between entity objects respectively;

[0071] Calculating the total similarity between the similarity of the entity name and the similarity of the attribute name;

[0072] By comparing the total similarity with a preset similarity threshold, marking two entity objects having an association relationship as entity-associated objects;

[0073] The relationship type of the entity objects is determined by analyzing the uniqueness of the attributes between two entity objects in each group of entity association objects.

[0074] In this embodiment, a database query statement is used to obtain the entity objects originally defined in the database and the attribute information corresponding to the entity objects. Specifically, in a SQL Server database, by using the database query statement, SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS, all entity objects in the database and the corresponding attribute information in the entity objects are obtained, that is, all data tables in the database and the columns corresponding to the data tables are obtained.

[0075] In this embodiment, an entity recognition algorithm is used to analyze the attributes corresponding to each entity to obtain the corresponding attribute name and data type, and a data filling method is used to fill the attribute information of the entity object into the entity data table to obtain a complete entity data table. Based on the entity data table, all entity objects and attribute information in the original database can be obtained, which provides a data basis for the reconstruction and optimization of the database and ensures the accuracy of data processing.

[0076] In this embodiment, natural language processing technology is used to perform text preprocessing on the entity names of multiple entity objects, including operations such as word segmentation and stop word removal, to obtain multiple standardized entity name texts. By calculating the semantic similarity index between the entity name texts, the similarity matrix of the entity names between the entity objects is obtained. Similarly, the similarity matrix of the attribute names in the attribute information is obtained, and the total similarity between two different entity objects is calculated using the product method, where n represents the number of similarity calculations, and ∈ is a positive number used to smooth the total similarity.

[0077] In this embodiment, a similarity threshold is preset, and by comparing the similarity threshold and the total similarity, it is determined that there is an association relationship between two entity objects, and the two entity objects with an association relationship are marked as a group of entity association objects to facilitate the subsequent establishment of a database table.

[0078] In this embodiment, the number and uniqueness of each attribute of the two entity objects in the entity association object are obtained. Uniqueness refers to whether a certain attribute value appears only once in the corresponding entity, that is, whether the attribute can uniquely identify an entity instance. Specifically, if there are one or more attributes in each of the two associated entities, and the combination of these attributes can uniquely identify an instance in the other entity, then the relationship between the two entities is one-to-one. For example, an employee entity (with a unique employee ID) corresponds to an employee file entity (also uniquely identified by the employee ID), then the relationship between the employee and the employee file is one-to-one. If in one entity, a combination of one or some attributes can uniquely identify an instance in another entity, but in the other entity, the same attribute value can correspond to multiple instances, then the relationship between the two entities is one-to-many. For example, a department entity (with a unique department ID) can contain multiple employee entities (the employee ID is not unique within the department), then the relationship between the department and the employees is one-to-many. If neither entity has an attribute (or combination of attributes) that uniquely identifies the other entity, that is, an instance in one entity can be associated with multiple instances in the other entity, and vice versa, then the relationship between the two entities is many-to-many. For example, a student can take multiple courses, and a course can be taken by multiple students, so the relationship between students and courses is many-to-many.

[0079] In some embodiments, after the above step S100, the method further includes: for two entity objects having an association relationship, using an entity layout algorithm to determine the initial position of each entity object in the ER diagram, analyzing the relationship type of the two entity objects, using a relationship line to represent the relationship type in the ER diagram, and the relationship type includes one-to-one, one-to-many and many-to-many;

[0080] In this embodiment, based on the entity association object, an entity layout algorithm is used to generate an ER diagram of the entity data table. Specifically, according to the size of the total similarity, the degree of association between each entity object is determined, and according to the degree of association between each entity object, the initial position of each entity object in the ER diagram is determined by using the entity layout algorithm. By analyzing the attributes of the entity association object, the association relationship between the entity objects is obtained, including one-to-one, one-to-many, many-to-many and other association types, and an entity association information list is obtained. According to the entity association information list, a connection method is used to draw the association lines between the entity objects in the ER diagram. The connection style is distinguished according to the association type, such as one-to-one is represented by a solid line, one-to-many is represented by a solid line with an arrow, and many-to-many is represented by a diamond plus a bidirectional arrow line. The attribute information of each entity is obtained, and by parsing the entity association object, a list of attributes of each entity object is obtained, including information such as attribute name, data type, whether it is a primary key, etc. According to the attribute list, an attribute layout algorithm is used to reasonably layout the location of the attributes in each entity box in the ER diagram. The attribute name and data type are displayed as labels, and the primary key attribute is marked with an underscore, thereby obtaining the ER diagram of the database. The ER diagram intuitively displays the entities, attributes and relationships of the database, providing an important basis for subsequent database design and development.

[0081] S200, based on the relationship type, using a relationship model design algorithm to generate a basic table structure, the basic table structure includes a table name, a field name, a data type and a field type, the field type includes a primary key field and a foreign key field;

[0082] In this embodiment, the database table is generated on the basis of the basic table structure, that is, the basic table structure is used as a template for each database table, which ensures the systematic and standardized design of the database, has high versatility and adaptability, and at the same time, by adopting a relational model design algorithm based on the relationship type, it can automatically and accurately generate a table structure that conforms to the business logic and data characteristics, avoiding arbitrariness and inconsistency in the design process. Secondly, the generated basic table structure contains detailed information, such as table name, field name, data type and field type (including primary key and foreign key). By clearly distinguishing between primary key fields and foreign key fields, the generated basic table structure further strengthens the integrity and consistency constraints of the data. The primary key ensures the uniqueness of each record in the table, while the foreign key maintains the logical relationship and business rules between the data through cross-table associations, providing strong support for complex data operations, making the operation and management of the database more convenient and reliable.

[0083] In some embodiments, the step S200 includes:

[0084] Normalize each of the entity objects and the attribute information corresponding to the entity objects to obtain a basic relationship model;

[0085] Based on the basic relationship model, generate a basic table structure for each entity object;

[0086] In the basic table structure, primary key fields, foreign key fields and preset index statements are set.

[0087] In this embodiment, each group of entity-related objects is normalized to eliminate data redundancy and anomalies, and obtain a relational model that conforms to the third normal form; a pattern merging algorithm is used to merge all entity-related objects to obtain a basic relational model.

[0088] In this embodiment, according to the entity objects in the basic relationship model, the name and attribute information of each entity object are obtained, and the table structure design scheme of the entity object in the database is determined by analyzing the association relationship between the entity objects. Specifically, the database table structure design principle is adopted to map each entity object to a database table, the table name is the entity name, the attribute information of the entity object is obtained, each attribute is mapped to a column in the table, the attribute name is used as the column name, and the attribute data type is used as the data type of the column. By analyzing the characteristics of the attribute such as length, precision, etc., the complete definition of the column is determined, and then the basic table structure of each entity object is obtained. In addition, according to the association relationship between the entity objects, the association between the tables is expressed by the primary key and foreign key constraints. For a one-to-many relationship, the primary key of the "one" party is added to the "many" party as a foreign key; for a many-to-many relationship, an association table is created to store the association relationship between the two entity tables. The business rules and constraints of the entity object are obtained, and according to these rules and constraints, corresponding constraints are added to the table structure, such as non-empty constraints, unique constraints, check constraints, etc., to ensure the integrity and consistency of the data. By querying, counting and analyzing the entity objects, preset index statements in the generated basic table structure to improve the efficiency of data query and retrieval and optimize the access performance of the table. Based on the above steps, a complete table structure definition statement is generated for each entity object, including table name, column definition, primary key constraint, foreign key constraint, other constraints and index, etc., to form an executable SQL statement.

[0089] S300, determining the attribute type of each entity object, and based on the attribute type, using attribute mapping rules to map the attributes of the entity object to the basic table structure, to generate a database table;

[0090] In some embodiments, mapping the attributes of the entity object to the basic table structure using attribute mapping rules based on the attribute type includes:

[0091] Mapping basic attributes of entity objects to fields of the basic data table structure one by one;

[0092] For complex attributes in entity objects, by determining the information volume and change frequency of the attribute value corresponding to the complex attribute, a mapping method of the complex attribute to the basic table structure is obtained, wherein the mapping method includes a serialization method and a sub-table establishment method;

[0093] For multi-valued attributes in entity objects, a sub-table is created for each multi-valued attribute, and the multi-valued attribute is mapped to the basic table structure through a foreign key;

[0094] For the derived attributes in the entity object, the calculation method of the derived attributes is recorded, and the derived attributes are mapped to the basic table structure.

[0095] In a database, the attribute types of entity objects usually include basic attributes, complex attributes, multi-valued attributes and derived attributes. Different attribute types have different mapping methods. Specifically, for basic attributes, a one-to-one mapping method is adopted. For complex attributes, serialization method and sub-table creation method are adopted. For derived attributes, since the derived attributes are calculated through other attributes, the calculation method of the derived attributes is mapped to the basic table structure without explicit storage. The above mapping methods are combined to generate multiple database tables with association relationships.

[0096] Specifically, basic attributes are the most basic data elements directly defined and stored in the data table, and they are the basis of the data table structure; complex attributes refer to attributes that contain multiple sub-attributes; multi-valued attributes refer to attributes that can have multiple values; and derived attributes are attributes calculated from other attributes. For example, an entity object "employee" includes basic attributes, complex attributes, multi-valued attributes, and derived attributes. Basic attributes include "employee number" (emp_id), with a data type of VARCHAR(10); "name" (name), with a data type of VARCHAR(50); "date of birth" (birth_date), with a data type of DATE; "position" (position), with a data type of VARCHAR(30); complex attributes include contact address, which includes sub-attributes such as "street", "city", and "zip code"; multi-valued attributes include skill attributes, where an employee can have multiple skills; and derived attributes include age, which is calculated from the "date of birth" in the basic attributes.

[0097] In this embodiment, according to the basic attributes of the entity object, a one-to-one mapping method is adopted to obtain each attribute value of the entity object, and the field value of the mapped basic data table structure is obtained by assigning the attribute value to the field corresponding to the basic data table structure. According to the field value of the mapped basic data table structure, a database operation statement is used to obtain a database connection, and the field value is inserted into the corresponding basic table structure by executing an INSERT statement to obtain a basic table storing the basic attributes of the entity object. A database query statement is used to obtain the primary key field value of the basic table, and the corresponding relationship between the entity object and the basic table is determined by assigning the primary key field value to the primary key attribute of the entity object. According to the corresponding relationship recorded between the entity object and the basic table, a database transaction control mechanism is used to obtain a database transaction, and by opening a transaction and executing a commit operation, it is determined that the data in the basic table structure is synchronized with the attribute value of the entity object.

[0098] In this embodiment, the mapping method for complex attributes is determined by the amount of information and the change frequency of the attribute value in the complex attribute. Specifically, for the data change frequency, a data access frequency analysis method is used to obtain the data change frequency of each complex attribute, and a change frequency list of the complex attribute is obtained. By comparing the threshold value of the change frequency list, it is determined whether the change frequency of each complex attribute exceeds the preset high-frequency threshold, and a complex attribute list with high-frequency changes and a complex attribute list with low-frequency changes are obtained. According to the complex attribute list with high-frequency changes, a sub-table establishment method is adopted to map these complex attributes to a separate complex attribute sub-table, and a one-to-many association relationship between the basic table and the complex attribute sub-table is established, and the mapping method of the high-frequency complex attributes is determined to be a sub-table establishment method. According to the complex attribute list with low-frequency changes, a serialization method is adopted to serialize these complex attributes into strings or binary data, and store them in the serialization field of the entity table, and the mapping method of the low-frequency complex attributes is determined to be a serialization method. Similarly, according to the amount of information in the attribute value of the complex attribute, the amount of information of each complex attribute is obtained, and a list of the amount of information of the complex attribute is obtained. By comparing the threshold value of the information list, the mapping method of the complex attribute list is determined. For complex attribute lists with high information content, a sub-table establishment method is adopted, and for complex attribute lists with low information content, a serialization method is adopted. It can be understood that when a complex attribute belongs to one of the complex attribute lists with high frequency changes or high information content, the sub-table establishment method is adopted, which can provide better query performance, and through the connection (JOIN) operation, related data can be flexibly queried and summarized.

[0099] In this embodiment, through the persistence operation of the entity object, the entity data is stored in the corresponding basic table structure according to the mapping methods of different complex attributes. For the sub-table creation method, the complex attribute data is stored in the associated sub-table; for the serialization method, the complex attributes are serialized and stored in the serialization field of the entity table. When querying data, the corresponding data is obtained from the basic table structure according to the complex attribute mapping method of the entity object. For the sub-table creation method, the complex attribute data is obtained from the sub-table through an associated query; for the serialization method, the complex attribute data is deserialized from the serialization field of the entity table. Through the above steps, it is realized that according to the frequency of change of the complex attributes, the sub-table creation method or the serialization method is adaptively selected for attribute mapping, and the corresponding data storage and acquisition are performed according to the mapping method during the persistence and query process, thereby optimizing the data storage and access efficiency of the entity object.

[0100] In this embodiment, since a multi-valued attribute has multiple attribute values ​​and the amount of data information is large, a sub-table establishment method is adopted to create a sub-table for each multi-valued attribute, and the multi-valued attribute is mapped to the basic table structure through a foreign key to obtain a multi-valued attribute sub-table of the entity object, wherein the multi-valued attribute sub-table contains the primary key and foreign key of the multi-valued attribute, and the foreign key is associated with the primary key of the basic table, and the table structure definition of the multi-valued attribute sub-table is obtained. Through the database management system, according to the table structure definition of the multi-valued attribute sub-table, a multi-valued attribute sub-table is created in the database, and a foreign key constraint is set to associate the multi-valued attribute sub-table with the basic table to ensure the integrity and consistency of the data. The data of the entity object is obtained, and according to the attribute value of the multi-valued attribute, the data of the multi-valued attribute is inserted into the corresponding multi-valued attribute sub-table, and the foreign key is associated with the primary key of the basic table to establish a mapping relationship between the entity object data and the multi-valued attribute data, and a complete entity object data storage structure is obtained. Use data query and processing tools to perform joint query, statistical analysis and other operations on basic tables and multi-valued attribute sub-tables according to business needs. Through multi-table association and aggregation functions, obtain the required entity object information to provide data support for business applications.

[0101] In this embodiment, the basic attributes and business rules of the entity object are obtained according to the business needs analysis. Based on the basic attributes and business rules, the derived attributes in the entity object are determined. For example, the annual income of an employee is calculated based on the monthly salary and bonus, and the age of the employee is calculated based on the date of birth. A mathematical formula or a logical expression is used to obtain the calculation method of the derived attribute. For example, the calculation method of the annual income may be the monthly salary multiplied by twelve plus the year-end bonus. Through this calculation method, the specific calculation formula of the derived attribute is obtained. Through the calculation formula of the derived attribute, the attribute value of the required derived attribute is obtained. For example, in order to calculate the annual income of an employee, the monthly salary and year-end bonus data of the employee need to be obtained. The derived attribute is mapped to the basic table structure, and the calculation method of the attribute value of the derived attribute is recorded at the same time, which is used for the subsequent storage and update of the database table data.

[0102] S400, using triggers and cascade operation methods to maintain the association relationship between entity objects in the database table and the consistency and integrity of the data;

[0103] In this embodiment, triggers and cascade operation methods are used to maintain complex associations between entity objects and ensure data consistency and integrity. When a specific event (such as insert, update or delete operation) occurs in the database, the preset database logic execution is triggered immediately, thereby avoiding the tediousness and error-proneness of manually maintaining related data. Triggers can ensure that whenever the data in the related table changes, the associated table can also be updated accordingly, maintaining the logical consistency between the data. At the same time, cascade operations further strengthen this consistency maintenance. It defines how the impact of operations (such as deletion) on one table is automatically propagated to other tables, ensuring data integrity and compliance with business rules.

[0104] In some embodiments, the above step S400 includes:

[0105] Based on the preset trigger definition syntax, determine the execution time of the trigger, write SQL statements and logical conditions inside the trigger, and synchronously modify the entity objects with associated relationships;

[0106] Acquire entity-related objects in a database, and determine restriction rules for data fields between different entity objects based on association relationships and integrity constraints of the entity-related objects, wherein the entity-related objects are two entity objects with an association relationship;

[0107] When the data in the main table of the database changes, the cascade operation method is used to obtain the changes in the sub-table data;

[0108] Based on the changes, a corresponding cascade operation is called through a trigger;

[0109] By defining transaction boundaries in triggers, all data operations on the database are processed atomically;

[0110] A trigger management mechanism is used to configure and adjust the trigger to dynamically adjust the behavior of the trigger. The configuration adjustment includes modifying the trigger definition, disabling the trigger, and enabling the trigger.

[0111] In this embodiment, a preset trigger definition syntax is used to create a trigger in the database, specify the name of the trigger, the execution time and the associated table. Inside the trigger, an SQL statement is written according to the business logic to synchronize the modification of the associated entity objects. For example, when the data in the employee table is updated, the employee ID is obtained, and then the corresponding data in the order details table is updated according to the employee ID. In the SQL statement, the data range and specific operations that need to be synchronized are determined through conditional judgment and logical operation. For example, it is determined whether the employee status is "resigned". If so, the corresponding employee in the employee table is deleted. The relevant data when the trigger is executed, such as the inserted, updated or deleted records, is obtained and passed to the variables in the SQL statement for accurate synchronization modification of the associated entity objects. Inside the trigger, the atomicity and consistency of the synchronization modification operation are ensured through transaction control and error handling mechanisms. If an exception occurs during the modification process, the transaction is rolled back to maintain the integrity of the data. After the above steps, a complete trigger is obtained, which can be automatically triggered at the specified execution time, and synchronize the associated entity objects according to the predefined SQL statements and logical conditions to maintain the consistency and integrity of the data.

[0112] In this embodiment, the ER diagram between entity-related objects is obtained. Through the ER diagram, in combination with the integrity constraints defined in the database, including entity integrity, referential integrity, and user-defined integrity, the data dependencies and constraints between different entity objects are determined, and the key fields and foreign key fields of the entity objects are determined. According to the key fields and foreign key fields of the entity objects, a data dependency analysis method is used to obtain the data dependencies between different entity objects, including functional dependencies, multi-value dependencies, and connection dependencies, and obtain the dependency graph between the data fields of the entity objects. Through the data field dependency graph, combined with the paradigm theory in the relational data theory, it is determined whether the entity object satisfies the first paradigm, the second paradigm, the third paradigm, and a higher-level paradigm, and whether the data field of the entity object is redundant and abnormal. According to the paradigm level of the entity object, a data normalization method is used to normalize the entity objects that do not meet the high-level paradigm, including field splitting, field merging, field adjustment, etc., to obtain the entity objects that meet the high-level paradigm, and eliminate data redundancy and insertion, deletion, and update anomalies. Through the normalized entity objects, combined with business rules and data integrity requirements, data validation methods are used to verify the value range, format, precision, etc. of the data fields of the entity objects to determine whether the data is legal and valid, and to determine the specific restriction rules for the entity object data fields. Based on the determined entity object data field restriction rules, metadata management methods are used to store the restriction rules in the form of metadata in the data dictionary or configuration file for application programs to verify and control when performing data operations, and obtain entity object data that meets data integrity requirements.

[0113] In this embodiment, a database trigger mechanism is used to obtain the primary key ID of the main table that has changed. The name of the subtable and the foreign key field associated with the main table record are obtained through the primary key ID of the main table record, and the subtable information that needs to be checked is obtained. A recursive query algorithm is used to obtain all associated subtable records based on the primary key ID of the main table and the foreign key field of the subtable. By comparing the current value of the subtable record with the value before the change, it is determined whether the subtable data has changed and the affected subtable records are determined. According to the change of the subtable data, the corresponding business logic is used to obtain the subsequent operations that need to be performed, such as updating the associated data, recording the change log, etc.

[0114] In this embodiment, by recursively traversing all associated subtables, the data changes of subtables at all levels are obtained to form a complete cascade operation result. The results of the cascade operation are saved in the corresponding data table or log to determine the impact of the data change on the business, providing a basis for subsequent business decisions. According to business needs, a transaction mechanism is adopted to ensure the atomicity and consistency of cascade operations and avoid data inconsistency problems. By optimizing database queries and algorithm implementations, efficient cascade operation performance is obtained to meet the real-time requirements of the business. 1 Monitor and count the results of the cascade operation, obtain the execution status and abnormal information of the operation, and provide data support for system optimization and problem location.

[0115] In this embodiment, the properties and behaviors of the trigger are defined in the form of a configuration file, including trigger conditions, execution operations, etc., to obtain the initial trigger configuration. Specifically, by reading the trigger configuration file, the definition information of the trigger is obtained, including the name, type, trigger condition, execution operation, etc. of the trigger, a trigger object is constructed, and the initial state of the trigger is determined. According to the changes in business requirements, the configuration adjustment mechanism is adopted to adjust the configuration of the trigger. Specifically, according to business requirements, the configuration adjustment of the trigger includes disabling the trigger, enabling the trigger, and modifying the trigger definition. Modifying the trigger definition includes changing the trigger condition, executing the operation, etc., to obtain the updated trigger definition. By writing the updated trigger definition into the configuration file, the persistent storage of the trigger definition is realized, and the configuration modification of the trigger is ensured to take effect. For disabling the trigger, the specified trigger is set to the disabled state, the execution of the trigger is stopped, and the disabled trigger state is obtained. By updating the state attribute of the trigger object, the trigger is marked as disabled, ensuring that the trigger will not be triggered for execution. For enabling the trigger, the specified trigger is set to the enabled state, the execution of the trigger is resumed, and the enabled trigger state is obtained. By updating the state attribute of the trigger object, the trigger is marked as enabled, ensuring that the trigger can be triggered and executed normally. In the trigger management mechanism, by listening to the change events of the configuration file, the real-time update of the trigger configuration is obtained to determine whether the trigger needs to be reloaded. If a change in the trigger configuration is detected, the properties and behaviors of the trigger object are dynamically adjusted according to the updated configuration information to ensure that the execution of the trigger meets the latest configuration requirements.

[0116] S500: Use a time series prediction algorithm to predict the access trend of each entity object within a preset time and generate a database table optimization plan.

[0117] In this embodiment, the application of the time series prediction algorithm improves the intelligent level of database management. Based on the prediction results of historical data and real-time trends, the database table optimization plan is automatically generated, which reduces the need for manual intervention, reduces management costs, and enhances the scientificity and accuracy of decision-making. In addition, this approach also helps to improve user experience. By optimizing database performance, the waiting time for users to access data is reduced, the response speed and stability of data processing are improved, thereby enhancing user satisfaction and loyalty.

[0118] In some embodiments, the step S500 includes:

[0119] Collect query logs of each database table through monitoring tools, wherein the query logs include query statements, query time and query users;

[0120] Based on the query log, calculate the access frequency and query complexity of each of the database tables to obtain access pattern data of the database tables;

[0121] Using a time series prediction algorithm to analyze the access pattern data to obtain access trends of each database table within a preset time;

[0122] Based on the access trend, obtaining preliminary suggestions for optimizing the database table, the preliminary suggestions including an index optimization plan, a column storage adjustment plan, and a partition adjustment plan;

[0123] Each suggestion plan in the preliminary suggestion is rated for change risk to obtain a risk level for each suggestion plan, and a database table optimization plan is determined based on the risk level.

[0124] In this embodiment, according to the query log, the number of queries for each database table in the historical database is counted to obtain the access frequency data of the historical database table. Specifically, the complexity evaluation algorithm of the query statement is adopted to analyze the complexity of each query statement and obtain the query complexity data of the database table. The query statement complexity evaluation needs to consider factors such as the number of tables involved in the query, the number of query conditions, the aggregate functions and subqueries in the query, and reflect the differences in different business queries. By comprehensively analyzing the access frequency data and query complexity data of the historical database table, the access pattern data of each historical database table is obtained. The access pattern data reflects the usage and query characteristics of the historical database table in different business scenarios.

[0125] In this embodiment, by performing feature engineering on the access pattern data, the key features reflecting the access pattern such as the access frequency, the access time distribution, the access user distribution, etc. are extracted to obtain the feature vector for time series prediction. The time series decomposition technology is adopted to decompose the access pattern data into trend items, seasonal items and random items. By analyzing the changing law of the trend items, the overall trend of the access volume of each historical database table is judged. According to the trend items, seasonal items and random items obtained by decomposition, the access volume of each historical database table is modeled by the autoregressive moving average model (ARMA), and the time series model that can characterize the changing law of the access volume is obtained by model training. The access volume of each regenerated database table is predicted by the exponential smoothing method. By setting different smoothing coefficients, the short-term, medium-term and long-term access volume prediction values ​​are obtained to judge the access trend of each database table on different time scales. According to the prediction results obtained by the ARMA model and the exponential smoothing method, the weighted average method is adopted to perform combined prediction, and by optimizing the weight parameters, more accurate and robust access volume prediction values ​​of each database table are obtained. Compare the predicted access volume of each database table with the actual access volume, use mean square error, mean absolute percentage error and other evaluation indicators to evaluate the accuracy of the prediction model, and continuously adjust the model parameters to obtain a time series model that can accurately predict future access trends. Use visualization technology to plot the historical access volume data and future prediction values ​​of each database table in the same trend chart, intuitively display the access trends of each database table, and provide decision support for database management and optimization.

[0126] In this embodiment, according to the access trend, the statistical information such as the access frequency and access mode of each column in the database table is obtained, and the statistical information is analyzed by the data analysis algorithm to obtain the access heat ranking and data distribution characteristics of each database table. Through the access heat ranking results, the columns that need to create indexes are determined, and for columns with high access frequency and strong selectivity, suggestions for creating indexes are obtained; at the same time, according to the data distribution characteristics, suggestions for creating indexes are also obtained for columns with uneven value distribution and high discrimination, and an index optimization plan is formed. The data sampling and clustering algorithms are used to sample and cluster the data of the database table. According to the clustering results, the access frequency and access mode of different data are judged to obtain the hot and cold area distribution of the data. According to the hot and cold area distribution of the data, for cold data with low access frequency and few updates, suggestions for using column storage are obtained; for hot data with high access frequency and many updates, suggestions for using row storage are obtained, and a column storage adjustment plan is formed. By analyzing the data volume, growth rate and access mode of the database table, a data growth prediction model is used to predict the data growth trend in a certain period of time in the future, and it is determined whether the database table needs to be partitioned. If partitioning is required, determine the appropriate partition key based on the time characteristics and business characteristics of the data, and obtain suggestions for horizontal or vertical partitioning; at the same time, plan the number and size of partitions based on the data growth forecast results to form a partition adjustment plan. Combine the index optimization plan, column storage adjustment plan, and partition adjustment plan to obtain preliminary suggestions for optimizing the database table, which are used to guide the optimization and adjustment of the database table and improve the query performance and storage efficiency of the database.

[0127] In this embodiment, a risk assessment matrix is ​​used to obtain the risk factors of each proposed solution in terms of database table structure, data integrity, query performance, etc., and the risk score of each proposed solution is obtained by weight calculation of the risk factors. According to the risk score, the risk level classification standard is used to obtain the risk level of each proposed solution. The risk level is divided into three levels: high risk, medium risk and low risk. The change risk of each proposed solution is judged by the level of risk level. According to the risk level of each proposed solution, the solution screening rule is used to obtain the proposed solution with a low risk level. Through the comprehensive evaluation of the solution, combined with the business needs and optimization goals of the database table, the final database table optimization solution is determined. According to the determined database table optimization solution, specific optimization measures such as database table structure adjustment, index optimization, and SQL statement optimization are adopted to obtain the optimized database table structure and query statement. The effectiveness and feasibility of the optimization solution are judged by performance testing and comparison before and after optimization. According to the implementation results of the optimization solution, the database monitoring and evaluation mechanism is adopted to obtain the performance indicators and business operation status of the optimized database table. Through tracking and analysis of performance indicators, the database table is continuously improved and optimized to ensure that the optimization solution of the database table can meet the changes in business development and user needs.

[0128] In some embodiments, the method for generating a database table further includes:

[0129] Constructing a data lineage analysis model, and using the data lineage analysis model to track the flow path of data between different database tables and fields;

[0130] By analyzing SQL statements and data operation logic, a mapping relationship between data and business processes is established, and a data flow diagram is drawn based on the mapping relationship;

[0131] According to the data flow diagram, the dependency and influence relationship between different data are displayed, and the correlation between the data is determined.

[0132] In this embodiment, the source table and source field information of the data are obtained to generate an initial data set. By analyzing the SQL statement, the conversion logic such as filtering, cleaning, and aggregation of the data in the flow process is identified to obtain the data conversion rules. Through the data conversion rules, the flow path and direction of the data between different storage locations are determined to obtain the data flow direction. According to the data flow direction, combined with the mapping relationship between the data and the business process, the storage location and processing logic of the key business data are identified to obtain the data processing logic. If the data processing logic contains sensitive data, the corresponding authorization access mechanism is defined to ensure the data security attributes and determine the data security policy. The data generation and update cycle is analyzed through the data processing logic to determine the data frequency and obtain the data update cycle information. According to the data update cycle information, the quality indicators such as the accuracy, completeness, and consistency of the data are evaluated to determine the data quality assessment results. If the data quality assessment results meet the business needs, a data flow diagram is drawn to intuitively display the life cycle of the data and obtain a data flow diagram. According to the data flow diagram, the dependency and influence relationship between different data are revealed, and the data association is determined to facilitate overall management.

[0133] Specifically, the source table and source field information of the data are obtained through the database query tool, such as extracting fields such as "EMPLOYEE_ID" and "FIRST_NAME" from the "HR.EMPLOYEES" table in the database. Next, by analyzing SQL statements, the conversion logic of the data in the flow process is identified. For example, in the SQL statement, it is found that the data in the "EMPLOYEES" table is filtered (such as the WHERE clause filters out employees with department ID 10), cleansed (such as using the TRIM function to remove spaces) and aggregated (such as using the SUM function to calculate the total salary), thereby obtaining data conversion rules. By parsing these SQL statements, clear data conversion rules can be generated. For example, when data flows from the "EMPLOYEES" table to the temporary table "T_EMPLOYEES_CLEAN", the above filtering, cleaning and aggregation operations are applied. Based on these transformation rules, the flow path and direction of data between different storage locations can be determined, such as from the source table "EMPLOYEES" to the temporary table "T_EMPLOYEES_CLEAN" and then to the aggregate table "AGG_EMPLOYEES". Combined with the mapping relationship between data items and business processes, the storage location and processing logic of key business data can be identified. For example, if the business process needs to analyze the total salary of each department, the key business data is stored in the aggregate table "AGG_EMPLOYEES" and processed through specific aggregation logic (such as SUM function). If the processing logic contains sensitive data (such as employee salary), the corresponding authorization access mechanism needs to be defined, such as only allowing users with specific roles to access this data, so as to ensure data security attributes and determine data security policies. By analyzing the data processing logic, the generation and update cycle of data can be inferred. For example, if the salary data is updated once a month, the data update cycle is one month. Based on this update cycle information, quality indicators such as data accuracy, completeness and consistency can be evaluated. For example, by checking the timeliness and completeness of the data, the quality assessment results of the data can be determined. If the data quality assessment results meet the business needs, a data flow diagram can be drawn to intuitively display the life cycle of the data. For example, the flow process from the "EMPLOYEES" table to the "T_EMPLOYEES_CLEAN" table, and then to the "AGG_EMPLOYEES" table. Based on the data flow diagram, the dependency and influence relationship between different data can be revealed. For example, the "AGG_EMPLOYEES" table depends on the data in the "EMPLOYEES" table, thereby determining the data association and facilitating overall management. In this way, a strict logical chain can be formed, and information technology can be used to achieve the whole process management of data from the source to the final storage and processing, ensuring the security and quality of the data.

[0134] See also Figure 2As shown, the present invention also provides a system for generating a database table, the system comprising:

[0135] The first processing module 201 is used to analyze each entity object in the database using an entity recognition algorithm, and to determine the association relationship and relationship type between different entity objects using a semantic similarity calculation method, wherein the relationship type includes one-to-one, one-to-many, and many-to-many;

[0136] The second processing module 202 is used to generate a basic table structure based on the relationship type by using a relationship model design algorithm, wherein the basic table structure includes a table name, a field name, a data type and a field type, wherein the field type includes a primary key field and a foreign key field;

[0137] The third processing module 203 is used to determine the attribute type of each entity object, and based on the attribute type, use the attribute mapping rule to map the attribute of the entity object to the basic table structure to generate a database table;

[0138] The fourth processing module 204 is used to maintain the association relationship between entity objects in the database table and the consistency and integrity of data by using triggers and cascade operation methods;

[0139] The fifth processing module 205 is used to use a time series prediction algorithm to predict the access trend of each entity object within a preset time and generate a database table optimization plan.

[0140] It is understandable that if Figure 1 The contents of the database table generation method embodiment shown in FIG. 1 are applicable to the database table generation system embodiment of the present invention. The functions specifically implemented by the database table generation system embodiment of the present invention are similar to those of the above-mentioned method embodiment. Figure 1 The method for generating the database table shown in the embodiment is the same as that in the embodiment shown in the figure, and the beneficial effects achieved are the same as those in the embodiment shown in the figure. Figure 1 The beneficial effects achieved by the embodiment of the method for generating the database table shown are also the same.

[0141] It should be noted that the information interaction, execution process and other contents between the above-mentioned systems are based on the same concept as the embodiment of the method of the present invention. Their specific functions and technical effects can be found in the method embodiment part and will not be repeated here.

[0142] Those skilled in the art can clearly understand that, for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example for illustration. In practical applications, the above-mentioned function allocation can be completed by different functional units and modules as needed, that is, the internal structure of the system can be divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiment can be integrated in a processing unit, or each unit can exist physically separately, or two or more units can be integrated in one unit. The above-mentioned integrated unit can be implemented in the form of hardware or in the form of software functional units. In addition, the specific names of the functional units and modules are only for the convenience of distinguishing each other, and are not used to limit the scope of protection of this application. The specific working process of the units and modules in the above-mentioned system can refer to the corresponding process in the aforementioned method embodiment, which will not be repeated here.

[0143] See also Figure 3 As shown, an embodiment of the present invention further provides a computer device 3, comprising: a memory 302 and a processor 301 and a computer program 303 stored in the memory 302. When the computer program 303 is executed on the processor 301, a method for generating a database table as described in any one of the above methods is implemented.

[0144] The computer device 3 may be a computing device such as a desktop computer, a notebook, a PDA, or a cloud server. The computer device 3 may include, but is not limited to, a processor 301 and a memory 302. Those skilled in the art will appreciate that Figure 3 It is only an example of computer device 3 and does not constitute a limitation on computer device 3. It may include more or fewer components than shown in the figure, or a combination of certain components, or different components, for example, it may also include input and output devices, network access devices, etc.

[0145] The processor 301 may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor may be a microprocessor or any conventional processor, etc.

[0146] In some embodiments, the memory 302 may be an internal storage unit of the computer device 3, such as a hard disk or memory of the computer device 3. In other embodiments, the memory 302 may also be an external storage device of the computer device 3, such as a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc. equipped on the computer device 3. Further, the memory 302 may also include both an internal storage unit of the computer device 3 and an external storage device. The memory 302 is used to store an operating system, an application program, a boot loader (BootLoader), data, and other programs, such as the program code of the computer program, etc. The memory 302 may also be used to temporarily store data that has been output or is to be output.

[0147] An embodiment of the present invention further provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the method for generating a database table as described in any one of the above methods is implemented.

[0148] In this embodiment, if the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the present application implements all or part of the processes in the above-mentioned embodiment method, which can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a computer-readable storage medium. When the computer program is executed by the processor, the steps of the above-mentioned various method embodiments can be implemented. Among them, the computer program includes computer program code, and the computer program code can be in source code form, object code form, executable file or some intermediate form. The computer-readable medium can at least include: any physical object or device that can carry the computer program code to the camera / terminal device, recording medium, computer memory, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), electric carrier signal, telecommunication signal and software distribution medium. For example, a USB flash drive, a mobile hard disk, a disk or an optical disk. In some jurisdictions, according to legislation and patent practice, computer-readable media cannot be electric carrier signals and telecommunication signals.

[0149] The embodiments described above are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the aforementioned embodiments, a person skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. Such modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application, and should all be included in the protection scope of the present application.

Claims

1. A method for generating a database table, characterized in that: include: Analyze each entity object in the database using an entity recognition algorithm, and determine the association relationship and relationship type between different entity objects using a semantic similarity calculation method, wherein the relationship types include one-to-one, one-to-many, and many-to-many; Based on the relationship type, a relationship model design algorithm is used to generate a basic table structure, wherein the basic table structure includes a table name, a field name, a data type and a field type, wherein the field type includes a primary key field and a foreign key field; Determine the attribute type of each entity object, and based on the attribute type, use attribute mapping rules to map the attributes of the entity object to the basic table structure to generate a database table; Using triggers and cascade operation methods to maintain the association relationship between entity objects in the database table and the consistency and integrity of data; Use time series prediction algorithms to predict the access trends of each entity object within a preset time and generate database table optimization plans; The mapping of the attributes of the entity object to the basic table structure using attribute mapping rules based on the attribute type includes: Mapping basic attributes of entity objects to fields of the basic table structure one by one; For complex attributes in entity objects, by determining the information volume and change frequency of the attribute value corresponding to the complex attribute, a mapping method of the complex attribute to the basic table structure is obtained, wherein the mapping method includes a serialization method and a sub-table establishment method; For multi-valued attributes in entity objects, a sub-table is created for each multi-valued attribute, and the multi-valued attribute is mapped to the basic table structure through a foreign key; For the derived attributes in the entity object, the calculation method of the derived attributes is recorded, and the derived attributes are mapped to the basic table structure; The time series prediction algorithm is used to predict the access trend of each entity object within a preset time and generate a database table optimization plan, including: Collect query logs of each database table through monitoring tools, wherein the query logs include query statements, query time and query users; Based on the query log, calculating the access frequency and query complexity of each of the database tables, and obtaining access pattern data of the database tables; Using a time series prediction algorithm to analyze the access pattern data to obtain the access trend of each database table within a preset time; Based on the access trend, obtaining preliminary suggestions for optimizing the database table, the preliminary suggestions including an index optimization plan, a column storage adjustment plan, and a partition adjustment plan; Each suggestion plan in the preliminary suggestion is rated for change risk to obtain a risk level for each suggestion plan, and a database table optimization plan is determined based on the risk level.

2. The method according to claim 1, characterized in that The entity recognition algorithm is used to analyze each entity object in the database, and the semantic similarity calculation method is used to determine the association relationship and relationship type between different entity objects. The relationship types include one-to-one, one-to-many and many-to-many, including: Acquire predefined entity objects and attribute information corresponding to the entity objects, use an entity recognition algorithm to analyze the attribute information of each entity object, and form an entity data table, wherein the entity data table includes the entity name and attribute information of each entity object, and the attribute information includes the attribute name and data type; Based on the entity data table, a semantic similarity calculation method is used to calculate the similarity of entity names and attribute names between entity objects respectively; Calculating the total similarity between the similarity of the entity name and the similarity of the attribute name; By comparing the total similarity with a preset similarity threshold, marking two entity objects having an association relationship as entity-associated objects; The relationship type of the entity objects is determined by analyzing the uniqueness of the attributes between two entity objects in each group of entity association objects.

3. The method according to claim 1, characterized in that Based on the relationship type, a relationship model design algorithm is used to generate a basic table structure, wherein the basic table structure includes a table name, a field name, a data type and a field type, wherein the field type includes a primary key field and a foreign key field, including: Normalize each of the entity objects and the attribute information corresponding to the entity objects to obtain a basic relationship model; Based on the basic relationship model, generate a basic table structure for each entity object; In the basic table structure, a primary key field, a foreign key field and a preset index statement are set.

4. The method according to claim 1, characterized in that The trigger and cascade operation method are used to maintain the association relationship between entity objects in the database table and the consistency and integrity of the data; Based on the preset trigger definition syntax, determine the execution time of the trigger, write SQL statements and logical conditions inside the trigger, and synchronously modify the entity objects with associated relationships; Acquire entity-related objects in a database, and determine restriction rules for data fields between different entity objects based on association relationships and integrity constraints of the entity-related objects, wherein the entity-related objects are two entity objects with an association relationship; When the data in the main table of the database changes, the cascade operation method is used to obtain the changes in the sub-table data; Based on the changes, a corresponding cascade operation is called through a trigger; By defining transaction boundaries in triggers, all data operations on the database are processed atomically; A trigger management mechanism is used to configure and adjust the trigger to dynamically adjust the behavior of the trigger. The configuration adjustment includes modifying the trigger definition, disabling the trigger, and enabling the trigger.

5. The method according to claim 1, characterized in that The method further comprises: Constructing a data lineage analysis model, and using the data lineage analysis model to track the flow path of data between different database tables and fields; By analyzing SQL statements and data operation logic, a mapping relationship between data and business processes is established, and a data flow diagram is drawn based on the mapping relationship; According to the data flow diagram, the dependency and influence relationship between different data are displayed, and the correlation between the data is determined.

6. A system for generating a database table, characterized in that: include: The first processing module is used to analyze each entity object in the database using an entity recognition algorithm, and to determine the association relationship and relationship type between different entity objects using a semantic similarity calculation method, wherein the relationship types include one-to-one, one-to-many, and many-to-many; The second processing module is used to generate a basic table structure based on the relationship type by using a relationship model design algorithm, wherein the basic table structure includes a table name, a field name, a data type and a field type, and the field type includes a primary key field and a foreign key field; The third processing module is used to determine the attribute type of each entity object, and based on the attribute type, use the attribute mapping rule to map the attribute of the entity object to the basic table structure to generate a database table; The fourth processing module is used to maintain the association relationship between entity objects in the database table and the consistency and integrity of data by using triggers and cascade operation methods; The fifth processing module is used to use a time series prediction algorithm to predict the access trend of each entity object within a preset time and generate a database table optimization plan; The mapping of the attributes of the entity object to the basic table structure using attribute mapping rules based on the attribute type includes: Mapping basic attributes of entity objects to fields of the basic table structure one by one; For complex attributes in entity objects, by determining the information volume and change frequency of the attribute value corresponding to the complex attribute, a mapping method of the complex attribute to the basic table structure is obtained, wherein the mapping method includes a serialization method and a sub-table establishment method; For multi-valued attributes in entity objects, a sub-table is created for each multi-valued attribute, and the multi-valued attribute is mapped to the basic table structure through a foreign key; For the derived attributes in the entity object, the calculation method of the derived attributes is recorded, and the derived attributes are mapped to the basic table structure; The time series prediction algorithm is used to predict the access trend of each entity object within a preset time and generate a database table optimization plan, including: Collect query logs of each database table through monitoring tools, wherein the query logs include query statements, query time and query users; Based on the query log, calculating the access frequency and query complexity of each of the database tables, and obtaining access pattern data of the database tables; Using a time series prediction algorithm to analyze the access pattern data to obtain the access trend of each database table within a preset time; Based on the access trend, obtaining preliminary suggestions for optimizing the database table, the preliminary suggestions including an index optimization plan, a column storage adjustment plan, and a partition adjustment plan; Each suggestion plan in the preliminary suggestion is rated for change risk to obtain a risk level for each suggestion plan, and a database table optimization plan is determined based on the risk level.

7. A terminal device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the method according to any one of claims 1 to 5 is implemented.

8. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the method according to any one of claims 1 to 5 is implemented.

Images