Efficient expansion method and system of relational table in database

Abstract

The present invention discloses a method and system for efficiently extending a relational table in a database to enable storing and exchanging data that does not conform to a standard defined domain data model to support multiple enterprise applications and systems with different requirements. Each application program can dynamically extend the meaning of a standard defined field by a value or a value and a data type, or dynamically add a new field in a standard defined domain relational table. These extensions enable the standard defined domain data model to meet enterprise application programs with different requirements and support system variability among various systems, and reduce the complexity of integration and interoperability.

Description

Technical Field

[0001] This invention relates to the field of relational databases, and to methods and systems capable of effectively processing extended standard-defined data models in relational databases. Background Technology

[0002] In relational databases, relational schemas are created based on universally accepted common data models that can be used by multiple systems and organizations. Sometimes, relational schemas are defined by Standard Definition Organizations (SDOs). We refer to predefined relational schemas as standard definitions or simple standards. Using standard-defined relational schemas reduces the complexity of system integration and interoperability. However, predefined schemas limit flexibility and often become a problem in many cases because the requirements of multiple systems and applications across multiple organizations are diverse, and it also increases deployment risk due to unexpected needs. One way to address this problem is to allow the flexibility to extend the standard-defined domain schema at runtime. This is particularly useful in distributed environments where standard-defined data tables or fields can be extended to meet the requirements of different applications. In a standard-defined relational schema with extension flexibility, standard-defined tables can add one or more new fields as needed to store new content; one application can interpret or translate one or more defined fields differently from other applications; one application can introduce one or more additional fields and make them available to other applications. An existing technical solution is to include all extensions in the domain schema, but this solution results in domain tables becoming very large and cumbersome, and inefficient to use and manage. Furthermore, it is essentially no different from a standard-defined schema; it's simply one with many additional fields and tables that may be used only occasionally or not at all, and it doesn't allow for dynamic expansion when unexpected additions are needed. This invention provides a system and method to solve this difficulty in an efficient and effective manner. Summary of the Invention

[0003] This invention discloses a method and system for extending standard-defined domain relation tables in a database, enabling the storage and exchange of data, including domain data models that do not conform to standard definitions, to support multiple enterprise applications and systems with different application requirements. Each application can dynamically extend the meaning of standard-defined fields by value or value and data type, or add additional fields to the standard-defined relation tables. Each defined table can add one or more additional fields that are not part of the default defined fields, and each defined field in the table can be modified to have a different meaning. Each application can read, write, store, and exchange valid extensions. The new meaning of the extension of a standard-defined field or additional field is defined by an Extension Definition (ED), which is stored in an Extension Definition Table (EDT). Each extension references an extension definition entry in the EDT using a reference or Uniform Resource Locator (URL). Extensions are created by one application and made available to other applications. These extensions enable standard-defined data models to support applications with different requirements and system variability across various organizations, and reduce the complexity of integration and interoperability.

[0004] In this invention, the unit of change to a defined domain relational table is called an extension. An extension includes value elements, data type elements, and the meaning of the change (extension definition). A relational database system includes one or more standard-defined domain relational tables that interact with multiple enterprise applications. Each application can read extensions from the database, write new extension names to the database, and exchange extensions with other applications over a network. An extension created by an application can be used and understood by other applications using the database. Applications and the database can run in any environment, such as cloud, on-premises, centralized, or distributed. Communication between applications and the database can use any interface over any network. This invention provides new methods and systems for dynamically extending any standard-defined domain relational table in a relational database system, such as redefining the meaning of defining field columns of a table or adding new field columns to the defined table, and performing the extension during system runtime if necessary.

[0005] In one embodiment, three types of tables are created for any standard-defined domain data model, referred to as the first table, the second table, and the third table. The standard domain data model hierarchically consists of a master object, one or more primitive data type fields, one or more sub-objects, and one or more complex data type fields; a sub-object consists of one or more primitive data type fields, one or more sub-objects, and one or more complex data type fields. Tables have a table name or table identifier ID, which uniquely identifies a table in the database system. Fields have a field name or field identifier ID, which uniquely identifies a field in the table. Table names and table identifier IDs or field names and field identifier IDs are interchangeable. A hierarchical data model can be implemented using a hierarchical first table pattern: constructing a first table for the master object; constructing one or more first tables for one or more sub-objects; and constructing one or more first tables for complex data types. The underlying database system supports primitive data types. Each master first table has a primary key column, used to uniquely identify a row in the database system, and one or more fields. Each subclass or complex data type first table has a primary key column, a foreign key column, used to uniquely point to a row in its parent first table, and one or more fields. The foreign key value is the primary key value of a row in its parent first table. The complex data type first table is a specific first table. Compared to child first tables, in addition to the primary key column, foreign key column, and one or more fields, the complex data type first table also includes a path column, which identifies which first table owns the rows of this complex data type first table. The main first table and multiple child first tables use the same complex data type first table to store the values ​​of the complex data type.

