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

How an Extension Table Stores Custom Data

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

• Overview of an Extension Table
• How a One-To-One Extension Table Extends Data Storage for a Single Business Component
• How an Implicit Join Creates a Relationship Between a Base Table and a Business Component
• An Explicit Join Creates a Relationship Between an Extension Table and a Business Component
• A One-To-Many Extension Table Stores Data From Multiple Business Components
• Summary of Support for Extension Tables and Extension Columns

For more information, see the following topics:

• Options to Use a Predefined One-to-One Extension Table
• Options to Use a Predefined One-to-Many Extension Table
• Manually Creating a One-to-One Extension Table
• Using the New Table Wizard to Create a New Table

Overview of an Extension Table

An extension table is a type of table that includes columns that you can use to store custom data. It includes an implicit one-to-one or a one-to-many relationship with a base table. Siebel CRM includes a set of predefined extension tables that you can use. Each of these tables includes generic ATTRIB_ columns that you can use to store custom data. These tables are part of the data objects layer, so you are not required to update the database if you use them.

Siebel CRM uses some ATTRIB_ columns in an extension table. You must not modify or delete an ATTRIB_ column that a predefined Siebel application uses.

You can use the New Table Wizard to create your own extension table. An extension table that you create requires a modification to the logical schema, so you must apply it to the physical database.

When Siebel CRM updates a column in a base table it does not update the timestamps of the extension tables of this base table unless it also updates the columns in the extension tables. If Siebel CRM modifies a record in an extension table, then it updates the system columns that exist in the parent table. Siebel CRM does this work because the object manager treats the associated record in an extension table as logically part of the parent record.

How a One-To-One Extension Table Extends Data Storage for a Single Business Component

The name of a one-to-one extension table includes an _X suffix. A row in an extension table contains a one-to-one relationship with the corresponding row in the base table. This row is an extension of the base table record. The value of the Type property of a one-to-one extension table is Extension.

Figure 5 describes an example of how a one-to-many extension table uses new business component fields that reference the base table and maps them to columns that are available in the one-to-one extension table. It adds the Hobby, Married, and Spouse fields to the Contact business component. These fields reference columns that reside in the S_CONTACT_X extension table.

Figure 5. Example of How a One-To-One Extension Table Extends Data Storage for a Single Business Component

How an Implicit Join Creates a Relationship Between a Base Table and a Business Component

An implicit join is a relationship that creates a one-to-one relationship between the extension table, the base table, and the business component. It odes the following:

• Creates a relationship between the following objects:
  • Between a one-to-one (_X) extension table and an intersection table.
  • Between an extension table of the S_PARTY table and the S_USER table. S_ORG_EXT, S_CONTACT, and S_POSTN are examples of these extension tables. These implicit joins map data to party business components. For example, if you add a field to the Account business component, and then choose the Join property, then Siebel Tools displays several implicit joins that it does not display in the Joins list, including joins that contain an S_ORG_EXT or S_USER alias.
• Makes the rows of the extension table available on a one-to-one basis to the business component that references the extension table.
• Is part of the Siebel object architecture. You do not use Siebel Tools to explicitly create an implicit join.
• Typically uses the table name as the Join Alias. The name of the implicit join is the same name as the extension table. If a business component field references a column in the extension table, then:
  • The Column property of the Field object contains the name of the column.
  • The Join property contains the name of the extension table.

    For example, the Column property for the Industry field in the Contact business component contains ATTRIB_48 and the Join property contains S_CONTACT_X.

• Is sometimes referred to as an implied join.

Unlike an explicit join, Siebel CRM can update the columns of an implicit join.

If you create an extension table, then Siebel Tools creates an implicit join. For more information, see How an Extension Table Stores Custom Data.

How Siebel CRM Creates an Implicit Join

Figure 6 describes how Siebel CRM creates an explicit join.

Figure 6. How Siebel CRM Creates an Implicit Join

Explanation of Callouts

Siebel CRM uses the following objects and properties to create an implicit join:

1. Empty join property. If the Join property is empty, then Siebel CRM gets the column from the base table that the business component references.
2. Column property. Identifies the table column.

An Explicit Join Creates a Relationship Between an Extension Table and a Business Component

An explicit join is a join that is different from an implicit join in the following ways:

• In the Siebel client, the user cannot typically edit a field that references a column from a joined table. You typically use this field only to display information.
• You do not create an implicit join. With an implicit join, the column in the extension table is available for you to use.

You use Siebel Tools to explicitly create the join for other tables. For more information, see About Joins.

How Siebel CRM Creates an Explicit Join

Figure 7 describes how Siebel CRM creates an explicit join.

Figure 7. How Siebel CRM Creates an Explicit Join

Explanation of Callouts

Siebel CRM uses the following objects and properties to create an explicit join:

1. Column property. Identifies the table column.
2. Defined join property. If the Join property is not empty, then the Join property identifies the join that supplies data from an extension table or other joined table.
3. Id field. A system field in the business component. It represents the ROW_ID column in the base table. You can use it in a join that involves an extension table and other joined tables. For more information, see System Fields of a Business Component.
4. PAR_ROW_ID (parent row ID) column. A column that is a foreign key to the base table that the extension table extends. Every extension table includes a column for parent row ID. Every row in an extension table contains a value in the PAR_ROW_ID column.

For more information, see Options to Use a Predefined One-to-One Extension Table.

A One-To-Many Extension Table Stores Data From Multiple Business Components

A one-to-many extension table is a table that you can use to track an entity that includes a one-to-many relationship with a parent business component but that a predefined business component does not represent. Note the following:

• You can store data for multiple business components in a one-to-many extension table.
• You use the Type column to group records in a one-to-many extension table.
• You configure each business component to get only the rows of a single type.
• A one-to-many extension table can contain multiple rows for a single row in the base table.
• The name of a one-to-many extension table includes an _XM suffix.
• Similar to a one-to-one extension table, a one-to-many extension table includes a set of generic ATTRIB_nn columns that you can use to store custom data.
• Unlike a one-to-one extension table, the value in the Type property of a one-to-many extension table is Data (Public) rather than Extension.

For more information, see the following topics:

• Options to Use a Predefined One-to-Many Extension Table
• Configuring Objects to Use a One-To-Many Extension Table
• Configuring a Business Component
• Configuring a Link That Creates a One-to-Many Relationship
• Creating a Business Object

Summary of Support for Extension Tables and Extension Columns

Table 8 summarizes support for extension tables and extension columns.