A database design method suitable for super multi-parameter multi-vehicle type data recording

Abstract

The application relates to the technical field of databases, in particular to a database design method suitable for super-multiple-parameter multi-vehicle-type data recording. A data storage database A_DATA.mdb is created, which is used for storing created data tables; the data tables take parameter numbers as record numbers and vehicle type numbers as fields when being created; the creation process of the data tables comprises the following steps: creating seven basic data tables, running a data table generation program, and automatically generating data tables; the data table names are respectively composed of basic data table names+iX, iX=Int(Val(Right(sVehicleID, 4)-1) / 200), and sVehicleID is a vehicle type field. The database designed by the method is not limited in the number of fields, the number of fields can be increased or decreased at will, and more vehicle type data can be recorded.

Description

Technical Field

[0001] This invention relates to the field of database technology, and specifically to a database design method suitable for recording data of numerous parameters and multiple vehicle models. Background Technology

[0002] Databases have virtually no limit on the number of records, as long as the computer hardware supports it; however, there are limits on the number of data fields. For example, an Access database supports no more than 252 data fields. As the number of vehicle models increases, the number of data table fields must also increase; however, program files and data files must be stored separately, and data must be accessed through links to ensure that data is not affected during program upgrades; however, linked data tables cannot have their fields edited. When the number of vehicle models exceeds the field limit, the database is no longer suitable. For example, consider designing a regulatory certification parameter management system with over 1100 regulatory parameters, which are constantly increasing. Each parameter contains 16 fields, and 7 parameters vary depending on the vehicle model. Developing this system using an Access database cannot be done using normal methods; a completely new database design approach must be invented. Summary of the Invention

[0003] The purpose of this invention is to address the shortcomings of existing technologies by providing a database design method suitable for recording data of numerous parameters and vehicle models. This method uses parameter numbers as the number of records and vehicle model numbers as fields, and transposes the entire data table. The database designed by this method has no limit on the number of fields, which can be added or removed at will, and can record more vehicle model data.

[0004] This invention provides a database design method applicable to recording data of numerous parameters and multiple vehicle models, including...

[0005] Create a database A_DATA.mdb to store the newly created data tables and existing parameter tables and organization / personnel tables. When creating the data tables, the parameter number is used as the record number, and the vehicle model number is used as a field. The creation process of the data tables includes:

[0006] Create seven basic data tables, including parameter content DATATOTAL, data entry person SPRPS, input date INPUTDATE, record number of the corresponding module MODRECORD, data reviewer VALIDER, review date VALIDDATE, and data status VALID.

[0007] Run the data table generation program to automatically generate data tables, each recording 200 vehicle model data. The vehicle model number is designed as V plus 4 digits. The field range of the first data table is V0001~V0200…… and the field range of the seventh data table is V1201~V1400. The data table names are composed of the basic data table name + iX, where iX = Int(Val(Right(sVehicleID,4)-1) / 200), and sVehicleID is the vehicle model field.

[0008] More preferably, the data table generation method includes:

[0009] Create a parameter number field F_ID, including:

[0010] Create a record parameter content data table with parameter encoding field;

[0011] Create a record with parameters for the user's data table and the corresponding code field.

[0012] When creating a record, fill in the date data table parameter encoding field;

[0013] Create a record by filling in the status data table parameter encoding field;

[0014] Create a record parameter approver data table with parameter code field;

[0015] Create a record with parameters, approval date, and a data table containing parameter encoding fields.

[0016] Create the vehicle type field sVehicleID, which includes:

[0017] The values ​​of iX are sequentially taken from 0 to conTableNum, and the values ​​of j are sequentially taken from 1 to 200. The above steps are repeated based on different values ​​of iX and j until all data tables are generated. Here, conTableNum is the number of data tables, and sVehicleID is any number from V0001 to V9999.

[0018] Based on iX and j, the vehicle type field sVehicleID = "V"&Right("000"&200*iX+j,4) is obtained;

[0019] The new record parameter content data table includes the vehicle model number field;

