|Bookshelf Home | Contents | Index | PDF|
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 14 shows the Contact Detail - Accounts View, in which one master contact is displayed with multiple detail accounts.
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.
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.
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.
The following are descriptions of the object definitions in Figure 16:
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.
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:
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.
|Configuring Siebel eBusiness Applications|