Configuring Siebel Business Applications > About Tables and Columns > About Siebel Tables >

How an Intersection Table Defines a Many-To-Many Relationship

This topic describes the intersection table. It includes the following topics:

• Overview of an Intersection Table
• How Siebel CRM Creates an Intersection Between Tables
• How Siebel CRM Creates a Many-To-Many Relationship
• Intersection Data in an Intersection Table
• How Siebel CRM Uses an Implicit Join With an Intersection Table

Overview of an Intersection Table

An intersection table is a table that defines a many-to-many relationship. It includes an intersection between two business components. A many-to-many relationship includes a one-to-many relationship from either direction. For example, a many-to-many relationship exists between Accounts and Contacts. You can view this relationship in the Siebel client:

• The Account Detail - Contacts View displays one account with multiple detail contacts.
• The Contact Detail - Accounts View displays one contact with multiple detail accounts.

Siebel CRM can include the two different views in different business objects. The business objects associate the two business components in opposite directions.

No database construct directly creates a many-to-many relationship. Instead, the Siebel schema uses two links and an intersection table to create a many-to-many relationship.

Figure 8 describes an example of how an intersection table defines a many-to-many relationship.

Figure 8. Example of How an Intersection Table Defines a Many-to-Many Relationship

The Type property of an intersection table contains Data (Intersection).

You can add custom extension columns to an intersection table. You cannot use custom extension tables to configure an intersection table. For more information, see About Links.

How Siebel CRM Creates an Intersection Between Tables

An association is a pair of ROW_ID values, where each value references a row in the base table of a business component. An intersection table contains one row for each association that exists between the row in the base table of one business component and a row in the base table of another business component. The association row in the intersection table stores the ROW_ID values of the row that resides in the base table of each business component.

Figure 9 describes how Siebel CRM creates an intersection. The associations in the intersection table serve the Opportunity/Contact and the Contact/Opportunity links and their corresponding views. The figure describes how the set of object definitions and relationships pertain to one of two links. The other link uses the same set of object types but with different relationships. Siebel CRM can display one association in both views. For example, the association between Cynthia Smith and Smith Dry Goods.

Figure 9. How Siebel CRM Creates an Intersection

Explanation of Callouts

Siebel CRM uses the following objects to create an intersection:

1. Business object. References the link that uses the intersection table. It contains the two business components that the link contains. The business object makes this reference indirectly through the child business object component of the business object.
2. Parent and child business object components. The Siebel schema uses the business object component to include business components in the business object. The business object component is a child of the business object. The detail business object component references the child business component through the Business Component property. It references the link through the Link property. The parent business object component only references the corresponding business component.
3. Link. Creates a one-to-many relationship between the two business components in a specific direction. The properties of the link define one business component as the parent and the other business component as the child in the parent-child relationship.
4. Parent and child business components. The Siebel schema specifies two business components in the link. They provide data to the objects that Siebel CRM displays in the parent-child relationship in the client. The base table of each business component contains the ROW_ID column that the Inter Child Column and Inter Parent Column properties of the link reference.
5. Intersection table. Contains the associations between rows in the base tables of the parent and child business components. Each row in the intersection table represents one association that exists between the two business components. Two columns in the intersection table serve as foreign keys to the base tables of the two business components. The Inter Parent Column and Inter Child Column properties of the link identify these columns.
6. Inter Parent column. Contains the reference to the associated row that resides in the base table of the parent business component. It is identified in the Inter Parent Column property of the link.
7. Inter Child column. Contains the reference to the associated row that resides in the base table of the child business component. It is identified in the Inter Child Column property of the link.
8. ROW_ID columns. A unique identifier column for each row that resides in the base table of each business component.

The Siebel schema uses the following properties of the link to create a many-to-many relationship. These properties are empty for a link that does not create a many-to-many relationship:

• Inter Table
• Inter Parent Column
• Inter Child Column

How Siebel CRM Creates a Many-To-Many Relationship

Figure 10 describes how Siebel CRM uses properties in two links to create a many-to-many relationship. In this example, the relationship is between opportunities and contacts.

Figure 10. Example of How Siebel CRM Creates a Many-To-Many Relationship

Explanation of Callouts

Siebel CRM uses the following objects to create a many-to-many relationship:

1. OPTY_ID column. The following properties reference the OPTY_ID column in the S_OPTY_CON table:
   • The Inter Child Column property of the Contact/Opportunity link
   • The Inter Parent Column property of the Opportunity/Contact link
2. PER_ID column. The following properties reference the PER_ID column in the S_OPTY_CON table:
   • The Inter Parent Column property of the Contact/Opportunity link
   • The Inter Child Column property of the Opportunity/Contact link
3. Contact business component. The following properties reference the Contact business component:
   • The Parent Business Component property of the Contact/Opportunity link
   • The Child Business Component property of the Opportunity/Contact link
4. Opportunity business component. The following properties reference the Opportunity business component:
   • The Child Business Component property of the Contact/Opportunity link
   • The Parent Business Component property of the Opportunity/Contact link

Intersection Data in an Intersection Table

An intersection table contains two foreign key columns that create a relationship between the records of two business components. It contains intersection data columns, which are columns that contain data that are specific to the intersection.

For example, the S_OPTY_CON table defines the many-to-many relationship that exists between opportunities and contacts. It includes several data columns in addition to OPTY_ID and PER_ID. These data columns contain information about the combination of a opportunity and a contact. Some of these columns include the following:

• ROLE_CD. The role that the contact in the opportunity plays.
• TIME_SPENT_CD. The time that the contact spends working on the opportunity.
• COMMENTS. Comment that is specific to this combination of opportunity and contact.

Some intersection data columns are useful to one parent-child relationship, some are useful to the other parent-child relationship, and some are useful to both of these relationships. For example:

• The ROLE_CD column is useful only in the context of a parent-child relationship that includes an opportunity that is the parent record that includes multiple detail contact records.
• The TIME_SPENT_CD column is useful in the context of either parent-child relationship. Each contact fulfills a unique role in the opportunity. The time spent can be useful if viewed from one of the following perspectives:
  • Time spent with each contact of an opportunity
  • Time spent with each opportunity of a contact

How Siebel CRM Uses an Implicit Join With an Intersection Table

To access an intersection data column, the Siebel schema uses a business component field that uses a join. An implicit join exists for any intersection table. It includes the same name as the intersection table. It exists for the child business component. If Siebel CRM creates a link that uses an intersection table, then it creates the implicit join. For example:

• The schema references the ROLE_CD column of the S_OPTY_CON table to the Role field that resides in the Contact business component.
• The Join property of the Role field contains S_OPTY_CON.
• The Contact business component does not contain a child S_OPTY_CON join object definition.

The Siebel schema includes the join. This join is not visible in the Object Explorer. This situation is similar to the implicit join that exists for a one-to-one extension table. You can use an implicit join to update data.