Use the Relationships tab to define the traversal path for selecting data from tables referenced in an Access Definition. The traversal path determines the sequence in which a process visits each table. If editing or browsing database data, the information on this tab cannot be edited.
The Start Table and any reference tables listed on the Tables tab are always included in a process. However, in order to select related data from other tables listed in an Access Definition, you must define the traversal path.
The status of the relationship. Changes to the DB Alias or Creator ID for a table referenced in the Access Definition can affect the status of a relationship. Status is populated automatically and cannot be modified.
When you run a process using the Access Definition, a warning message advises you of New relationships. Although you may proceed despite the warning, it may be prudent to review the relationship usage list.
Select the check box to include a relationship in the process. Clear the check box to exclude the relationship. To use relationships with New status automatically, select the Use new relationships check box in the lower left corner of the dialog. Right-click the grid column to select or clear the check boxes for all listed relationships.
To edit a relationship, right-click a grid row and select Open Relationship from the shortcut menu.
To review the traversal path, select the Show Steps option from the Tools menu. For details, see Show Steps.
Maximum number of rows from the child table to be selected for a relationship (for example, five orders for each customer).
The type of relationship, indicated by DBMS name, Optim™, or Generic. You cannot modify this value.
Allows you to override the default method (scan or key lookup) for accessing the parent or child table for each relationship. A scan reads all rows in a table at one time; whereas a key lookup locates rows using a WHERE clause to search for primary or foreign key values. (Note that you should override the default method only if the statistical information in the process report indicates the default method is less efficient.) Specify:
To set a method for all parent or child tables at once, right‑click a Table Access cell and select Set All Default, Set All Force Key Lookup, or Set All Force Scan from the shortcut menu.
The maximum number of key lookups performed at one time for a table. Valid values are 1 through 100. By default, Optim looks up one key at a time.
Note that increasing the Key Lookup Limit may significantly improve performance. For example, if you specify 5 as the Key Lookup Limit and the key has a single column, 5 key values are searched in a single request to the DBMS.
To display the Key Lookup Limit dialog, right-click a Key Lookup Limit cell and select the Set All... command from the shortcut menu.
Use the Key Lookup Limit dialog to set all values in the parent or child tables at once.
Option to exclude rows from processing if their relationship has an empty value. Optim will not attempt to fetch related rows for this relationship. You can ignore relationships with values equal to null, blank, zero length, any numeric value you choose, or any combination of these options. This option is available if you select Advanced Options from the Options menu.
To use this option, in the Ignore Empty Relationship grid column, right-click the cell for a relationship and this menu displays:
Fully qualified name of the relationship. You cannot modify this value. A relationship name is in four parts: dbalias.creatorid.tablename.constraint.
This check box is selected by default. Use it to include all relationships with New status in a process.
If you clear this check box, new relationships must be selected manually to be included in a process.
The following examples use a simple database structure to explain how relationships are traversed using an Access Definition to select a subset of data. This database structure includes four tables: CUSTOMERS, ORDERS, DETAILS, and ITEMS. Based on the relationships between each pair of tables:
The Basic Traversal path is from parent to child as shown next:
The traversal path begins at the Start Table and proceeds through the data model, traversing relationships from parent to child. For example, if the Start Table is CUSTOMERS, the process traverses from ORDERS to DETAILS.
To select all the ORDERS, DETAILS, and ITEMS rows related to a specific set of CUSTOMERS rows:
To select all ORDERS rows for CUSTOMERS that placed specific ORDERS:
In this example, use Point and Shoot with the ORDERS table as the Start Table to select ORD2. The related CUSTOMERS row B has three related ORDERS rows, ORD1, ORD2, and ORD3. The rows are selected in the sequence shown:
Because you selected Option (2), the process selects additional ORDERS table rows for every parent row selected from the CUSTOMERS table.
In this example, orders are shipped only when all items are available. If one ordered item is out of stock, the order is not shipped to the customer. To identify orders that are outstanding because of an out-of-stock item, the set of selected data must include full details on all orders.
To obtain complete details, select data from the CUSTOMERS, ORDERS, DETAILS, and ITEMS tables:
ITEMS to DETAILS | ![]() |
STEPS Step 1–The ITEMS row with a quantity of zero is selected based on the selection criteria. |
---|---|---|
Step 2–The DETAILS rows containing the item are selected because of the parent to child traversal of relationship RID. | ||
The process selects ORDERS related to DETAILS, followed by CUSTOMERS related to ORDERS.
DETAILS to ORDERS | ![]() |
Step 3–The ORDERS that are related to these DETAILS rows are selected by traversing the relationship ROD from child to parent because Option (1) is selected. Step 4–The CUSTOMERS that placed these ORDERS are selected by traversing the relationship RCO from child to parent because Option (1) is selected. |
ORDERS to CUSTOMERS |
The process selects additional DETAILS related to these ORDERS.
ORDERS to DETAILS | ![]() |
Step 5–The additional DETAILS that are part of the ORDERS are selected because Option (2) is selected for the relationship between ORDERS and DETAILS. |
The process selects ITEMS for the additional DETAILS.
DETAILS to ITEMS | ![]() |
Step 6–The ITEMS for the additional DETAILS are selected because Option (1) is selected for the relationship between ITEMS and DETAILS. |
The process selects only the ORDERS for selected ITEMS. The process does not select additional ORDERS for selected CUSTOMERS because you did not select Option (2) for the relationship RCO between CUSTOMERS and ORDERS.
Some tables are parent to more than one child table. Options (1) and (2) apply only to each pair or tables that share a relationship. For example, if the process selects a row in the CUSTOMERS table because you selected Option (1) for the relationship between CUSTOMERS and ORDERS, then Option (2) applies only to related rows from the ORDERS table. To select data from other child tables, you must select Option (2) for those relationships.
In this example, the SHIP_TO and ORDERS tables are children of the CUSTOMERS table. To obtain the shipping information for specific orders:
The process begins as described in the next illustration:
ORDERS to CUSTOMERS to SHIP_TO | ![]() |
STEPS |
You can direct a process to cycle through (revisit) tables to select data. These traversal cycles depend on the options you specify for each relationship.
Extending Example 4 (Multiple Relationships) to select all related data about orders for an out-of-stock item, a traversal cycle results by selecting all items that have a quantity of zero. To obtain a complete set of orders:
The process performs the following steps to complete a traversal cycle:
In Example 6, the process starts and ends at the ITEMS table to complete one cycle, but a process can also include multiple traversal cycles.
Example 6 can be extended to show multiple, although unlikely, traversal cycles. After selecting ITEMS rows for additional items in Step 5, it is necessary to select ORDERS and DETAILS rows for these ITEMS. This is done by selecting Option (2) for the relationship RID.
After Step 5 in the example, the process traverses the relationship RID from parent to child to select the remaining DETAILS rows related to these additional ITEMS. Then the process repeats Steps 3, 4, and 5 to select additional ORDERS, the DETAILS rows related to those ORDERS, the ITEMS rows related to those DETAILS, and so on.
You can select data from tables that are related to each other by referential cycles. In a referential cycle, the process starts at one table and returns to it after traversing one or more relationships. (In contrast, for many databases, a process proceeds up or down through the hierarchy or the network and never returns to the Start Table.)
The following examples use two tables — DEPARTMENT and EMPLOYEE. The tables are defined as:
DEPARTMENT | EMPLOYEE | ||||
---|---|---|---|---|---|
DEPT_ID | DEPT_NAME | MGR_ID | EMP_ID | EMP_NAME | DEPT_ID |
The DEPT_ID is the primary key of the DEPARTMENT table; the EMP_ID is the primary key of the EMPLOYEE table. The relationships between the DEPARTMENT and EMPLOYEE tables are shown next:
The relationships used in the following examples are defined as:
Relationship | Parent Table.Column | Child Table.Column |
---|---|---|
MANAGER | EMPLOYEE.EMP_ID | DEPARTMENT.MGR_ID |
MEMBER | DEPARTMENT.DEPT_ID | EMPLOYEE.DEPT_ID |
The data in each table is as follows:
DEPT_ID | DEPT_NAME | MGR_ID |
---|---|---|
A | Executive | 1 |
B | Finance | 2 |
C | Sales | 3 |
D | Development | 4 |
E | Bookkeeping | 5 |
F | Support | 9 |
EMP_ID | EMP_NAME | DEPT_ID |
---|---|---|
1 | Fred | |
2 | Bill | A |
3 | John | A |
4 | Laurie | A |
5 | Bobbie | B |
6 | Don | B |
7 | Pat | C |
8 | Zack | C |
9 | Meghan | D |
10 | Tim | D |
11 | Jack | D |
12 | Diane | E |
13 | Dick | E |
14 | Jill | F |
15 | Melanie | F |
The following is a sample company organization chart:
In the next examples, a sample query demonstrates how to handle cyclic relationships by selecting Options (1) and (2) for different relationships. Changing these options affects the set of data selected from each table.
To select the names of all employees in the Development Department, including employees that are members of subordinate departments:
The process performs these steps:
When the process completes, two rows are selected from the DEPARTMENT table and five rows are selected from the EMPLOYEE table:
DEPARTMENT | EMPLOYEE |
---|---|
DEPT_ID='D' | EMP_ID = 9 |
DEPT_ID='F' | EMP_ID = 10 |
EMP_ID = 11 | |
EMP_ID = 14 | |
EMP_ID = 15 |
To select the manager of the Development Department in addition to all employees in the Development Department and subordinate departments (Example 7):
The process follows the steps described for Example 7. In addition, Step 4 traverses the relationship MANAGER from DEPARTMENT (child) to EMPLOYEE (parent), based on Option (1). As a result, the process selects MGR_ID = 4 to identify the manager of the Development Department (Laurie) and selects a row from the EMPLOYEE table, EMP_ID = 4 (Laurie).
To select any other departments this manager manages in addition to all employees in the Development Department and the manager of the Development Department:
The process follows the steps described for Example 8. In addition, after Laurie is selected from the EMPLOYEE table, the process follows the MANAGER relationship from EMPLOYEE (parent) to DEPARTMENT (child). The process traverses the DEPARTMENT table to see if Laurie is manager of any other department. Based on the employee hierarchy for these examples, no additional rows are selected. Laurie manages only the Development Department.
In addition to all employees in the Development Department, the manager of the Development Department, and any other departments under that manager, extend the example to select any other departments of which the manager of the Development Department is a member. To do this:
In this example, after Laurie is selected from the EMPLOYEE table, the process follows the MEMBER relationship from EMPLOYEE (child) to DEPARTMENT (parent) based on Option (1). The process traverses the DEPARTMENT table to see if Laurie is a member of any other department. Laurie is a member of the Executive Department. The process selects an additional row from the DEPARTMENT table, DEPT_ID = A.
In addition, because you selected Option (1) for the MANAGER relationship, the process traverses from DEPARTMENT (child) to EMPLOYEE (parent) to select an additional row from the EMPLOYEE table, EMP_ID = 1. (Fred is the manager of the Executive Department.)
In this example, select only employees that are members of the Development Department. Unlike Example 7, do not select employees in subordinate departments. This process disables the referential integrity (RI) cycle:
In this example, Step 3 disables the RI cycle. The DEPARTMENT row for the Development Department is selected. Then, using the relationship MEMBER, the process traverses from parent to child to select the three employees in the department. Because the MANAGER relationship is unselected, it is not traversed. The process is complete.