[0020] Add a new record parameter: the vehicle model number field in the data table for the person who entered the parameter.

[0021] When adding a new record, fill in the date data table's vehicle model number field as the parameter.

[0022] Add new record parameters by filling in the vehicle model number field of the status data table;

[0023] Add a new record parameter, the vehicle model number field, to the reviewer data table;

[0024] Add a new record parameter: Review Date data table vehicle model number field;

[0025] j loop;

[0026] iX loop;

[0027] When a new vehicle model is created, the name of the data storage file is calculated based on the vehicle model number. If the file record is empty, the parameter number filling method is executed to fill in the parameter number.

[0028] A preferred approach also includes designing a data table model within the program database RHN.mdb for all data that users need to write, and automatically creating a series of data tables named after the user's login name strUserCode + the model data table name when the user logs in, and copying the model data structure into the created series of data tables.

[0029] More preferably, the parameter number filling method includes:

[0030] Use the subscript variable Dat(i) to store the parameter record table name;

[0031] Use data records to define a summary table of variable parameters;

[0032] The loop variable i takes values ​​from 0 to 7 in sequence;

[0033] Move the parameter summary table to the initial record;

[0034] Define the data record variable as the newly added record;

[0035] Write the parameter codes from the parameter summary table into the parameter record table;

[0036] The parameter record table records variable updates;

[0037] The parameter summary table records the variable to the next record;

[0038] Repeat the above steps until the last record in the parameter summary table is entered, then the loop ends.

[0039] The loop variable i loops;

[0040] Close the parameter record table to record variables.

[0041] A more preferred approach also includes linking the data tables in the data file A_DATA.mdb in the program file RHN.mdb, and performing the following associations:

[0042] Associate the responsible department, associated module, field name, parameter priority, parameter content, data entry person, input date, record number of the corresponding module, data reviewer, review date, and data status with the corresponding parameter table.

[0043] A more preferred method also includes generating a master data table A1 for the selected vehicle model (sVehicleID), the generation method of which includes:

[0044] Copy the parameter data of the selected vehicle model to the temporary data table strUsercode&"_TEMP;

[0045] Based on the relationships between the parameter tables, a Structured Query Language (SQL) table is generated to obtain the combined data table strUserCode&"_A1".

[0046] More preferably, the method also includes searching based on the Structured Query Language (SQL), wherein the search method includes:

[0047] The search interface generates Structured Query Language (SQL);

[0048] Retrieve the field names Field1, Field2, Field3… from the SQL query;

[0049] Create a data table OrientTable with vehicle number V_ID, Field1, Field2, Field3… and copy an empty table NewTable;

[0050] Add the vehicle model numbers from the selected vehicle model list to the created data table OrientTable;

[0051] Open the parameter value record table DATATOTAL+iX, search for Field1, Field2, Field3... corresponding to the selected vehicle model, and write the data to the OrientTable to complete the field record transposition;

[0052] Execute an SQL query on the OrientTable data table and store the query results in an empty table NewTable.

[0053] The beneficial effects of this invention are as follows:

[0054] 1. Create a database A_DATA.mdb to store the newly created data tables and existing parameter and personnel tables. This method uses the parameter number as the record count and the vehicle model number as the field, transposing the data tables. The number of fields in this database is unlimited and can be added or removed freely; it remains applicable even when the number of vehicle models exceeds the field limit. Furthermore, since the number of records in each data table equals the number of parameters, the system will not slow down due to the increasing number of records as the data volume increases (existing operating systems will experience lag when exceeding 100,000 records).

[0055] 2. When selecting the vehicle model number sVehicleID, the parameter data of the selected vehicle model is copied to a temporary data table strUsercode&"_TEMP; and a structured query language SQL is generated based on the relationship between the parameter tables to obtain a combined data table strUserCode&"_A1, thus realizing the generation of the overall data table for easy searching and querying.

[0056] 3. By using the structured query language SQL, queries can be performed on the OrientTable data table created with vehicle number V_ID, Field1, Field2, Field3… This solves the defect of existing databases that can only perform conditional searches on fields and cannot perform parameter searches that meet specific conditions for multiple vehicle models.