[0006] The extensions of the first table are stored in the second table. The columns of the second table include a primary key column, a foreign key column, a path (PATH) column, a resource locator (URL) column, and a value (VALUE) column. The primary key specifies the row to uniquely identify it; the foreign key specifies the row to uniquely identify one or more rows in the first table; the PATH indicates which first table or which field of that first table is being extended; the PATH, in conjunction with the foreign key, associates the extension with a row in the first table containing the extended definition field or an additional field. The URL points to the row in the third table where the extension definition is stored. Extension definitions can be pre-stored or stored in the third table via the user interface. Extension definitions can be saved along with domain messages when those messages are saved. All first tables use the same second table and can be partitioned by their foreign key columns to improve query efficiency and performance. The second table can have a composite key index composed of the primary key and foreign key columns.

[0007] When the application reads domain records from the first table, it also reads each extension of the first table from the second table. By matching the primary key value of each first record with the first table name in the foreign key and path columns of the second table, it determines whether the extension is an additional field of the first table or an extension of a defined field of the first table. Therefore, the application constructs a domain message with the retrieved extensions and sends this message back to the requesting application as a response. When the application saves the domain message to the first table, it also needs to read each extension of each defined field or each additional field of the first table from the message and save it to the second table. If the extension definition does not exist in the third table, it adds the extension definition to the third table; the extension definition should be included in the domain message.

[0008] This invention includes an embodiment of a database system comprising a domain or standard-defined data model, which includes a collection of native and / or complex data types, and sub-data objects; one or more relational tables based on the domain or standard-defined data model, wherein each table has a set of fields and a primary key or a unique composite key, each field being a native data type, a complex data type, or a sub-data object, some or all of which have one or more extensions, the extensions redefining the default content of one or more fields or adding one or more additional fields to the records or relational tables; a data structure constructed using foreign keys to represent hierarchical relationships between the relational tables; one or more extension definition tables storing extension definitions; one or more extension tables for identifying records with extensions in one or more relational tables, and references to records in the extension definition tables, the records in the extension definition tables storing the definition of each extension field. In the database system, the extension definition tables and extension tables can be combined into a single table. Furthermore, complex data types can be relational tables associated with one or more other relational tables, and there can be second-level extension tables and extension definition tables defining extensions of the extension fields. Attached Figure Description

[0009] Figure 1 A block diagram of an enterprise system for interacting with a standard domain relational database and multiple enterprise applications;

[0010] Figure 2 Metadata of the standard domain relation tables in the database and the relational diagram of the first, second, and third tables;

[0011] Figure 3 According to Figure 2 Metadata for the first, second, and third tables, depicting the standard-defined domain relationship tables and example diagrams of the first, second, and third tables;

[0012] Figure 4 Metadata for complex data types of tables is described and added. Figure 2The standard definition of domain relation table metadata;

[0013] Figure 5 According to Figure 4 Metadata of complex data types, an example diagram of the first table describing the complex data type and the defined domain relationship table;

[0014] Figure 6 An extended data flow diagram for saving extended definition fields or additional fields to the main first and second tables;

[0015] Figure 7 A data flow diagram for saving extended or additional fields defined by extensions to sub- or complex data types in the first and second tables;

[0016] Figure 8 Retrieve extended data flow graphs of extended and additional fields from the first and second tables. Detailed Implementation

[0017] Please refer to the accompanying drawings, in which the same reference numerals denote the same parts. Exemplary embodiments of the invention will now be described. Exemplary embodiments are provided to illustrate various aspects of the invention and should not be construed as limiting the scope of the invention. When exemplary embodiments are described with reference to block diagrams or flowcharts, each block may represent a method step or apparatus or system element for performing the method steps. Depending on the implementation, the corresponding apparatus element may be configured in hardware, software, firmware, or a combination thereof.

[0018] This invention can be implemented in various database systems, including RDBMS database systems, and / or any big data or NoSQL database platform that supports relational models.

