A relationship is a defined connection between the rows of two tables. This connection is generally determined by values in selected columns from a parent table that correspond to values in the child table. Optim uses relationships to determine the data to be retrieved from related tables and relies upon relationships defined to the database, when available. However, you can also define relationships to supplement those in the database.
The relationships you define are stored in the Optim™ Directory. Although you can define Optim relationships that conform to database management system requirements, much greater flexibility is possible. For example, some database management systems have the following requirements for relationships:
When defining Optim relationships, a number of the database restrictions are relaxed. For example:
The more flexible Optim relationships are called “extended” relationships. Extended relationships can replicate implicit or application-managed relationships in your database, allowing you to manipulate sets of relational data in the same manner as in your production environment.
In addition, an Optim relationship can be stored in the Optim Directory as:
Generic relationships are useful when several sets of tables differ only by Creator ID. (For example, in a test environment, each programmer may use a separate copy of the same production tables. Each set of tables can be distinguished by the Creator ID.) Using generic relationships, you define one set of relationships that applies to all sets of tables. Also, when a set of these tables is added, the generic relationships automatically apply.
Although primary keys are not required to define relationships, they do make it easier. When you define a relationship using a parent table that has a primary key, the names of primary key columns are automatically inserted into the Relationship Editor, which also displays the names of matching columns in the child table. You can then edit the column expressions, as needed.
If a parent table does not have a primary key, a blank line is inserted into the Relationship Editor for you to enter column names for the parent and child tables, as required.
The name of a relationship is: dbalias.creatorid.tablename.constraint. This name consists of the fully qualified name of the child table suffixed with the constraint assigned to the relationship. It is helpful to use a consistent convention for naming relationships.
This section explains how to create and maintain explicit and generic relationships defined to the Optim Directory. You can include expressions in a relationship to define column data. You may also convert a database relationship or an explicit relationship to a generic relationship.