[0057] 4. Within the program database RHN.mdb, design a data table model for all users who need to write data. When a user logs in, automatically create a series of data tables named after the user's login name (strUserCode) followed by the model data table name, and copy the model data structure over. Copy the user's authorized data from the generated master data table to the created data tables. Each user edits their own unique data table, avoiding data conflicts with other users. When a user logs out, automatically delete the data table starting with the user's login name. This method solves the data conflict problem and supports up to 252 users online simultaneously. Attached Figure Description

[0058] Figure 1 This is a schematic diagram illustrating the data table association in this invention;

[0059] Figure 2 This is a schematic diagram illustrating the search process based on the Structured Query Language (SQL) of this invention. Detailed Implementation

[0060] To make the technical problems, technical solutions, and beneficial effects to be solved by this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and are not intended to limit the scope of this application.

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

[0062] It should also be understood that the term “and / or” as used in this application specification and the appended claims means any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.

[0063] As used in this application specification and the appended claims, the term "if" may be interpreted, depending on the context, as "when," "once," "in response to determination," or "in response to detection." Similarly, the phrase "if determined" or "if detected [the described condition or event]" may be interpreted, depending on the context, as meaning "once determined," "in response to determination," "once detected [the described condition or event]," or "in response to detection [the described condition or event]."

[0064] Furthermore, in the description of this application and the appended claims, the terms "first," "second," "third," etc., are used only to distinguish descriptions and should not be construed as indicating or implying relative importance.

[0065] References to "one embodiment" or "some embodiments" in this specification mean that one or more embodiments of this application include a specific feature, structure, or characteristic described in connection with that embodiment. Therefore, the phrases "in one embodiment," "in some embodiments," "in other embodiments," "in still other embodiments," etc., appearing in different parts of this specification do not necessarily refer to the same embodiment, but rather mean "one or more, but not all, embodiments," unless otherwise specifically emphasized. The terms "comprising," "including," "having," and variations thereof mean "including but not limited to," unless otherwise specifically emphasized. "A plurality" means "two or more."

[0066] Example 1

[0067] This embodiment is a preferred embodiment of a database design method applicable to data records of numerous parameters and multiple vehicle models. For ease of explanation, only the parts relevant to this embodiment are shown, and are detailed below:

[0068] This invention provides a database design method applicable to recording data of numerous parameters and multiple vehicle models, including...

[0069] Create a database A_DATA.mdb to store the newly created data tables and existing parameter tables and personnel tables. When creating the data tables, the parameter number will be used as the number of records, and the vehicle model number as a field. The creation process of the data tables includes (the following processes are performed within this database):

[0070] A single regulatory parameter contains seven vehicle model-related fields, requiring the creation of seven basic data tables named as follows: Parameter Content DATATOTAL, Data Enterer SPRSP, Input Date INPUTDATE, Record Number of the Parameter Corresponding Module MODRECORD, Data Reviewer VALIDER, Review Date VALIDDATE, and Data Status VALID.

[0071] Since linked data tables cannot be created in real-time via program code, all data tables must be created in advance. Run the data table generation program to automatically generate the data tables. Based on the upper limit requirement for data table fields, each data table records 200 vehicle model data entries, with the vehicle model number designed as V followed by 4 digits, ranging from V0001 to V9999. The fields in the first data table range from V0001 to V0200, the fields in the second data table range from V0201 to V0400, the fields in the third data table range from V0401 to V0600, the fields in the fourth data table range from V0601 to V0800, the fields in the fifth data table range from V0801 to V1000, the fields in the sixth data table range from V1001 to V1200, and the fields in the seventh data table range from V1201 to V1400. The names of the data tables are composed of the basic data table name + iX, where iX = Int(Val(Right(sVehicleID,4)-1) / 200), and sVehicleID is the vehicle model field.

