A database model automatic construction method and system

Abstract

The application provides a database model automatic construction method and system, and the method comprises the following steps: obtaining information in a first table structure table, wherein the information in the table structure table at least comprises the following technical fields: unique identification ID, data flag, release time, update time, update ID, valid flag, and deletion time; the unique identification ID is generated according to the release time of the information, and when multiple pieces of information are released at the same time, the unique identification ID is sorted; the data flag is used for recording data generation flag; the update ID is generated according to the update time of the information, and when multiple pieces of information are updated at the same time, the update ID is sorted; and a first database model is automatically constructed according to the information in the first table structure table. The database model automatic construction method and system provided by the application can realize automatic construction of a database by providing standardized technical fields, avoid mistakes in understanding and operation of database model establishment by developers, and greatly improve work efficiency.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database management, in particular to a database model automatic construction method and system. BACKGROUND

[0002] The database modeling on the market requires developers to write code to process each model to be established, and requires developers to extract the required model from the modeling development needs of different templates of different business personnel. If the business personnel need to modify the model, the developers need to write code again to modify the model. In fact, not only do developers need to spend time understanding the requirements, extracting and communicating with business personnel, but subsequent business personnel also have many obstacles in understanding, changing and upgrading the functions of existing models. Moreover, the existing database modeling is prone to differences between models established by different business personnel, and data cannot be standardized managed.

[0003] Therefore, it is necessary to provide a database model automatic construction method and device to solve the above problems. SUMMARY

[0004] The present application provides a database model automatic construction method and system to solve the problems and deficiencies of the prior art.

[0005] The present application solves the above technical problems by the following technical solutions:

[0006] The present application provides a database model automatic construction method, which comprises:

[0007] Obtain information in a first table structure table, the information in the table structure table at least includes the following technical fields: unique identifier ID, data flag, release time, update time, update id, valid flag, deletion time;

[0008] The unique identifier ID is generated according to the release time of the information, and when multiple information is released at the same time, the unique identifier ID is sorted, and the unique identifier ID is set as a trigger;

[0009] The data flag is used to record the data generation flag, and the data flag is set as a trigger;

[0010] The update id is generated according to the update time of the information, and when multiple information is updated at the same time, the update id is sorted, and the update id is set as a trigger;

[0011] According to the information in the first table structure table, a first database model is automatically constructed.

[0012] Preferably, the publishing time is used to record the data storage time, the updating time is used to record the data updating time, the deleting time is used to record the data invalid time, and the valid flag is used to record the data validity or invalidity.

[0013] Preferably, the information in the second table structure table is acquired, a corresponding relationship between the information in the second table structure table and the information in the first table structure table is established in a mapping table, and the second database model is automatically constructed according to the corresponding relationship.

[0014] Preferably, the data flag default value is automatically generated, the publishing time default value is the current time and does not change according to the updating, the updating time default value is the current time and changes according to the updating, the valid flag default value is valid, and the deleting time default value is 9999-12-31 00:00:00.000000.

[0015] Preferably, the first database model comprises a development document table, and the development document table comprises remark information, a data source table, a source field and a constant source.

[0016] The application further provides a database model automatic construction system, which comprises:

[0017] an information acquisition module, which is used to acquire information in a first table structure table, and the information in the table structure table at least comprises the following technical fields: a unique identification ID, a data flag, a publishing time, an updating time, an updating ID, a valid flag and a deleting time; the unique identification ID is generated according to the publishing time of the information, the unique identification ID is sorted when multiple pieces of information are published at the same time, and the unique identification ID is set as a trigger; the data flag is used to record the data generation flag, and the data flag is set as a trigger; the updating ID is generated according to the updating time of the information, the updating ID is sorted when multiple pieces of information are updated at the same time, and the updating ID is set as a trigger;

[0018] a database model automatic construction module, which is used to automatically construct a first database model according to the information in the first table structure table.

[0019] Preferably, the publishing time is used to record the data storage time, the updating time is used to record the data updating time, the deleting time is used to record the data invalid time, and the valid flag is used to record the data validity or invalidity.

[0020] Preferably, the information in the second table structure table is acquired, a corresponding relationship between the information in the second table structure table and the information in the first table structure table is established in a mapping table, and the second database model is automatically constructed according to the corresponding relationship.

[0021] Preferably, the data flag default value is automatically generated, the publishing time default value is the current time and does not change according to the update, the update time default value is the current time and changes according to the update, the validity flag default value is valid, and the deletion time default value is 9999-12-31 00:00:00.000000.

[0022] Preferably, the first database model includes a development document table, and the development document table includes note information, a data source table, a source field, and a constant source.

