The OpenSearch console provides four methods for you to define an application schema. The OpenSearch Industry Algorithm Edition supports multiple tables, which facilitates your calls in complex business scenarios.
Rules of associating primary and secondary tables
Take note of the following rules when you manually create multiple tables to configure the schema of an application:
The ratio of primary table records to secondary table records must be N:1 or 1:1. The ratio of 1:N is not supported. This means that a primary table record cannot be associated with multiple secondary table records.
A secondary table can be associated with a primary table by associating the primary key of the secondary table with a foreign key of the primary table. The foreign key of the primary table can be associated only with the primary key of the secondary table.
A maximum of three levels of association are supported. A maximum of 10 secondary tables can be created for an application.
Only one primary table can be configured.
Primary and secondary tables | Association logic | Supported |
Table A (primary table), Table B | Table A (primary table) -> Table B | Yes |
Table A (primary table), Table B, Table C | Table A (primary table) -> Table B -> Table C | Yes |
Table A (primary table), Table B, Table C, Table D | Table A (primary table) -> Table B, Table A (primary table) -> Table C, Table A (primary table) -> Table D. No more than two levels of association are configured. No more than 10 secondary tables are created. | Yes |
Table A (primary table), Table B, Table C, Table D | Table A (primary table) -> Table B -> Table C -> Table D. More than two levels of association are configured. | No, except for exclusive applications, which support three levels of association. |
Table A (primary table), Table B | Table A (primary table) -> Table B, Table B -> Table A. Tables are associated in the form of the cyclical association. | No |
Table A (primary table), Table B, Table C, ..., Table K | Table A (primary table) -> Table B, Table A (primary table) -> Table C, Table A (primary table) -> Table D. No more than two levels of association are configured. More than 10 secondary tables are created. | No, except for exclusive applications, which support more than 10 secondary tables. |
Data models
Examples of association between primary and secondary tables
Usage notes
Fields can be mapped between tables only if the fields are of the same type. For example, if the primary key of a secondary table is of the INT type, the primary key can map only fields of the INT type in a primary table.
Offline tables are joined based on the following logic: During the indexing process, a LEFT JOIN operation is performed on a primary table and secondary tables to generate a wide table. The index is built based on the wide table for search services. Assume that the ratio of primary table records to secondary table records is N:1 and the value of N is large. When data records in the secondary tables are updated, the number of data records that need to be updated in the primary table is N times that in the secondary tables. This may cause an update delay for both the primary and secondary tables. In this case, we recommend that the value of N is not greater than 10.
After the JOIN operation, the number of rows in the generated wide table is the same as that in the primary table. Fields that are empty in the secondary tables are automatically filled with a default value in the wide table. The default value for fields of the INT type is 0. The default value for fields of the STRING type is null.
Best practice: Data synchronization latency caused by multi-table joins