[0072] When a new vehicle model is created, the name of the data storage file is calculated based on the vehicle model number. If the file record is empty, the parameter number filling method is executed to fill in the parameter number.

[0073] In one embodiment, to resolve multi-user data conflict issues, a data table model (containing only fields and no records) is designed within the program database RHN.mdb for all users to write data to. Upon user login, a series of data tables named with the user's login name (strUserCode) plus the model data table name are automatically created, and the model data structure is copied into these created tables. User-permitted data is copied from the generated master data table to the created data tables, ensuring each user edits only their own unique data table, thus avoiding data conflicts with other users. When a user logs out of the system, the data table starting with the user's login name is automatically deleted.

[0074] In one embodiment, the data table generation method includes:

[0075] Code001'iX Loop statement from 0 to conTableNum

[0076] Code002' Create a record with parameters, content, data table, parameter encoding field (F_ID)

[0077] Code003' Create Record Parameters: Enter Person Data Table Parameters: Encoding Field (F_ID)

[0078] Code004' Create a record. Parameters: Enter the date, data table parameters, and the code field (F_ID).

[0079] Code005' Create a record. Parameters: Fill in the status data table parameters. Parameter code field (F_ID)

[0080] Code006' Create Record Parameters: Auditor Data Table Parameter Code Field (F_ID)

[0081] Code007' Create record parameters: Approval date, Data table parameters, Encoding field (F_ID)

[0082] Create vehicle model field

[0083] Code008'j: Loop statement from 1 to 200

[0084] Code009' derives the vehicle number sVehicleID from iX and j: sVehicleID = "V"&Right("000"&200*iX+j,4)

[0085] Code010 adds a new record with the following parameters: data table vehicle ID field.

[0086] Code011' Adds a new record with the parameter input person's data table vehicle number field (sVehicleID)

[0087] Code012' Adds a new record with parameters including the date and vehicle ID field from the data table.

[0088] Code013' Add record parameters: Enter the vehicle ID field from the status data table.

[0089] Code014 adds a new record parameter, the reviewer, and the vehicle ID field in the data table.

[0090] Code015 adds a new record parameter: Review Date, Data Table, Vehicle ID field (sVehicleID).

[0091] Code016'j loop

[0092] Code017 iX Loop

[0093] Here, `conTableNum` represents the number of data tables. One table corresponds to 200 vehicle models, 50 tables correspond to 10,000 vehicle models, and if there are more than 10,000 vehicle models, the vehicle model code must be changed to a 5-digit number. `sVehicleID` can be any number from V0001 to V9999, thus establishing a correspondence between vehicle models and data tables.

[0094] In one embodiment, the parameter number filling method includes:

[0095] Code001's subscript variable Dat(7) represents a data table containing 7 parameters.

[0096] Code002 uses a data record to define a summary table of variable parameters.

[0097] Code003'i Loop statement from 1 to 7

[0098] Code004' Move the parameter summary table to the initial record

[0099] Code005 uses a data record variable to define a parameter record table (Dat(i)).

[0100] Code006' Data Loop Statement

[0101] Code007 defines the data record variable as the newly added record.

[0102] Code008' Parameter Summary Table: Parameter Codes Written into Parameter Record Table

[0103] Code009' parameter record data table records variable updates

[0104] Code010' Parameter Summary Table records variables to the next record.

[0105] Code011' Loops to the last record in the parameter list and then ends.

[0106] Code012' Close the parameter record table to record variables

[0107] Code013'i loop

[0108] Code014' Close the parameter summary table to record variables

[0109] Each parameter is assigned a number, starting with "F" followed by four digits, ranging from F0001 to F9999, depending on the actual number of parameters. The created data table is initially empty. When the data table corresponding to a newly created vehicle model is empty, the parameter numbering method described in the appendix is ​​executed.

[0110] In one embodiment, the method further includes linking the data tables in the data file A_DATA.mdb to the program file RHN.mdb and performing the following associations:

[0111] like Figure 1 As shown, the responsible department, associated module, field name, parameter priority, parameter content, data filler, input date, record number of the corresponding module, data reviewer, review date, and data status are associated with the corresponding parameter table.

