How table relations are structured

Detailed guidelines for relating tables

Building relations that successfully display the data you want requires a certain amount of planning, especially if the relation involves a number of tables. The guidelines that follow may help you as you build a relation.

Matching between key fields

Carefully consider the common key fields in each table pair, the type of values they contain, and the matches that are likely to result. The completeness and accuracy of matching between key fields directly impacts the quality of any subsequent informational review or analysis.

Reversing the parent-child relation

Consider the implications of making one table the parent and the other the child, and how the results might differ if you reverse the position of the two tables.

Keep in mind that relations in Analytics are of the many-to-one type. Single or identical parent key values are related to the first occurrence only of a matching child key value. Additional occurrences of matching child key values, and the records containing them, are ignored.

If legitimate duplicate child key values exist, making the child table the parent may yield more complete results, assuming the current parent table does not also contain legitimate duplicates.

If both tables contain legitimate duplicates, joining the tables using a many-to-many join may be a better approach.

The effect of a table's position in the relational hierarchy

At each level of a relational hierarchy, a child table can be a parent to a table at the next lower level of the hierarchy.

Because of the many-to-one matching of key values between parent and child, as a table is positioned progressively lower in the hierarchy, the possibility increases that more of its data will be omitted from the final relation. This potential cumulative effect of many-to-one matching is not an issue if a one-to-one correspondence exists between the values in all common key fields.

If you want to ensure that all the records in a table are included in a relation, make that table the parent table.

Using intermediary tables

If you want to relate two tables that lack a common key field, you may be able to build the relation using one or more intermediary tables.

In the figure above, the Contract table serves as an intermediary table that indirectly relates the Accounts Receivable and the Sales tables. You may or may not be interested in the data in the intermediary table.

Variations on the basic relational association

Beyond the basic parent-to-child relational association, the following types of relational associations are allowed or disallowed:

Individual instances of two tables can have only one relational association	If you try to relate the same table pair a second time, the operation is prohibited and the following message appears: "One of these files is already part of a relation. To create another relation, add another instance of the file." You can add another instance of the required table by clicking the Add Table button in the Relations dialog box and selecting the appropriate table. An additional table instance is added with an incrementing numeric suffix, or a name of your choosing. Alternatively, you can make a copy of the appropriate table layout in the Navigator, and add the copy to the Relations dialog box.
Relating tables using multiple key fields	Tables can be related using two or more key fields in combination if the key fields are concatenated. For more information, see Using multiple key fields in combination. A parent table can be related to two (or more) separate instances of the same child table. The relation can be between: the same parent key field and two different child key fields two different parent key fields and the same child key field For more information, see Using multiple key fields in isolation.
Relating a table to itself	A table can be related to a separate instance of itself.