Bookshelf Home | Contents | Index | Search | PDF

Siebel Tools Reference > Data Objects Layer > Tables >

Extension Tables

---

An extension table provides additional columns to a data table that cannot be directly added to the original table because the underlying DBMS may support only a limited number of columns, or will not allow adding a column to a table once it is populated with data. An extension table allows you to provide additional columns for use as fields in a business component without violating DBMS or Siebel application restrictions.

An extension table is a logical augmentation of an existing table. Its columns are provided mostly for developers, and are generally not used by standard Siebel applications. An extension table extends a base table in the sense that it effectively adds additional columns. These columns are not physically part of the base table, but are available for use in a business component alongside the base table columns as if they were.

NOTE:  When columns in a base table are updated, the timestamps of its extension tables are not updated unless columns in those extension tables are also updated.

The relationships between a base table, an extension table, and the business component that uses them are illustrated in Figure 45.

Figure 45. Business Component, Base Table and Extension Table

Note the following distinctions between standard and custom extension tables:

Siebel applications provide standard extension tables for several of the standard data tables. A standard extension table has a predefined relationship with a standard data table. This relationship allows you to add columns for new functionality without making alterations to the base table. You cannot create or delete standard extension tables.
You can use the Table Wizard to create custom extension tables to extend data tables, provided the data tables are of type Data (Public).

An extension table, whether standard or custom, provides a set of generic columns of various data types and lengths for your use. These may eliminate the need to add a custom column to the extension table. Generic columns in an extension table have names of the form ATTRIB_xx, where xx stands for a two-digit number. For example, there are generic columns named ATTRIB_04 and ATTRIB_12.

The standard Siebel applications use certain columns in extension tables. The following columns in these tables are used:

S_CONTACT_X. ATTRIB_03, 04, 05, 06, 07, 08, 14, 15, 26, 48, and MODIFICATION_NUM.
S_EMPLOYEE_X. ATTRIB_48.
S_OPTY_X. ATTRIB_04, 05, 08, 09, 10, 11, 15, 16, 17, 18, 34, 35, 36, 37, 38, 39, 41, 42, 43, 44, 45.
S_ORG_EXT_X. ATTRIB_01, 02, 03, 08, 14, 15, 16, 27, 48, 49, 50, 51, 52, 53.

NOTE:  Extension columns used by standard Siebel applications should be treated as data columns in base tables—that is, they should not be modified or deleted.

Figure 46 illustrates how columns from a standard one-to-one extension table are used in the Contact business component in Siebel applications.

Figure 46. Extension Table Example

There are eight fields in the Contact business component displaying data from generic columns in S_CONTACT_X; only three are shown here. Extension tables themselves are "sparse"—extension table rows exist only for those base table rows that have extension data to store.

Extension tables can be of the one-to-one or one-to-many style:

Rows in one-to-one extension table have a one-to-one relationship with corresponding rows in the base table. A one-to-one extension table extends the base table horizontally, as shown in Figure 46. One-to-one extension tables are described in greater detail in the following paragraph.
In a one-to-many extension table, there are multiple extension table rows for each base table row. There are standard one-to-many extension tables for certain of the major business components, including Opportunity, Contact and Account. These are used primarily to create multi-value groups based on user-created business components. One-to-many extension tables are described in One-to-Many Extension Tables.

One-to-One Extension Tables

One-to-one extension tables have the _X suffix on their names (with the exception of TAS tables, which have the suffix _T). The details of the object definition relationships (excluding the implied join) are illustrated in Figure 47.

Figure 47. Extension Table Details (Excluding Implied Join)

The object definitions in Figure 47 are:

Business component. Business component being extended.
Fields based on base columns. Fields that represent data from columns in the business component's base table. They are unaffected by the extension table.
Fields based on extension columns. Represent data from columns in the extension table.
Extension table. Provides columns that may be used to add developer-defined fields to the business component.

When writing data to a one-to-one extension table, at least one column of the extension table must be updated for a record to be written to the extension table.

For example, you might want to create a workflow policy based on a column in a 1:1 extension table. If there is no data to be written, the record will not be updated. Therefore, the workflow policy will not be triggered.

Implied Joins

Underlying the one-to-one extension table's relationships with the base table and business component is a set of hidden relationships called an implied or implicit join. The implied join makes the extension table rows available on a one-to-one basis to the business component that uses the extension table. Every extension table has an implied join with the business component it extends. This join always has the name of the extension table.

An implied join is different from joins defined as object definitions. Data can be updated through an implied join. Data can be displayed only through other joins. This update capability is important for extension table functionality.

When a field in the business component is based on a column in the extension table, the Column property of the Field object is set to the name of the column, and the Join property is set to the name of the extension table. For example, the Birthday field in the Contact business component has a Column property value of ATTRIB_26 and a Join property value of S_CONTACT_X.

The details of the object definition relationships in an implied join are illustrated in Figure 48.

Figure 48. Extension Table Details with Implied Join

The following definitions participate in the implementation of the implied join:

Id field. The Id field is a system field in the business component. It represents the ROW_ID column in the base table, and it can be used in joins involving extension tables and other joined tables.
PAR_ROW_ID column. PAR_ROW_ID stands for parent row ID. Every extension table has this column, and every extension table row has a value there. It is used as a foreign key to the base table that is extended by the extension table.

For more information, see Joins.

One-to-Many Extension Tables

One-to-many extension tables have a Type property value of Data (Public) rather than Extension. However, from a functional standpoint, one-to-many extension tables are considered extension tables, and they have the same set of generic and system columns. The names of one-to-many extension tables have the suffix _XM.

You can extend existing one-to-many extension tables. You can also add new one-to-many extension tables using Advanced Database Extensibility. For information, see Advanced Database Extensibility.

You can use one-to-many extension tables to create multi-value groups and master-detail views that are based on custom business components—that is, business components not present in standard Siebel applications.

For example, you may have a need for three new multi-value fields in the Contact business component to store information on hobbies, prior companies, and areas of expertise for each contact. No business components exist for these entities. However, you can implement the same functionality using S_CONTACT_XM, the one-to-many extension table that extends the Contact business component. A one-to-many, rather than one-to-one, extension table is required because there can be many hobbies, prior companies, or areas of expertise for one contact. Since the relationship is one-to-many rather than one-to-one, a link is required rather than an implicit join. See Business Objects Layer for a discussion of master-detail business components.

Bookshelf Home | Contents | Index | Search | PDF