[0112] In one embodiment, when selecting vehicle model number sVehicleID, a data summary table A1 for that vehicle model is generated, and the generation method includes:

[0113] Code001 copies the parameter data of the selected vehicle model to the temporary data table strUsercode&"_TEMP".

[0114] Code002' according to Figure 1 The data table relationships are analyzed to generate SQL (Structured Query Language), resulting in the combined data table strUserCode&"_A1".

[0115] By calling the data table strUserCode&"_A1", you can obtain all the data of the selected car model, and then input, modify, and query it.

[0116] In one embodiment, due to the transposition of fields and records in the data table, conditional queries can only be performed on fields, making conventional search queries impossible. This also includes searches based on the Structured Query Language (SQL), such as... Figure 2 As shown, the search method includes:

[0117] The search interface generates Structured Query Language (SQL);

[0118] Retrieve the field names Field1, Field2, Field3… from the SQL query;

[0119] Create a data table OrientTable with vehicle number V_ID, Field1, Field2, Field3… and copy an empty table NewTable;

[0120] Add the vehicle model numbers from the selected vehicle model list to the created data table OrientTable;

[0121] Open the parameter value record table DATATOTAL+iX, search for Field1, Field2, Field3... corresponding to the selected vehicle model, and write the data to the OrientTable to complete the field record transposition;

[0122] Execute an SQL query on the OrientTable data table and store the query results in an empty table NewTable.

[0123] It should be understood that the specific order or hierarchy of steps in the disclosed process is an example of an exemplary method. Based on design preferences, it should be understood that the specific order or hierarchy of steps in the process may be rearranged without departing from the scope of this disclosure. The appended method claims provide elements of various steps in an exemplary order and are not intended to limit the scope to the specific order or hierarchy described.

[0124] In the above detailed description, various features are combined together in a single embodiment to simplify this disclosure. This approach to disclosure should not be construed as reflecting an intention that embodiments of the claimed subject matter require more features than are explicitly stated in each claim. Rather, as reflected in the appended claims, the invention is presented with fewer features than all of the features of the single disclosed embodiment. Therefore, the appended claims are hereby explicitly incorporated into the detailed description, wherein each claim stands alone as a preferred embodiment of the invention.

[0125] The disclosed embodiments have been described above to enable any person skilled in the art to implement or use the present invention. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein can be applied to other embodiments without departing from the spirit and scope of this disclosure. Therefore, this disclosure is not limited to the embodiments given herein, but is consistent with the broadest scope of the principles and novel features disclosed in this application.

[0126] The foregoing description includes examples of one or more embodiments. It is certainly impossible to describe all possible combinations of components or methods in order to describe the above embodiments, but those skilled in the art will recognize that further combinations and arrangements of the various embodiments are possible. Therefore, the embodiments described herein are intended to cover all such changes, modifications, and variations that fall within the scope of the appended claims. Furthermore, the term "comprising" as used in the specification or claims is interpreted in a manner similar to the term "including," as it is used as a conjunction in the claims. Additionally, the use of any term "or" in the specification of the claims is intended to mean "non-exclusive or."

[0127] The above-described embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application 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 of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A database design method suitable for recording data of numerous parameters and multiple vehicle models, characterized in that: include Create a database A_DATA.mdb to store the newly created data tables and existing parameter tables and organization / personnel tables. When creating the data tables, the parameter number is used as the record number, and the vehicle model number is used as a field. The creation process of the data tables includes: Create seven basic data tables, including parameter content DATATOTAL, data entry person SPRPS, input date INPUTDATE, record number of the corresponding module MODRECORD, data reviewer VALIDER, review date VALIDDATE, and data status VALID. Run the data table generation program to automatically generate data tables, each recording 200 vehicle model data. The vehicle model number is designed as V plus 4 digits. The field range of the first data table is V0001~V0200... and the field range of the seventh data table is V1201~V1400. The data table names are composed of the base data table name + iX, where iX = Int(Val(Right(sVehicleID, 4)-1) / 200), and sVehicleID is the vehicle model field.

