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:

• How Siebel CRM Constructs an Intersection Between Tables
• How Siebel CRM Constructs a Many-To-Many Relationship
• Intersection Data in an Intersection Table
• How Siebel CRM Uses an Implicit Join with an Intersection Table

An intersection table is a table that defines a many-to-many relationship. It provides an intersection 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 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.

The two different views can be included in different business objects. The business objects associate the two business components in opposite directions.

There is no database construct that directly establishes 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 illustrates 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 customize an intersection table with extension columns. You cannot customize an intersection table with custom extension tables.

For more information, see About Links.

How Siebel CRM Constructs an Intersection Between Tables

An association is a pair of ROW_ID values, where each value references a specific 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 in the base table of each business component.

Figure 9 illustrates how Siebel CRM constructs an intersection. The associations in the intersection table serve the Opportunity/Contact and the Contact/Opportunity links, and their corresponding views. The figure illustrates 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 Constructs an Intersection

Siebel CRM uses the following objects to construct an intersection:

1. Business object. References the link that uses the intersection table. It also contains the two business components that are included in the link. 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 also references the link through the Link property. The parent business object component only references the corresponding business component.
3. Link. Establishes a one-to-many relationship between the two business components in a specific direction. The properties of the link define that one business component is the parent and the other is 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 display the parent-child relationship in the Siebel 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 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.
6. Inter Parent column. Contains the reference to the associated row 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 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 the rows in the base table of each business component.

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

• Inter Table
• Inter Parent Column
• Inter Child Column

How Siebel CRM Constructs a Many-To-Many Relationship

Figure 10 illustrates how Siebel CRM uses properties in two links to construct a many-to-many relationship. In this example, the relationship is between Opportunities and Contacts.

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

Siebel CRM uses the following objects to construct 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 establish a relationship between the records of two business components. It also 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 between opportunities and contacts, and it includes several data columns in addition to OPTY_ID and PER_ID. These data columns contain information about the combination of a specific opportunity and a specific contact. Some of these columns include:

• ROLE_CD. The role that the contact in the opportunity plays.
• TIME_SPENT_CD. The time spent on the opportunity with the contact.
• 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 in which an opportunity is the parent record with 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 field in a business component that uses a join. An implicit join exists for any intersection table, and it includes the same name as the intersection table. Siebel CRM creates the implicit join if a link that uses an intersection table is created. It exists for the child business component. For example:

• The schema references the ROLE_CD column of the S_OPTY_CON table to the Role field 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 schema automatically provides the join. This join is not visible in the Object Explorer. This is similar to the implicit join that exists for a one-to-one extension table. You can also use an implicit join to update data.