[0023] Compared with the prior art, the technical scheme of the embodiment of the present application has the following beneficial effects:

[0024] The database model automatic construction method and system provided by the embodiment of the present application can realize automatic construction of a database by providing standardized technical fields, avoid mistakes in understanding and operation of a database model by a developer, and greatly improve work efficiency.

[0025] Further, by establishing a corresponding relationship between the information in the second table structure table and the information in the first table structure table in the mapping table, and automatically constructing a second database model according to the corresponding relationship, the second structure table can also adapt to the automatic construction of the database model, so that the second database model can be quickly and automatically constructed. BRIEF DESCRIPTION OF DRAWINGS

[0026] Figure 1 A flowchart of a database model automatic construction method provided by an embodiment of the present application is shown.

[0027] Figure 2 A structure diagram of a database model automatic construction system provided by an embodiment of the present application is shown. DETAILED DESCRIPTION

[0028] To make the objectives, technical schemes, and advantages of the embodiments of the present application clearer, the technical scheme of the embodiments of the present application will be described in detail below with reference to the drawings of the embodiments of the present application. Obviously, the described embodiments are some embodiments of the present application, but not all embodiments of the present application. Based on the embodiments of the present application, all other embodiments obtained by those of ordinary skill in the art without creative work fall within the scope of protection of the present application.

[0029] The technical scheme of the present application will be described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes can not be described in some embodiments.

[0030] Based on the problems in the prior art, such asFigure 1 As shown, the present invention provides a method for automatically constructing a database model, the method comprising:

[0031] Step S101: Obtain information from the first table structure table. The information in the table structure table includes at least the following technical fields: Unique Identifier ID, Data Flag, Publication Time, Update Time, Update ID, Validity Flag, and Deletion Time. The Unique Identifier ID is generated based on the publication time of the information. When multiple pieces of information are published at the same time, the Unique Identifier ID is sorted and set as a trigger. The Data Flag is used to record the data generation flag and is set as a trigger. The Update ID is generated based on the update time of the information. When multiple pieces of information are updated at the same time, the Update ID is sorted and set as a trigger.

[0032] In practice, the unique identifier (ID) is used to record the ID of each row of data, facilitating the unique identification of a specific row. The unique identifier is calculated based on the publication time and serves as the primary key of the data. When multiple data entries are published simultaneously, the unique identifiers are sorted. During automatic database model construction, the unique identifier is set as a trigger; when data is entered into the table, the unique identifier is automatically generated based on the entry time. The unique identifier is a 19-digit number; the first 15 digits are automatically generated based on the current time, and the last 4 digits are used for sorting. If multiple pieces of information are updated or inserted at the same time, they will be sorted from 0001 to 9999.

[0033] The update ID is a sequence reflecting information updates, generated based on the update time. Once information is updated, such as a change in the value of a technical field, the update ID will change according to the time of the change. If multiple pieces of information are updated or inserted at the same time, they will be sorted from 0001 to 9999.

[0034] The trigger for the unique identifier ID is a pre-insertion trigger, which causes the function that automatically generates the ID to generate a unique identifier ID before the data is inserted. It is also stipulated that the update ID and the unique identifier ID are equal at this time. In this way, when a new row of data is inserted, the unique identifier ID will be automatically generated without manual input. The unique identifier ID and the update ID of the first insertion are consistent.

[0035] The trigger for the update ID is a pre-update trigger. When the data is updated, the UPID function is automatically referenced to generate the update ID, so that the update ID changes when the data changes.

[0036] Step S102: Automatically construct the first database model based on the information in the first table structure table.

[0037] After obtaining the information from the first table structure table, the first database model is automatically constructed, and the primary key, uniqueness constraint, triggers, etc. of the first database model are generated accordingly.

[0038] The primary key is used to record the primary key information of the model. When automatically building the database model, it is set as the primary key of the database model and serves as a candidate key for the unique identifier of a row in the table.

[0039] Uniqueness constraints are used to record the uniqueness constraint information of the model. When automatically building the database model, they are set as unique indexes to ensure the uniqueness of each row of data in the database table model.

[0040] In specific implementation, the release time is used to record the data entry time, the update time is used to record the data update time, the deletion time is used to record the data expiration time, and the validity flag is used to record whether the data is valid or invalid.

[0041] The publication time is used to record the time when data is entered into the database, making it easy to check the data entry status. This publication time will be used as the entry time for subsequent data storage in the database model.

[0042] The update time is used to record the time when data is updated, facilitating the viewing of data update status. The update time for the initial data entry is equal to the publication time. Subsequent updates to this unique identifier ID will record the time of the change. This update time will be used as the data change time during subsequent data storage in the database model.

