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 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, so that all of our other symbolics think the relational is part of the main table.  The slight reduction in performance is far outweighed by the benefits.

Typically relationals have a "one side" and a "many side".  In the example below, 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


DATA_BREACH_REL


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", therfore the Tablename is: DB_COMMUNICATION.  The FROM Column Name 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.


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


Other calculated columns can be built off the calculated column 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