[0019] Figure 1A distributed enterprise system 100 is illustrated, comprising a standard relational database system 101 and domain relation tables 103 defined by domain relation table metadata 102 based on a standard domain data model. Domain relation table metadata 102 and domain relation tables 103 are stored in the database system. Multiple enterprise applications interact with the database system through industry-standard interfaces, where enterprise application 1 interacts with the database system using interface 120, enterprise application 2 interacts with the database system using interface 130, and enterprise application n interacts with the database system using interface 140. Each interface may utilize different industry technologies and methodologies. The standard-defined domain data model is typically an agreed-upon industry or community-wide model across systems and organizations, covering their broad needs. Enterprise applications may run within the same organization or across multiple organizations. Enterprise applications may be cloud-based or on-premises, centralized or distributed. Due to the diversity of application requirements across various organizations, each organization may have specific requirements that do not fall within the standard-defined domain data model. Each application may need to add different additional fields to the standard-defined domain data model and may need to extend the meaning of the defined fields of the standard-defined domain data model, sometimes requiring dynamic addition or extension during runtime. Any extensions added to an enterprise application can be understood and used by other enterprise applications. In large-scale connected industry environments or communities, the number and variety of applications can be enormous, requiring a wide variety of domain relationship tables. Therefore, standard-defined domain relationship tables accelerate the deployment process, while their scalability allows for dynamic customization, further simplifying the complexities of integration and interoperability.

[0020] In the standard domain data model, each domain has a master object, a master object can have one or more child objects, a child object can have one or more child objects, and a parent object. Complex data types are specific child objects that can contain one or more parent and child objects. The master object, child objects, and complex data types form the FirstTables. Figure 2 This shows an example of domain relation table metadata defined in the standard. Table 200 is... Figure 3 The main first table (300) contains metadata, and each row (200) defines the metadata. Figure 3 In the main table 300, for example, the 200_PK column in the field name column of table 200 defines... Figure 3 In the main table 300, the primary key column 200_PK; the A100 column in the field name column defines... Figure 3 In the main table 300, the first field column A100 has a data type of D100; the field name column A200 defines... Figure 3In the main table 300, the first field A200 has a data type of D200. 200_PK is... Figure 3 The primary key column of table 300 is the unique identifier for each record; it is typically an integer data type. Data types D100 and D200 are both native data types supported by the underlying database system. Table 201 is... Figure 3 The metadata of the first table 301 in table 201, where the field name column 200_FK defines... Figure 3 The foreign key column 200_FK in table 301 is linked to the primary key column 200_PK in table 300, which is defined by the field name column 200_PK in table 200, as follows: Figure 2 As shown by arrow 233, the definitions of A100 and A200 in the field name column are... Figure 3 Table 301 contains columns A100 and A200. The data type of A100 is defined as D100 in the data type column of Table 201; the data type of A200 is defined as D200 in the data type column of Table 201. Equivalently, Table 20N is... Figure 3 The metadata of the first table 30N. The first table 20N is related to its parent first table using its foreign key 20N-1_FK, such as... Figure 2 As shown by arrow 233.

[0021] Table 220 is the definition Figure 3 The metadata for Table 220 is the second table in the table. This second table is an extension table that stores extended information. Table 220 metadata may contain... Figure 3 The second table, 320, has the following definitions: a primary key column 220_PK, a foreign key column 2XX_FK, PATH (path), URL (Uniform Resource Locator), and VALUE (value). The VALUE column stores the actual data value and can reference any data type supported by the underlying database, depending on the extended definition. Typically, VALUE is stored as a BLOB (Binary Large Object), CLOB (Character Large Object), or VARCHAR (Variable Length Character) data type. Values ​​of any data type are written to or read from these data types through serialization or deserialization. The URL column points to... Figure 3 The third table, entry 330, contains the extended definition. The foreign key column 2XX_FK refers to one of the primary keys in the first table, such as... Figure 2As shown by arrows 211, 244, and 255. The combined PATH and foreign key column 2XX_FK determine which first table the foreign key column 2XX_FK refers to. The path column represents the hierarchical data structure and can consist of a sequence of elements, including the main object name, child object names, and field names of the data structure separated by a delimiter, typically a dot ".". The root of the path always represents the main object. The name element furthest from the root can be a field name or an object name; if it is an object name, it indicates that the extension is an additional field of that object in the first table; if it is a field name, it indicates that the extension is an extended field of an already defined field of that object in the first table, identified by the second element name furthest from the root. The path is expressed as follows:

[0022] PATH = [Name of Primary Object First Table]{.[Name of Child Object First Table]*}{.[Field Name of Primary Object or Child Object]}?, where '*' indicates that it appears zero or more times; '?' indicates that it occurs zero or once.

[0023] For example:

[0024]