[0043] The deletion time is used to record the time when the data becomes invalid. For valid data, this field defaults to 9999123100000. If data is deleted, the deletion time is changed to the current time. The lifecycle of this data is: publication time – deletion time. During subsequent data storage in the database model, if a piece of data becomes invalid, its deletion time can be changed to the current time to perform a soft deletion.

[0044] In specific implementation, information from the second table structure is obtained, and a correspondence between the information in the second table structure and the information in the first table structure is established in the mapping table. Based on the correspondence, a second database model is automatically constructed.

[0045] For example, identifying the technical fields in the second table structure as the technical fields in the first table structure involves adding a mapping relationship between the technical fields in the first table structure and the technical fields in the second table structure to the mapping table. Once the mapping relationship is established, the second table structure can automatically build and generate the second database model.

[0046] In practice, it is not necessary to map all the technical fields in the second table structure table. Only the key technical fields in the second table structure table need to be mapped to adapt to the technical fields in the first table structure table, so that the second database model can be automatically generated.

[0047] After obtaining the information from the second table structure, a correspondence is established between the information in the second table structure and the information in the first table structure in the mapping table. Based on the correspondence, a second database model is automatically constructed, and the primary key, uniqueness constraint, trigger, etc. of the second database model are generated accordingly.

[0048] The primary key is used to record the primary key information of the model. When automatically building the database model, it is set as the primary key of the database model and serves as a candidate key for the unique identifier of a row in the table.

[0049] Uniqueness constraints are used to record the uniqueness constraint information of the model. When automatically building the database model, they are set as unique indexes to ensure the uniqueness of each row of data in the database table model.

[0050] In specific implementation, the default value of the data flag is automatically generated, the default value of the publication time is the current time and does not change according to the update, the default value of the update time is the current time and changes according to the update, the default value of the validity flag is valid, and the default value of the deletion time is 9999-12-3100:00:00.000000.

[0051] The default value of the data flag records the data generation flag, which has a default value of 1, indicating automatic generation. When the data flag is changed to 2, indicating manual modification, the data will no longer be automatically updated. During automatic database model construction, the data flag is set as a trigger. When data is entered into the table, the data flag will be automatically generated with a value of 1. When the value of this field is changed to 2, the trigger will prevent the automatic update of that row of data.

[0052] The default value of the validity flag is set to 1, indicating that the data is valid. If a row of data needs to be soft deleted, the validity flag of the row of data can be set to 0, indicating that the data is invalid.

[0053] Once data invalidation is detected, the valid flag field can be processed. The valid flag is invalidated and assigned a value of 0, and the deletion time is assigned the current time. This marks the invalid information while preserving historical traces.

[0054] In a specific implementation, the first database model includes a development documentation table, which includes notes, a data source table, source fields, and constant sources.

[0055] The development documentation table records notes on technical fields, data source tables, source fields, and constant sources. Technical fields include: Unique Identifier (ID), Data Flag, Release Time, Update Time, Update ID, Validity Flag, and Deletion Time.

[0056] The remarks section is to be filled in by business personnel to help developers understand the meaning of the fields and to facilitate business personnel in viewing the table's development change records.

[0057] Data source table and source field: When the technical field data of the automatically built database model comes from other forms, the form name and form field can be written after the technical field to facilitate developers' viewing and understanding.

[0058] Source of constants: When a technical field in an automatically built database model is a constant, an explanation can be added after the corresponding technical field in that column to facilitate developers' understanding.

[0059] The present invention also provides an automatic database model construction system, the system comprising:

[0060] The information acquisition module 21 is used to acquire information from a first table structure table. The information in the table structure table includes at least the following technical fields: unique identifier ID, data flag, publication time, update time, update ID, validity flag, and deletion time. The unique identifier ID is generated based on the publication time of the information. When multiple pieces of information are published at the same time, the unique identifier ID is sorted and set as a trigger. The data flag is used to record data generation flags and is set as a trigger. The update ID is generated based on the update time of the information. When multiple pieces of information are updated at the same time, the update ID is sorted and set as a trigger.

[0061] The database model automatic construction module 22 is used to automatically construct the first database model based on the information in the first table structure table.

[0062] In specific implementation, the release time is used to record the data entry time, the update time is used to record the data update time, the deletion time is used to record the data expiration time, and the validity flag is used to record whether the data is valid or invalid.

[0063] In specific implementation, information from the second table structure is obtained, and a correspondence between the information in the second table structure and the information in the first table structure is established in the mapping table. Based on the correspondence, a second database model is automatically constructed.

