Creating Relational Indexes in OpenInsight 10

To create any type of Index in OpenInsight 10 you will need to view the Database Tool Panel.  From the OpenInsight 10 IDE, go to View, Tool Panels, Database.


Click on the table and right click for the menu. Choose Add Relational.


The How and Why regarding Relational Indexes

Our suggested best practice is NOT to embed the relational in the data table, but rather to make a table called MYTABLE_REL whose key is the same as the data table.  Why are we suggesting this?  Because MYTABLE_REL will never be locked by a user and the relational updates will always succeed.  Also long lists of keys in a record will not skew the hashing on the primary table. With this method, you can run clear-table on the relational table prior to rebuilding the index.  This provides the ability to have a table related to itself without getting confused.

In our example below, "this breach" is related to these "other breaches".  We will add a symbolic in the data table with an Xlate to the relational index, then use that in any other calculated columns which depend on the index.  The slight reduction in performance is far outweighed by the benefits.

Relational indexes typically have a "one side" and a "many side". When building the index, the relationship is from the many side to the one side.  In the example below there will be many communications for one breach, so the "one side" is DATA_BREACH and the "many side" is DB_COMMUNICATION.

Given a table named DATA_BREACH and a table named DB_COMMUNICATION, we will create a table named DATA_BREACH_REL with the same key as DATA_BREACH.

DATA_BREACH table

DB_COMMUNICATION table

DATA_BREACH_REL table

To add our relational index we will start with the "many side" table, in this case DB_COMMUNICATION.

From the Database Tool Panel, right click on DB_COMMUNICATION and choose Add Relational.  The Add Relational Index dialog box will display.  The Index FROM side is the "many side", therefore the Tablename is: DB_COMMUNICATION.  The FROM Column Name is the column which contains the key of the "one side" table DATA_BREACH, in this case: DB_ID.

The Index TO table is: DATA_BREACH_REL.  The Index TO Column Name is the multivalue which holds the keys for this relation: DB_COMMUNICATIONS.

Choose the Sort Option: Append to End.

Click the “Add” button, OpenInsight will build the relational index.

Using the Table Designer, add a calculated column to the "one side" table DATA_BREACH to Xlate the keys from DATA_BREACH_REL.  In this example it is called: DB_COMMUNICATIONS

You can now build other calculated columns driven by the calculated column DB_COMMUNICATIONS.

Revelation Conference

Revelation Software does not have a Conference scheduled for 2019. Click the button below for our previous Conference web site.

Click Here

On The Road

Throughout the year Revelation Software attends various multivalue User Conferences as well as our Regional User Group meetings.

Click Here

International Offices

Revelation Software has various International Offices and sales regions located on multiple continents.

Click Here

Featured Clients

Revelation Software's clients are involved in every industry around the world. Here is a sampling of some of our clients and their businesses

Click Here