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.