[0025] Figure 2 Table 230 is the definition Figure 3 The metadata for Table 330 in the table is as follows. Table 3 is the Extended Definition Table (EDT), which stores extended definitions. The EDT may include the primary key column 230_PK, a definition column (DEFINITION), and a description column (DESCRIPTION). Both the DEFINITION and DESCRIPTION columns are of data type VARCHAR (variable-length character). DEFINITION includes the extended metadata and the meaning of the extension. All enterprise applications generally agree on and are compatible with this format, such as JSON, XML, or a DSL (domain-specific language), or referencing or pointing to the fourth table, the field definition table. The metadata for DEFINITION may include field names, data types, sizes, minimum values, maximum values, descriptions, constraints, and user-defined attributes. Figure 3 The URL in column 320 of the second table points to Figure 3 The primary key column 230_PK in the third table 330, such as Figure 3 As shown by the middle arrow line 222. Figure 3 The entries in the third table 330 can be pre-populated using an industry-standard interface, or can be dynamically saved during runtime via messages passed in by enterprise applications.

[0026] Figure 3 It shows that according to Figure 2 This is an example of a domain relationship table constructed from the metadata of the first, second, and third tables. Tables 300, 301, and 30N in the first table store relationship value records. Table 320 stores each extended instance of the first table, and table 330 stores the meaning of each extended instance of table 320. (Example...) Figure 3 As shown, Table 300 has two records with primary key values ​​200_Id1 and 200_Id2 respectively in the primary key column 200_PK. Record 200_Id1 has two field values, a100_v1 and a200_v1; record 200_Id1 in Table 300 is associated with record 201_Id1 in Table 301; record 201_Id1 in Table 301 contains two field values, a100_v1 and a200_v1. Record 200_Id2 in Table 300 has two field values, a100_v2 and a200_v2; record 200_Id2 in Table 300 is associated with record 201_Id2 in Table 301; record 201_Id2 in Table 301 contains two field values, a100_v2 and a200_v2.

[0027] In the first row of the second table 320, record 220_Id1 has PATH 300.A100, which indicates that field A100 in the first table 300 is an extended field and that its foreign key 2XX_FK value 200_Id1 is associated with record 200_Id1 in the first row of the first table 300. Therefore, the new value of field A100 in record 200_Id1 of the first table 300 is a100_v3, the description of which refers to its URL value defined by record 230_Id1 in the first row of the third table 330.

[0028] In the second row of the second table 320, the second record 220_Id2 contains PATH 300, which indicates that the first table 300 has an additional field, and also indicates that its foreign key 2XX_FK value 200_Id2 is associated with record 200_Id2 in the second row of the first table 300. Therefore, the value of the new field is a300_v1, and its field name and data type are defined by record 230_Id2 in the second row of the third table 330 by referencing its URL value 230_Id2.

[0029] In the third row of Table 320, record 220_Id3 has a PATH value of 300.301.A200, which indicates that field A200 in Table 301 is an extended field, and that its foreign key 2XX_FK value 201_Id2 points to record 201_Id2 in the second row of Table 301, and that Table 301 is a sub-table of Table 300. As a result, the new value of field A200 in record 201_Id2 of Table 301 is a200_v3, the description of which is defined by referencing its URL value from record 230_Id2 in the second row of Table 330. The two extended values ​​220_Id2 and 220_Id3 in Table 320 refer to the same extended definition in Table 330.

[0030] Figure 4 It is achieved by adding complex data types. Figure 2 Metadata graph of complex data types in domain relation tables. Metadata definition of complex type tables (400) is as follows. Figure 5 The first table shown is 500 and consists of multiple columns, such as the primary key column 400_PK, the foreign key column 2XX_FK, the path column ATH, and one or more field columns, such as fields A100 and A200. 400_PK is the primary key column of the first table 400; it is a unique identifier for each record and is typically an integer data type. Field A100 in the first table 400 is of data type D100, and field A200 in the first table 400 is of data type D200. Both D100 and D200 in the first table 400 are native data types supported by the database system. Each first table can be associated with one or more complex type tables. The first table is associated with a complex type table using its foreign key 2XX_FK. 2XX_FK can be the primary key of any other first table. PATH indicates which first table is associated with the complex type table 400 and what the first field name is. PATH includes the first table name and the first field name of the first table, separated by a delimiter, usually a dot ".". The root of the path always represents the first table. The path is expressed as follows:

[0031] PATH=[Name of First Table].[Name of Field]

[0032] For example:

[0033]

[0034] The main object and child objects, based on the standard-defined domain model, can have one or more different complex data type objects, and these complex data type objects can contain one or more other complex data types. In other words, the main first table and the child first table can be associated with one or more complex type first tables. Complex type first tables can also be extended by redefining the meaning of defined fields or adding other fields.

