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