[0064] In specific implementation, the default value of the data flag is automatically generated and assigned a value of 1. The default value of the publication time is the current time and does not change after the update, while the default value of the update time is the current time and changes after the update. The default value of the validity flag is valid and assigned a value of 1. The default value of the deletion time is 9999-12-31 00:00:00.000000.

[0065] In a specific implementation, the first database model includes a development documentation table, which includes notes, a data source table, source fields, and constant sources.

[0066] In summary, the database model automatic construction method and system provided by the embodiments of the present invention, by providing standardized technical fields, realizes automatic construction of database models, avoids errors in developers' understanding and operation of database model establishment, and greatly improves work efficiency;

[0067] Furthermore, by establishing a correspondence between the information in the second table structure table and the information in the first table structure table in the mapping table, a second database model is automatically constructed based on the correspondence. This allows the second structure table to adapt to the automatic construction of the database model, thereby quickly and automatically constructing the second database model.

[0068] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features therein. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for automatically constructing a database model, characterized in that, The method includes: Obtain information from the first table structure table, wherein the information in the table structure table includes at least the following technical fields: unique identifier ID, data flag, publication time, update time, update ID, validity flag, and deletion time; The unique identifier ID is generated based on the publication time of the information. When multiple pieces of information are published at the same time, the unique identifier ID is sorted and set as a trigger. The data flag is used to record the data generation flag, and the data flag is set as a trigger; The update ID is generated based on the update time of the information. When multiple pieces of information are updated at the same time, the update ID is sorted and set as a trigger. The first database model is automatically constructed based on the information in the first table structure table; the data flag is used to record the data generation flag, and its default value is 1, indicating automatic generation; when the data flag is modified to 2, indicating manual modification, the data will no longer be automatically updated; Obtain information from the second table structure table, establish a correspondence between the information in the second table structure table and the information in the first table structure table in the mapping table, and automatically construct a second database model based on the correspondence. The publication time is used to record the time when the data is entered into the database, the update time is used to record the time when the data is updated, the deletion time is used to record the time when the data becomes invalid, and the validity flag is used to record whether the data is valid or invalid. The default value of the validity flag is 1, indicating that the data is valid. If a row of data needs to be soft-deleted, the validity flag of that row of data is assigned a value of 0, and the deletion time is assigned the current time. The default value of the data flag is automatically generated, the default value of the publication time is the current time and does not change after the update, the default value of the update time is the current time and changes after the update, the default value of the validity flag is valid, and the default value of the deletion time is 9999-12-3100:00:00.000000. When the database model is automatically constructed, the data flag and unique identifier ID are used.

2. The automatic database model construction method according to claim 1, characterized in that, The first database model includes a development documentation table, which includes notes, a data source table, source fields, and constant sources.

3. A database model automatic construction system, characterized in that, The system includes: The information acquisition module is used to acquire information from a first table structure table. The information in the table structure table includes at least the following technical fields: Unique Identifier ID, Data Flag, Publication Time, Update Time, Update ID, Validity Flag, and Deletion Time. The Unique Identifier ID is generated based on the publication time of the information. When multiple pieces of information are published at the same time, the Unique Identifier ID is sorted, and the Unique Identifier ID is set as a trigger. The Data Flag is used to record data generation flags, and the Data Flag is set as a trigger. The Update ID is generated based on the update time of the information. When multiple pieces of information are updated at the same time, the Update ID is sorted, and the Update ID is set as a trigger. The database model automatic construction module is used to automatically construct the first database model based on the information in the first table structure table; the data flag is used to record the data generation flag, the default value of which is 1, indicating automatic generation; when the data flag is modified to 2, indicating manual modification, the data will no longer be automatically updated. Obtain information from the second table structure table, establish a correspondence between the information in the second table structure table and the information in the first table structure table in the mapping table, and automatically construct a second database model based on the correspondence. The publication time is used to record the time when the data is entered into the database, the update time is used to record the time when the data is updated, the deletion time is used to record the time when the data becomes invalid, and the validity flag is used to record whether the data is valid or invalid. The default value of the validity flag is 1, indicating that the data is valid. If a row of data needs to be soft-deleted, the validity flag of that row of data is assigned a value of 0, and the deletion time is assigned the current time. The default value of the data flag is automatically generated, the default value of the publication time is the current time and does not change after the update, the default value of the update time is the current time and changes after the update, the default value of the validity flag is valid, and the default value of the deletion time is 9999-12-3100:00:00.000000. When the database model is automatically constructed, the data flag and unique identifier ID are used.

4. The database model automatic construction system according to claim 3, characterized in that, The first database model includes a development documentation table, which includes notes, a data source table, source fields, and constant sources.

Images