[0035] Figure 5 It shows that according to Figure 4 This is an example of a complex type first table with a master and child first table, representing the metadata of complex data types. As shown, the complex type first table 500 contains one or more records; for example, two records are identified by primary key values ​​400_Id1 and 400_Id2, respectively. Record 400_Id1 in the first row is associated with record 200_Id1 in first table 300 by referencing its PATH value 300.A300 and foreign key column 2XX_FK value 200_Id1; the complex type field name in first table 300 is A300; in the first row of 500, record 400_Id1 has values ​​a100_v1 and a200_v1 in A100 and A200, respectively; by referencing second table 320 and using... Figure 3 Using the same method described above, arrow 533 indicates that field A100 of record 400_Id1 is expanded, and its new value is referenced in the second table; by referring to PATH 300.301.A400 in the second row of 500, arrow 544 indicates that a new field A300 is added to record 400_Id2. For details on the new field's data type, usage, and description, refer to the second table 320.

[0036] Figure 6 This is the first part of a data flow diagram illustrating how various extensions are stored together with domain messages. Figure 6 The data flow demonstrates how to handle the expansion of the master object. Step 601 initiates saving the domain message to... Figure 2 and Figure 3 The domain relationship table is shown. Step 602 saves the master object to... Figure 3 The first table in the table is 300. Process 603 processes the main object to determine if it contains additional fields; if so, process 603 proceeds to process 620; otherwise, it proceeds to decision 604. For each additional field in the main object, decision 620 checks if the field's extended definition exists, such as... Figure 3 If the URL points to the third table 330, then the process moves from decision 620 to block processing 622; otherwise, it indicates a new extension definition, and step 621 saves the new extension definition to... Figure 3The third table 330. Step 622 creates the PATH value by setting the name of the primary first table to PATH: for example, PATH = 300. And step 623 adds the VALUE, URL, and PATH fields of the additional fields along with the primary key of the primary first record to the second table 320, as follows. Figure 3 As shown. In step 624, it is determined whether the main object has more additional fields; if so, step 624 returns to step 620 and repeats; otherwise, the process proceeds to the next step 604. In step 604, it is determined whether the main object contains any extended fields; if step 604 returns no, the process proceeds to the next step 605 for further processing, see [link to step 605]. Figure 7 Otherwise, proceed to step 630.

[0037] For each extended field of the main object Figure 3 Decision step 630 in the process uses the URL field for evaluation Figure 3 Does the third table 330 shown have an extended definition for its fields? If the evaluation result is yes, the process moves from process 630 to process 632; if the evaluation result is no, it indicates that this is a new extended definition, and step 631 saves the new extended definition to... Figure 3 In the third table 330 shown; step 632 creates the PATH value by setting the main first table name and the extended field name to PATH: for example, PATH = 300.A100, where A100 is as follows Figure 3 The field names defined in the first main table 300 are shown. Step 633 saves the VALUE, URL, and PATH of the extended fields along with the primary key of the first main record to the second table 320, as shown. Figure 3 As shown. In step 634, it is determined whether the main object has more extended fields; if the result is yes, then return to step 630 and repeat; otherwise, the process proceeds to the next step 605 to perform... Figure 7 Further processing as described in [the document].

[0038] Figure 7 This is the second part of a data flow diagram illustrating how various extensions are stored together with domain messages. Figure 7 This demonstrates how to handle extensions of sub-objects and complex data types. Step 701 initiates saving the data of sub-objects and complex data types to... Figure 3 and Figure 5 The domain relationship table described in [the document]. Step 702 tests whether the sub-object or complex data type value contains additional fields; if so, the process proceeds to step 750; otherwise, it proceeds to step 703. For each additional field extension of the sub-object and complex data type, step 750 is used to determine whether the extended definition of the field exists in the extended definition in the third table 330 pointed to by the URL, such as [example table]. Figure 3As shown; if yes, then process from step 750 to step 752; if not, it indicates a new extended definition, and step 751 saves the new extended definition in the third table 300; step 752 creates the PATH value by setting the main first table name and the sub-object name or complex data type name to PATH, for example,

[0039] PATH=300.301

[0040] PATH=300.301.500

[0041] Where 1) 300.301 indicates that the extension is Figure 3 1) An additional field in the child first table 301 of its parent first table 300; 2) 300.301.500 indicates that the extension is Figure 3 His father's first table 301 Figure 5 The additional fields in the first table 500 of the complex data type. Step 753 stores the VALUE, URL, and PATH of the additional fields together with the primary key of the child object or complex data type of the main first record in the second table 320. Step 754 determines whether there are any other additional fields in the child object or complex data type object; if so, step 754 can be traced back to step 750 and repeated; otherwise, the process proceeds to the next step 703.

