Configuring Siebel eBusiness Applications > Configuring Tables and Columns >

About Intersection Tables

An intersection table implements a many-to-many relationship between two business components. A many-to-many relationship is one in which there is a one-to-many relationship from either direction. For example, there is a many-to-many relationship between Opportunities and Contacts. One Opportunity can be associated with many Contact people, and one Contact person can be associated with many Opportunities. Two different views can appear (in different business objects) which associate the two business components in opposite ways, as illustrated in Figure 13 and Figure 14.

Figure 13 shows the Account Detail - Contacts View, in which one account is displayed with multiple detail contacts.

Figure 13.  Account Detail - Contacts View

Figure 14 shows the Contact Detail - Accounts View, in which one master contact is displayed with multiple detail accounts.

Figure 14.  Contact Detail - Accounts View

To implement a many-to-many relationship, two links and a table designated as an intersection table are required. The table is designated as an intersection table in its Type property by means of a value of Data (Intersection). The intersection table represents the many-to-many relationship as two one-to-many relationships, which the underlying DBMS is designed to handle. There is no database construct that implements many-to-many relationships directly. This representation design is illustrated in Figure 15.

Figure 15.  Many-to-Many Relationship as Two One-to-Many Relationships

For more information about links, see About Links.

You can configure custom intersection tables using New Table Wizard. For information, see Creating New Tables Using the New Table Wizard.

About How Intersection Tables are Used

The intersection table contains one row for each association between a row in one business component's base table and a row in the other business component's base table, regardless of which one-to-many relationship the association pertains to. The association row in the intersection table stores the ROW_ID values of the row in each business component base table. The details of intersection table relationships are illustrated in Figure 16.

Notice how the associations stored in one intersection table serve both the Opportunity/Contact and Contact/Opportunity links, and their corresponding views. An association is simply a pair of ROW_ID values pointing to rows in their respective business component base tables. One association may appear in both views, for example, the association between Cynthia Smith and Smith Dry Goods.

NOTE:  The set of object definitions and relationships in Figure 16 pertains to one of the two links. The other link uses the same set of object types, but slightly different relationships.

Figure 16.  Intersection Table Details

The following are descriptions of the object definitions in Figure 16:

• Business object. The business object references the link (indirectly through the business object's child business object component) that uses the intersection table. It also contains the two business components included in the link.
• Business object components. Business Object Component object definitions are used to include business components in the business object. Business Object Component is a child object type of Business Object. The detail business object component references both the detail business component, by means of the Business Component property, and the link, by means of the Link property. The master business object component only references its corresponding business component.
• Link. The link object definition establishes a one-to-many relationship between the two business components in a particular direction. That is, the property settings in the link specify that one business component is the master and the other is the detail in the master-detail relationship.
• Master and detail business components. The two business components are specified in the link. They provide data to the user interface object definitions that display the master-detail relationship. The base table of each business component contains the ROW_ID column referenced by the Inter Child Column (detail) and Inter Parent Column (master) properties of the Link object type.
• Intersection table. The intersection table holds the associations between rows in the base tables of the master and detail business components. Each row in the intersection table represents one association between the two business components. Two columns in the intersection table serve as foreign keys to the base tables of the two business components. These columns are identified in the Inter Parent Column and Inter Child Column properties of the link.
• Inter Parent column. This column in the intersection table holds the pointer to the associated row in the master business component's base table. It is identified in the Inter Parent Column property of the Link object.
• Inter Child column. This column in the intersection table holds the pointer to the associated row in the detail business component's base table. It is identified in the Inter Child Column property of the Link object.
• ROW_ID columns. The base table of each business component has a unique identifier column for the rows in that table. This is the ROW_ID column.

NOTE:  The Inter Table, Inter Parent Column, and Inter Child Column properties of the Link object type are specific to links used in implementing many-to-many relationships based on intersection tables, and are blank in other links.

Figure 17 illustrates the property settings in the two links used to implement a many-to-many relationship—in this case the relationship between Opportunities and Contacts. Notice how the inter child column of one link is the inter parent column of the other, and the other way around. Also notice how the parent business component in one link is the child business component in the other, and the other way around. The two links are mirror images of each other.

Figure 17.  Two-Link Intersection Table Example

About Intersection Data in Intersection Tables

In addition to the two foreign key columns that establish relationships between the records in the two business components, an intersection table may contain various columns that hold data specific to the intersection of the two. These columns are called intersection data columns.

For example, in the S_OPTY_CON table, which implements the many-to-many relationship between Opportunity and Contact, there are several data columns in addition to OPTY_ID and PER_ID. These columns hold information about the combination of a particular opportunity and a particular contact. A description of a few of these columns follows:

• ROLE_CD. The role played by this contact in this opportunity.
• TIME_SPENT_CD. The time spent on this opportunity with this contact.
• COMMENTS. Comment specific to this combination of opportunity and contact.

Some intersection data columns are useful primarily to one master-detail relationship, some primarily to the other, and some to both. For example, ROLE_CD would make sense only in the context of a master-detail relationship in which an opportunity was the master record with multiple detail contact records. In contrast, TIME_SPENT_CD would make sense in the context of either master-detail relationship. That is, each contact has a unique role in the opportunity and the converse does not make sense. However, the time spent with each contact on an opportunity could be seen from the alternative perspective of the time spent on each opportunity with a contact.

An intersection data column is accessed by a field in a business component using a join. An implicit join exists for any intersection table, and has the same name as the intersection table. The implicit join is created when a link using an intersection table is created. It will exist for the child business component. For example, the ROLE_CD column in S_OPTY_CON is mapped into the Role field in the Contact business component. The Join property of this field has the value S_OPTY_CON. The Contact business component does not have a child join object definition named S_OPTY_CON; the join is automatically provided and invisible in the Object Explorer. This is similar to the implicit join that exists for one-to-one extension tables. Data can also be updated through the implicit join.

Intersection tables can be extended with extension columns. They cannot be extended with custom extension tables.