2. The database design method for recording data of multiple parameters and multiple vehicle models according to claim 1, characterized in that, The data table generation method includes: Create a parameter number field F_ID, including: Create a record parameter content data table with parameter encoding field; Create a record with parameters for the user's data table and the corresponding code field. When creating a record, fill in the date data table parameter encoding field; Create a record by filling in the status data table parameter encoding field; Create a record parameter approver data table with parameter code field; Create a record with parameters, approval date, and a data table containing parameter encoding fields. Create the vehicle type field sVehicleID, including: The values ​​of iX are sequentially taken from 0 to conTableNum, and the values ​​of j are sequentially taken from 1 to 200. The above steps are repeated based on different values ​​of iX and j until all data tables are generated. Here, conTableNum is the number of data tables, and sVehicleID is any number from V0001 to V9999. The vehicle model field is derived from iX and j. ; The new record parameter content data table includes the vehicle model number field; Add a new record parameter: the vehicle model number field in the data table for the person who entered the parameter. When adding a new record, fill in the date data table's vehicle model number field as the parameter. Add new record parameters by filling in the vehicle model number field of the status data table; Add a new record parameter, the vehicle model number field, to the reviewer data table; Add a new record parameter: Review Date data table vehicle model number field; j loop; iX loop; When a new vehicle model is created, the name of the data storage file is calculated based on the vehicle model number. If the file record is empty, the parameter number filling method is executed to fill in the parameter number.

3. The database design method for recording data of multiple parameters and multiple vehicle models according to claim 1, characterized in that: It also includes designing data table models for all users to write data in the program database RHN.mdb, and automatically creating a series of data tables named after the user's login name strUserCode + model data table name when the user logs in, and copying the model data structure into the series of created data tables.

4. The database design method for recording data of multiple parameters and multiple vehicle models according to claim 2, characterized in that, The parameter number filling method includes: Store the parameter record table name using the subscript variable Dat(i); Use data records to define a summary table of variable parameters; The loop variable i takes values ​​from 0 to 7 in sequence; Move the parameter summary table to the initial record; Define the data record variable as the newly added record; Write the parameter codes from the parameter summary table into the parameter record table; The parameter record table records variable updates; The parameter summary table records the variable to the next record; Repeat the above steps until the last record in the parameter summary table is entered, then the loop ends. The loop variable i loops; Close the parameter record table to record variables.

5. The database design method for recording data of multiple parameters and multiple vehicle models according to claim 1, characterized in that, This also includes linking the data tables in the data file A_DATA.mdb in the program file RHN.mdb, and performing the following associations: Associate the responsible department, associated module, field name, parameter priority, parameter content, data entry person, input date, record number of the corresponding module, data reviewer, review date, and data status with the corresponding parameter table.

6. The database design method for recording data of multiple parameters and multiple vehicle models according to claim 5, characterized in that, This also includes generating a master data table A1 for the selected vehicle model (sVehicleID), using the following methods: Copy the parameter data of the selected vehicle model to the temporary data table strUsercode & "_TEMP; Based on the relationships between the parameter tables, a Structured Query Language (SQL) table is generated to obtain the combined data table strUserCode & "_A1".

7. The database design method for recording data of multiple parameters and multiple vehicle models according to claim 6, characterized in that, It also includes searching based on the Structured Query Language (SQL), and the search method includes: The search interface generates Structured Query Language (SQL); Retrieve the field names Field1, Field2, Field3… from the SQL query; Create a data table OrientTable with vehicle number V_ID, Field1, Field2, Field3… and copy an empty table NewTable; Add the vehicle model numbers from the selected vehicle model list to the created data table OrientTable; Open the parameter value record table DATATOTAL+iX, search for Field1, Field2, Field3... corresponding to the selected vehicle model, and write the data to the OrientTable to complete the field record transposition; Execute an SQL query on the OrientTable data table and store the query results in an empty table NewTable.

Images