[0042] Step 703 tests whether the sub-object or complex data type object contains any extended fields; if not, the process moves to step 704; otherwise, it moves to step 760. Step 704 determines whether the main object contains more sub-objects and complex data type objects; if it has more sub-objects or complex data type data, the process returns to step 701 to repeat; otherwise, the process proceeds to step 705 and the domain message processing ends.

[0043] Step 760 uses the URL to determine if the extended definition of the extended field is in, for example... Figure 3 The extended definition shown has an entry in the third table 330; if so, proceed from step 760 to step 762; if not, it indicates a new extended definition, and step 761 stores the new extended definition in the third table 330, as shown. Figure 3 As shown. Step 762 creates the PATH value by setting the primary first table name, the child object name or complex data type name, and the extended field name to PATH: for example...

[0044] PATH=300.301.A100

[0045] PATH=300.400.A200

[0046] PATH = 300.301.400.A300

[0047] Among them, 1)A100 is Figure 3 1) An extended field of the first child table 301 of the parent first table 300; 2) A200 is Figure 5 The extended field of the first table (500) is a complex data type, where the parent table of the first table is... Figure 3 300 in; 3) A300 is Figure 3 The extended fields of the composite type first table 500 in the parent first table 301. Step 763 saves the VALUE, URL, and PATH of the additional fields along with the child object or complex type of the parent first record of the child or complex type first table to the second table 320, such as... Figure 3 As shown. Finally, step 764 tests whether there are more extended fields in the sub-object and the complex data type object; if so, the process returns to step 760 and repeats; otherwise, the process moves to step 705 and ends.

[0048] Figure 8 This is a flowchart illustrating how to retrieve various extensions and domain message processing. Step 801 begins by reading domain records from the first table based on user-defined search criteria. Then, for each domain record, step 802 uses the primary key of the first record to retrieve... Figure 3 The second table 301 (Extension Table) retrieves any extensions. For each retrieved extension, step 803 retrieves the extension definition from the third table 330 (Extension Definition Table); step 804 uses PATH to determine whether the extension is an extended field or an additional field of the main object: for example,

[0049] PATH=300

[0050] PATH=300.A100

[0051] 1) PATH=300 indicates that the extension is Figure 3 1) Additional field 300 in the first table of the main table; 2) PATH=300.A100 indicates that the extension is Figure 3 The extended field A100 of the main first table 300.

[0052] Step 805 determines if there are any more extensions. If no more extensions are retrieved, processing proceeds to the next step, 806; otherwise, it returns to step 803 for iteration. Step 806 uses the primary key of each first record in the sub-first table or the composite type first table, or from... Figure 3 Any extensions are retrieved from the second table 320 (Extension Table). For each retrieved extension, step 807 retrieves the extension definition from the third table 330 (Extension Definition Table), and step 808 uses PATH to determine whether the extension is a sub- or complex type of the first table's extension field, or additional fields, for example...

[0053] PATH=300.301

[0054] PATH=300.301.A100

[0055] PATH=300.500

[0056] PATH=300.500.A200

[0057] 1) PATH = 300.301 indicates that the extension is its Figure 3 1) The additional field of the child first table 301 of its parent first table 300; 2) PATH = 300.301.A100 indicates that this extension is its Figure 3 The extended field A100 of the first child table 301 of the parent first table 300; 3) PATH = 300.500 indicates that the extension is Figure 3 The first table of his father's 300 Figure 5 4) PATH = 300.500.A200 indicates that the extension is the extension field A200 of the child first table 500 of its parent first table 300.

[0058] Step 809 determines if there are any more extensions. If no more extensions are retrieved, processing moves to step 810; otherwise, it moves to step 807 and repeats. Step 810 tests if there are any more retrieved domain records. If there are, processing returns to step 802 to process the more domain records; otherwise, processing ends.

[0059] The above embodiments illustrate the elements of the present invention. It can be further generalized by adding more fields and tables, combining fields and tables, combining or using multi-level pointers or links, embedding another level of the data model into a standard-defined data model, etc. Methods and systems for extending relational databases are disclosed, including: creating one or more first tables, each table including a unique table identifier ID, multiple first records, each first record including a primary key, and one or more first fields, some or all of which have one or more extensions that redefine the meaning and / or data type of the first field, differing from the default or standard definition meaning and / or data type of the first field; creating one or more second tables with one or more records, each record having one or more path fields, each path field consisting of a table identifier ID, a field identifier ID, and a primary key to identify the first record with the extended first field, and a value field; if the extension defines a data type different from the default data type, the value field stores the value of the extended data type of the first field; if the extension defines a meaning different from the default meaning of the first field, the value field stores the extended meaning of the first field; retrieving the extended first field, using one or more PATH fields in the second table to identify the first field, and retrieving the value of the first field from the VALUE field in the second table if the data type of the first field is extended; and retrieving the meaning of the first field from the meaning field in the second table if the first field has an extended meaning.

