Bookshelf Home | Contents | Index | Search | PDF

Siebel Tools Reference > Data Objects Layer > Tables >

Intersection Tables

---

An intersection table implements a many-to-many relationship between two business components.

NOTE:  You might find it helpful to read the section titled Links before reading this section.

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 49 and Figure 50.

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

Figure 49. Account Detail - Contacts View

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

Figure 50. 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 51.

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

You can configure custom intersection tables using Advanced Database Extensibility. For information, see Advanced Database Extensibility. However, if your organization needs this functionality, contact Siebel Expert Services for assistance.

How Intersection Tables Are Configured

Figure 52 displays the object types used in the implementation and use of an intersection table.

Figure 52. Intersection Table Architecture

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 53.

Figure 53. Intersection Table Details

Figure 53 shows the object definition relationships in greater detail.

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 in Figure 53.

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

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

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 54 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.

Figure 54. Two-Link Intersection Table Example

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.

Intersection Data in the Intersection Table

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 implied join exists for any intersection table, and has the same name as the intersection table. The implied 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 implied join that exists for one-to-one extension tables. Data can also be updated through the implicit join.

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

Joins are not the only way to expose intersection data. An alternative is to use the intersection table as the base table for an intersection business component. Intersection business components are described in Intersection Business Components.

Updating Fields That Are Based on Columns in Extension Tables of Intersection Tables

It is not possible to update a field that is based on a column in an intersection table's standard extension table, through an implied or explicit join from the parent or child business component (Figure 55).

Figure 55. Cannot Use an Implied or Explicit Join to Update the Field

To update such fields, you can create a field in an intersection business component (Figure 56). Expose this field in the parent or child business component (Business Component B in Figure 56) using a multi-value link and multi-value field.

Figure 56. Use a Field in an Intersection Business Component with a Multi-Value Link and a Multi-Value Field

However, an easier solution is to use a custom extension column to the intersection table.

Bookshelf Home | Contents | Index | Search | PDF