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:

• A One-To-One Extension Table Extends Data Storage for a Single Business Component
• 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

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

NOTE:  Siebel CRM uses some ATTRIB_ columns in an extension table. Do 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. Because an extension table that you create requires a change to the logical schema, 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 the base table unless the columns in the extension tables are also updated. However, if Siebel CRM changes a record in an extension table, then it updates the system columns in the parent table. Siebel CRM performs this work because the object manager treats the associated record in an extension table as logically part of the parent record.

Related Topics

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

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. The row is basically an extension of the base table record. The value of the Type property of a one-to-one extension table is Extension.

Figure 5 illustrates an example of how a one-to-many extension table requires you to create new fields for the business component of the base table and map them to available columns in the one-to-one extension table. The Hobby, Married, and Spouse fields are added to the Contact business component. These fields reference columns 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

An Implicit Join Creates a Relationship Between a Base Table and a Business Component

An implicit join is a relationship that establishes a one-to-one relationship between the extension table, the base table, and the business component. An implicit join fulfills the following roles:

• Establishes a relationship between the following objects:
  • Between one-to-one (_X) extension tables and relevant intersection tables.
  • Between extension tables 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 are not included in the Joins list in Siebel Tools, 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 uses 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 field in the business component references a column in the extension table, then the Column property of the Field object is the name of the column, and the Join property is 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 from an implicit join.

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

How Siebel CRM Constructs an Implicit Join

Figure 6 illustrates how Siebel CRM constructs an explicit join.

Figure 6. How Siebel CRM Constructs an Implicit Join

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

1. Empty join property. If the Join property is empty, then Siebel CRM obtains 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 automatically available for you to use.

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

How Siebel CRM Constructs an Explicit Join

Figure 7 illustrates how Siebel CRM constructs an explicit join.

Figure 7. How Siebel CRM Constructs an Explicit Join

Siebel CRM uses the following objects and properties to construct 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, and 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 is not represented by a predefined business component. 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 retrieve only the rows of a given type.

The name of a one-to-many extension table includes an _XM suffix. A one-to-many extension table can contain multiple rows for a single row in the base table. 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 for a one-to-many extension table is Data (Public) rather than Extension.

Related Topics

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
• Customizing a Business Component
• Configuring a Link to Create a One-to-Many Relationship
• Creating a Business Object

Summary of Support for Extension Tables and Extension Columns

Table 7 summarizes support for extension tables and extension columns.