[0060] Furthermore, the meaning field can be a pointer field pointing to a record in a third table; one or more third tables can be created with one or more records, each record storing an extended meaning of a first field identified by one or more path fields in a second table; and the meaning of the extended first field can be retrieved by using one or more path fields in the second table to identify the first field, retrieving the corresponding pointer in the second table, and using the pointer to retrieve the meaning of the first field from the third table.

[0061] The steps for updating or writing records or extended fields are similar, involving updating or writing the path, value, and meaning fields of the first field in the first table (or, if a separate table is used, the third table) in the second table. For example, updating a first field with extensions involves using one or more PATH fields in the second table to identify the first field, and updating the value of the extended first field in the VALUE field of the second table. The second and third tables can also be combined into a single table. In some cases, one second table is sufficient, but in others, when a second table becomes too large, it may be split into multiple second tables to improve performance. Additionally, second-level second and third tables can be used to add second-level extended fields to extended first fields defined in the second and third tables.

[0062] Methods and systems for extending relational databases also include creating one or more first tables, each including a unique table identifier ID, multiple first records, each first record including a primary key, and one or more first fields, and extending some or all of the first records of one or more first tables. Extension is achieved by adding one or more additional first fields, which are not stored in the first records; creating one or more second tables with one or more records, each second table record having one or more path fields storing the table identifier ID and the primary key to identify the first record with one or more additional fields, one or more value fields, each value field storing the value of the additional first field of the first record, and a meaning field defining the meaning of the additional first fields; retrieving the additional first fields of the first record, including using one or more path fields in the second tables to identify the first record, retrieving the value of the additional first field from the value fields in the second tables, and retrieving the additional meaning of the first field from the meaning fields in the second tables.

[0063] Furthermore, the meaning field can be a pointer field to a record in a third table; one or more third tables can be created with one or more records, each record storing the meaning of an additional first field of a first record identified by one or more path fields in a second table; and retrieving the meaning of the additional first field of a first record includes using one or more path fields in a second table to identify the first record, retrieving the corresponding pointer in the second table, and using the pointer to retrieve the meaning of the additional first field from the third table referenced by the pointer.

[0064] The steps for updating or writing records or appended fields are similar, involving updating or writing to a second table and a third table. If a separate third table is used, the path, value, and meaning fields of the first field in the first table are also updated. For example, updating a first record extended by an appended first field involves using one or more path fields in the second table to identify the first record, and updating the value of the appended first field of the first record in the value field of the second table.

[0065] The second and third tables can also be combined into a single table. In some cases, a single second table is sufficient, but in others, when a second table becomes too large, it may be split into multiple second tables to improve performance. Additionally, the second and third tables can be used to add second-level supplementary fields to supplementary first fields defined in the second and third tables.

[0066] Although a preferred embodiment of the invention has been described above, outlining or illustrating the essential innovative features or principles of the invention, it should be understood that various omissions, substitutions, and variations in detail are possible. Those skilled in the art can make the methods, elements, or apparatuses shown and their uses without departing from the spirit of the invention. Therefore, the scope of the invention should not be limited to the foregoing description. Rather, the principles of the invention can be applied to a wide range of methods, systems, and apparatuses to achieve the advantages described herein and to realize other advantages or to fulfill other objectives.

Claims

1. A highly efficient system for extending relational tables in a database, characterized in that, include: A domain or standard-defined data model, including a collection of native and / or complex data types, and sub-data objects; One or more relational tables based on the data model defined by the domain or the standard, wherein each relational table has a set of fields and a primary key or a unique composite key, wherein each field is a primitive data type or a complex data type or a sub-data object, wherein some or all records of the relational table have one or more extensions, which redefine the default content of one or more fields or add one or more additional fields to the records or relational table. Foreign keys are used to construct a data structure to represent hierarchical relationships between multiple relation tables; One or more extension definition tables store extension definitions; and One or more extension tables are used to store extension records of one or more extended relation tables, as well as references to extension definition records in the extension definition tables; One approach uses foreign keys to construct a data structure to represent hierarchical relationships between multiple relation tables, including: Use the combined path column and foreign key column to determine the relation table pointed to by the foreign key column; The path column represents a hierarchical data structure, consisting of a sequence of elements including the name of the main object, the names of the child objects, and the field names of the data structure separated by delimiters. The root of the path always represents the main object, and the name element furthest from the root is either a field name or an object name. If it is an object name, it means that the extension is an additional field of that object in the relation table; if it is a field name, it means that the extension is an extension field of an existing field of that object in the relation table, and the existing field is identified by the name of the second element furthest from the root.

2. The efficient extension system for extended database relational tables as described in claim 1, wherein the extended definition table and the extended table are combined into a single table.

3. The efficient extension system for extended database relational tables as described in claim 1, wherein the complex data type is a relational table associated with one or more other relational tables.

4. The efficient extension system for extended database relation tables as described in claim 1 includes a second-level extension table and an extension definition table, wherein the extension definition table defines the extension of the extension fields.

5. A method for extending a relational database, applied to an efficient extension system for extending relational tables in a database as described in claim 1, characterized in that, include: Create one or more first tables, each table including a unique table identifier ID, multiple first records and each first record including a primary key, and one or more first fields, and some or all of the first fields have one or more extensions, the extensions redefine the meaning and / or data type of the first field, and the extended first field has a different meaning and / or data type than the default or standard definition of its first field; Create one or more second tables with one or more records, each record having one or more path fields and value fields. The path field stores the identifier ID, field identifier ID, and primary key of the first table to identify the first record with the first field extended. If the extended field defines a data type different from the default data type, the value of the extended data type of the first field is stored in the value field and / or if the extended field defines a new meaning for the first field, the new meaning is defined in the meaning field. and Retrieving a first field with extensions involves identifying the first field using one or more path fields in the second table, retrieving the value data type of the first field from the value field in the second table if the first field has extensions, or retrieving the meaning of the first field from the meaning field in the second table if the first field has extended meanings.

6. The method for extending a relational database as claimed in claim 5, further comprising updating a first field having been extended, the extension including identifying the first field using one or more path fields in a second table, and updating the value of the extended first field by updating the value of a value field in the second table.

7. The method for extending a relational database as described in claim 5, wherein the meaning field is a pointer field pointing to a record in a third table; and further comprising... Create one or more third tables with one or more records to store the extended meaning of the first field identified by one or more path fields in the second table; and Retrieving the meaning of the extended first field involves identifying the first field using one or more path fields in the second table, retrieving the corresponding pointer in the second table, and using the pointer to retrieve the meaning of the first field from the third table referenced by the pointer.

8. The method for expanding a relational database as described in claim 7, wherein the second table and the third table are combined into a single table.

9. The method for extending a relational database as described in claim 7, further comprising using a second-level second table and a third table, wherein the second-level extended fields are added to the extended first fields defined in the second table and the third table.

10. An efficient method for extending a relational database, applied to the efficient extension system for extending relational tables in a database as described in claim 1, characterized in that, include: Create one or more first tables, each table including a unique table identifier ID, multiple first records, each first record including a primary key, and one or more first fields, and some or all of the first records in one or more first tables can be extended by adding one or more additional first fields, which are not stored in the first records; Create one or more second tables with one or more records, each record having one or more path fields. The path fields store the table identifier ID and primary key. The path fields are used to identify the first record, which is expanded to have one or more additional fields, one or more more value fields, each value field storing the value of the additional first field of the first record, and the meaning field defining the meaning of the additional first field. and Retrieving the additional first field of the first record includes identifying the first record using one or more path fields in the second table, retrieving the value of the additional first field from the value field in the second table, and retrieving the meaning of the additional first field from the meaning field in the second table.

11. The efficient expansion method for expanding a relational database as described in claim 10, further comprising updating a first record expanded by an additional first field, including identifying the first record using one or more path fields in a second table, and updating the value of the additional first field of the first record in the value field of the second table.

12. The efficient expansion method for expanding a relational database as described in claim 10, wherein, The meaning field is a pointer field that points to a record in the third table; and also includes... Create one or more third tables with one or more records, each record storing the meaning of an additional first field of the first record identified by one or more path fields in the second table; and The meaning of retrieving the additional first field of the first record includes using one or more path fields in the second table to identify the first record, retrieving the corresponding pointer in the second table, and using the pointer to retrieve the meaning of the additional first field from the third table referenced by the pointer.

13. The efficient expansion method for expanding a relational database as described in claim 12, wherein the second table and the third table are combined into a single table.

14. The efficient expansion method for extending a relational database as described in claim 12 further includes using a second-level second table and a third table, which add additional fields of the second level to additional first fields defined in the second table and the third table.

Images