About Tables and Columns

This chapter describes tables and columns. It includes the following topics:

About Siebel Tables

This topic describes Siebel tables. It includes the following information:

For more information, see Overview of the Data Objects Layer.

Overview of Siebel Tables

The object definition of a Siebel table is a logical representation of the physical table that resides in the underlying RDBMS. Note the following:

You can use an extension table to configure the data objects layer.
You can use an extension column on a base table.
You cannot add a new base table, delete a base table or column, or modify the properties of a base column.

Siebel CRM uses the term base table to describe the following object definitions:

The table that an extension table configures, as defined in the Base Table property of the extension table
The table that a business component references, as defined in the Table property of the business component

For more information, see Guidelines for Naming an Object.

Naming Format for a Siebel Table

A Siebel table in the Siebel database uses the following three part naming format:

PREFIX_NAME_SUFFIX

The following table describes the naming format.

Table Parts of the Table Naming Format

Part	Description
PREFIX	A one-letter to three-letter prefix that distinguishes the table from other tables. Example prefixes include EIM_, S_, W_, and so on.
NAME	A unique table name that is typically an abbreviation of the name of the entity supertype. For example, the table name for the event supertype is EVT.
SUFFIX	The subtype of the entity. For example, the EVT supertype includes the activity subtype that the ACT suffix represents. For example, S_EVT_ACT.

The following table describes some of the prefixes that Siebel CRM commonly uses. Each prefix indicates the part of the Siebel schema that contains the table.

Table Table Prefixes That Siebel CRM Commonly Uses

Prefix	Description
EIM_	Interface table for Enterprise Integration Manager.
S_	Siebel base table. In some situations, a table might contain a name of the form S_name_IF. This format indicates an obsolete interface table.
W_	Siebel Business Data Warehouse table.

Prefix

Description

EIM_

Interface table for Enterprise Integration Manager.

S_

Siebel base table.

In some situations, a table might contain a name of the form S_name_IF. This format indicates an obsolete interface table.

W_

Siebel Business Data Warehouse table.

The following table describes some of the suffixes that Siebel CRM commonly uses. Each suffix indicates a table type.

Table Table Suffixes That Siebel CRM Commonly Uses

Suffix	Description
_ATT	File attachment table.
_REL	A table that supports a many-to-many relationship from an entity back to itself.
_SS	A table that stores Siebel-to-Siebel integration information.
_X	A one-to-one extension table that you can use to add custom data to the Siebel database.
_XA	A table that stores custom data that Siebel CRM associates with an object class.
_XM	A one-to-many extension table that you can use to add custom data to the Siebel database.

How an Extension Table Stores Custom Data

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

For more information, see the following topics:

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.

The following figure 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.

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

The following figure describes how Siebel CRM creates an explicit join.

Explanation of Callouts

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

Empty join property. If the Join property is empty, then Siebel CRM gets the column from the base table that the business component references.
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

The following figure describes how Siebel CRM creates an explicit join.

Explanation of Callouts

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

Column property. Identifies the table column.
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.
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.
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:

Summary of Support for Extension Tables and Extension Columns

The following table summarizes support for extension tables and extension columns.

Table Summary of Support for Extension Tables and Extension Columns

Object	Description
Public data table	Can be extended by using an extension table and extension columns.
Private data table	The following support is available for the private data table: Cannot contain an extension column Cannot add an extension column to a private data table A private data table is a table with the Type property set to Data (Private). Some interface tables are private, but most are public.
Intersection table	The following support is available for an intersection table: Can be extended with an extension column Cannot be extended with a custom extension table
LOV Bounded, LOV Type property of a table column	Read-only for a predefined column in Siebel CRM but is editable for a custom extension column. MLOV (multilingual list of values) is allowed with a custom extension column.
Predefined one-to-one extension column	It is recommended that you do not modify or delete a predefined one-to-one extension column.
Predefined extension column	Similar to a data column in a base table, you must not modify or delete a predefined extension column that a predefined Siebel application uses.
Custom extension column	The following support is available for a custom extension column: You can use the Database Designer to add a custom extension column to a base table. The Database Designer is available in the Tables list in Siebel Tools. The relational database that you use with Siebel CRM determines if you can or cannot create a custom extension column on a base table. You can add a custom extension column to one of several types of tables. For more information, see Adding an Extension Column to a Base Table.
Custom extension table	The following is available for a custom extension table: You can use the Database Designer to create a new one-to-one extension table. Several types of custom extension tables are available. For more information, see the table in Options to Use a Predefined One-to-One Extension Table.
EIM mapping	The following support is available for Enterprise Integration Manager (EIM) mapping: The EIM Table Mapping Wizard allows you to create or associate a new table with an interface table that uses EIM: You can create EIM Table Mapping objects to import data to a table that you define. You can automate the creation of an EIM attribute map on an extension column that Siebel CRM adds to a base table. You cannot add an EIM mapping for a foreign key relationship to a table that does not contain a user key. For more information, see Mapping a Custom Table to an Interface Table.
Custom extension to a dock object	The Dock Object Mapping Wizard allows you to associate a new table with a predefined or a new custom dock object. This support allows Siebel CRM to synchronize data that resides in the dock object of a Remote user.

How an Intersection Table Defines a Many-To-Many Relationship

This topic describes the intersection table. It includes the following information:

Overview of an Intersection Table

An intersection table is a table that defines a many-to-many relationship. It includes an intersection between two business components. A many-to-many relationship includes a one-to-many relationship from either direction. For example, a many-to-many relationship exists between Accounts and Contacts. You can view this relationship in the Siebel client:

The Account Detail - Contacts View displays one account with multiple detail contacts.
The Contact Detail - Accounts View displays one contact with multiple detail accounts.

Siebel CRM can include the two different views in different business objects. The business objects associate the two business components in opposite directions.

No database construct directly creates a many-to-many relationship. Instead, the Siebel schema uses two links and an intersection table to create a many-to-many relationship.

The following figure describes an example of how an intersection table defines a many-to-many relationship.

The Type property of an intersection table contains Data (Intersection).

You can add custom extension columns to an intersection table. You cannot use custom extension tables to configure an intersection table. For more information, see About Links.

How Siebel CRM Creates an Intersection Between Tables

An association is a pair of ROW_ID values, where each value references a row in the base table of a business component. An intersection table contains one row for each association that exists between the row in the base table of one business component and a row in the base table of another business component. The association row in the intersection table stores the ROW_ID values of the row that resides in the base table of each business component.

The following figure describes how Siebel CRM creates an intersection. The associations in the intersection table serve the Opportunity/Contact and the Contact/Opportunity links and their corresponding views. The figure describes how the set of object definitions and relationships pertain to one of two links. The other link uses the same set of object types but with different relationships. Siebel CRM can display one association in both views. For example, the association between Cynthia Smith and Smith Dry Goods.

Explanation of Callouts

Siebel CRM uses the following objects to create an intersection:

Business object. References the link that uses the intersection table. It contains the two business components that the link contains. The business object makes this reference indirectly through the child business object component of the business object.
Parent and child business object components. The Siebel schema uses the business object component to include business components in the business object. The business object component is a child of the business object. The detail business object component references the child business component through the Business Component property. It references the link through the Link property. The parent business object component only references the corresponding business component.
Link. Creates a one-to-many relationship between the two business components in a specific direction. The properties of the link define one business component as the parent and the other business component as the child in the parent-child relationship.
Parent and child business components. The Siebel schema specifies two business components in the link. They provide data to the objects that Siebel CRM displays in the parent-child relationship in the client. The base table of each business component contains the ROW_ID column that the Inter Child Column and Inter Parent Column properties of the link reference.
Intersection table. Contains the associations between rows in the base tables of the parent and child business components. Each row in the intersection table represents one association that exists between the two business components. Two columns in the intersection table serve as foreign keys to the base tables of the two business components. The Inter Parent Column and Inter Child Column properties of the link identify these columns.
Inter Parent column. Contains the reference to the associated row that resides in the base table of the parent business component. It is identified in the Inter Parent Column property of the link.
Inter Child column. Contains the reference to the associated row that resides in the base table of the child business component. It is identified in the Inter Child Column property of the link.
ROW_ID columns. A unique identifier column for each row that resides in the base table of each business component.

The Siebel schema uses the following properties of the link to create a many-to-many relationship. These properties are empty for a link that does not create a many-to-many relationship:

Inter Table
Inter Parent Column
Inter Child Column

How Siebel CRM Creates a Many-To-Many Relationship

The following figure describes how Siebel CRM uses properties in two links to create a many-to-many relationship. In this example, the relationship is between opportunities and contacts.

Explanation of Callouts

Siebel CRM uses the following objects to create a many-to-many relationship:

OPTY_ID column. The following properties reference the OPTY_ID column in the S_OPTY_CON table:
- The Inter Child Column property of the Contact/Opportunity link
- The Inter Parent Column property of the Opportunity/Contact link
PER_ID column. The following properties reference the PER_ID column in the S_OPTY_CON table:
- The Inter Parent Column property of the Contact/Opportunity link
- The Inter Child Column property of the Opportunity/Contact link
Contact business component. The following properties reference the Contact business component:
- The Parent Business Component property of the Contact/Opportunity link
- The Child Business Component property of the Opportunity/Contact link
Opportunity business component. The following properties reference the Opportunity business component:
- The Child Business Component property of the Contact/Opportunity link
- The Parent Business Component property of the Opportunity/Contact link

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

How Siebel CRM Uses an Implicit Join With an Intersection Table

To access an intersection data column, the Siebel schema uses a business component field that uses a join. An implicit join exists for any intersection table. It includes the same name as the intersection table. It exists for the child business component. If Siebel CRM creates a link that uses an intersection table, then it creates the implicit join. For example:

The schema references the ROLE_CD column of the S_OPTY_CON table to the Role field that resides in the Contact business component.
The Join property of the Role field contains S_OPTY_CON.
The Contact business component does not contain a child S_OPTY_CON join object definition.

The Siebel schema includes the join. This join is not visible in the Object Explorer. This situation is similar to the implicit join that exists for a one-to-one extension table. You can use an implicit join to update data.

About Columns and Indexes in a Siebel Table

A column is a representation of the physical column that resides in the underlying database management system. The Siebel schema records the name, data type, length, primary key status, foreign key status, alias, and other properties of the database column as properties in the corresponding object definition of the column. The schema includes other properties that are internal to Siebel CRM in the object definition, such as the Changed status, Inactive status, and the Type. For more information, see the following topics:

Data Columns of a Siebel Table

A data column is a column that provides data for a field. It can serve as a foreign key that references a row in another table. Most columns in Siebel CRM are data columns. A data column is sometimes referred to as a base column. A data column can be public or private. You cannot modify the properties of a data column.

Extension Columns of a Siebel Table

An extension column is a column that stores custom data. Siebel CRM supports the following types of extension columns:

Predefined extension column. Included in a predefined extension table. Siebel CRM names these columns ATTRIB_nn, where nn is a value between 01 and 47. For example, ATTRIB_13. It is recommended that you do not modify or delete a predefined extension column.
Custom extension column in an extension table. Added by a developer to an extension table. Siebel CRM names these with an X_ prefix.
Custom extension columns in a base table. Added by a developer to a base table. The relational database system that you use with Siebel CRM determines if this configuration is allowed or not allowed. If the database system supports a custom extension column in a base table, it might be preferable for performance reasons to add it to the base table rather than to add it to an extension table. Performance might be affected if you add an extension column to an extension table because Siebel CRM creates extra SQL to join the extension table.

System Columns of a Siebel Table

A system column is a column that Siebel CRM displays in all tables, but it does not include the same set of system columns in every table. You can use the data in a system column for various reasons. For example, you can use the ROW_ID column to create a join. Most system columns are read-only. You typically must not modify the data in a system column. Some exceptions exist, such as using certain system columns in an interface table. For more information, see System Fields of a Business Component.

The following table describes some of the system columns that Siebel CRM commonly uses.

Table System Columns That Siebel CRM Commonly Uses

Column	Description
ROW_ID	Stores a unique, base 36 alphanumeric identifier for the rows in the table. ROW_ID is present in all tables. It is the typical destination column of a foreign key relationship from another table. In a predefined data table, the Id field often represents ROW_ID for use in a join or link. For example, the Id field in the Account business component represents the ROW_ID column in the S_ORG_EXT table. For more information, see Relationship Between a System Field and a System Column.
CREATED	Stores the creation date and time of each record.
CREATED_BY	Stores the ROW_ID of the S_USER record of the person who created the record. This is not the user name that the user enters when the user logs in to Siebel CRM.
LAST_UPD	Stores the date of the last update that Siebel CRM performed for the record.
LAST_UPD_BY	Stores the ROW_ID of the S_USER record of the person who last updated the record. This is not the user name that the user enters when the user logs in to Siebel CRM.
DB_LAST_UPD	Stores the date of each record that Siebel CRM updates in the database. DB_LAST_UPD is different than LAST_UPD. For example, if the user updates a record, then Siebel CRM updates the LAST_UPD and DB_LAST_UPD columns in the local database. If the user synchronizes with a Server database, then Siebel CRM only updates the DB_LAST_UPD column.
PAR_ROW_ID	Stores a foreign key to the ROW_ID column of the base table. Siebel CRM includes the PAR_ROW_ID column in extension tables, file attachment tables, and tables whose name contains a _T suffix.

Siebel CRM updates the following columns:

CREATED
CREATED_BY
LAST_UPD
LAST_UPD_BY
ROW_ID

The following columns store the date, time, and user values for the client. They do not store the date, time, and user values for the Siebel database:

CREATED
CREATED_BY
LAST_UPD
LAST_UPD_BY

Indexes of a Siebel Table

An index is a logical representation of a physical index that resides in the underlying database management system. Siebel CRM includes a set of predefined indexes. The name for each index contains an S_ prefix. You must not modify or delete a predefined index. You can create a custom index. For more information, see Properties of an Index of a Siebel Table and Creating a Custom Index.

Index Columns of an Index

An index column is a child object of the index object. The object definition for an index column associates one column to the parent index. For more information, see Properties of an Index Column and Creating a Custom Index.

How a User Key Creates a Unique Set of Values

A user key is a key that specifies columns that must contain unique sets of values. The purpose of a user key is to prevent the user from entering duplicate records. You can use it to determine the uniqueness of records during a data import operation in Enterprise Integration Manager.

The name of the parent table of the user key that contains an _Un suffix designates the user key. For example, S_PROD_INT_U1. Each user key includes User Key Column child objects that define the table columns that must include unique values. For example, BU_ID, NAME, and VENDR_OU_ID in the S_PROD_INT_U1 user key.

A predefined index exists for each predefined user key. This index uses the following format:

S_TABLE_NAME_Un

You cannot add or modify a user key that resides in a predefined Siebel table or an EIM base table. For help with remapping data to meet your business requirements, see Getting Help From Oracle.

For more information, see About Interface Tables.

How the S_Party Table Controls Access

The party model organizes entities such as Person, Organization, Position, and Household. A party always represents a single person or a group that Siebel CRM can translate to a set of people, such as a company or a household. Siebel data access technology uses this party model. Some parts of the data objects layer use the party model to abstract the difference between people, companies, households, and other legal entities. This model covers the relationships that exist between your company and people, such as contacts, employees, partner employees, and users, and other businesses, such as accounts, divisions, organizations, and partners. Siebel CRM uses the S_PARTY table as the base table for this access. The Siebel schema implicitly joins related tables as extension tables.

The following information lists the extension tables and their corresponding EIM interface tables. A party table is a table that holds party data. Some example party tables include S_CONTACT, S_ORG_EXT, S_USER, and S_POSTN.

Table S_PARTY Extension Tables and Corresponding EIM Interface Tables

Data Type	Extension Table to S_PARTY	EIM Interface Table
Accounts	S_ORG_EXT	EIM_ACCOUNT
Business Units	S_BU	EIM_BU
Contacts	S_CONTACT	EIM_CONTACT
Employees	S_CONTACT	EIM_EMPLOYEE
Households	S_ORG_GROUP	EIM_GROUP
Positions	S_POSTN	EIM_POSITION
Users	S_USER	EIM_USER

The Siebel schema implicitly joins these extension tables to the S_PARTY table, so they are available through the S_PARTY table. The PARTY_TYPE_CD column of the S_PARTY table supports the following types:

AccessGroup
Household
Organization
Person
Position
UserList

Guidelines for Using the S_PARTY_PER and S_PARTY_REL Tables

The predefined S_PARTY_PER and S_PARTY_REL intersection tables create a many-to-many relationship between party business components, such as Account and Contact. The table you use depends on whether you must or must not enforce access control.

You can use the S_PARTY_PER table to create a many-to-many relationship between two party business components where you must create access control. Records in the S_PARTY_PER table provide data access rights from the parent to the child parties. To maintain a good response time with a query that constrains visibility, you must minimize the number of rows that the S_PARTY_PER table contains. If you create a many-to-many relationship where you do not require access control, such as if you create a recursive many-to-many relationship between a party business component and itself, then it is recommended that you use the S_PARTY_REL table.

For example, you can use the S_PARTY_PER table to create a relationship between the following items:

Access groups and members
Accounts and contacts
Employees and positions
User lists and users

If you must configure tables in the party model, then you must create an extension table from the S_PARTY table. For example, S_CONTACT is an extension table of the S_PARTY table. The S_CONTACT table is an Extension (Siebel) type, so you cannot use it as a base table for an extension table. You must create an extension table and use the S_PARTY table as the base table. To display data from the new extension table, you can create an explicit join that brings data from the new extension table to the business component you are using.

For more information about the party model, see Siebel Security Guide.

Options to Configure the Data Objects Layer

This topic describes options to configure the data objects layer. It includes the following information:

For more information, see the following topics:

Options to Configure Predefined Objects and Perform Advanced Configuration

This topic describes options that are available to you to configure predefined objects and to do advanced configuration.

Options to Configure a Predefined Database Object

You can configure a predefined extension table or column that is available for you to use for your own purposes. These tables and columns provide the easiest option to store more entities because they are already part of the data objects layer. Using them does not require you to modify the logical schema. The following predefined extensions are available:

Extension columns
One-to-one extension tables
One-to-many extension tables

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

You can use the Database Designer to add an extension column to a base table or to create a new one-to-one extension table. For more information, see Adding an Extension Column to a Base Table.

Options to Perform Advanced Configuration of Database Objects

You can use the New Table Wizard to create the following types of tables:

Stand-alone table
One-to-one extension table
One-to-many extension table
Intersection table

For more information, see Using the New Table Wizard to Create a New Table.

You can use the EIM Table Mapping Wizard to map an extension to an interface table. This wizard allows you to create or associate the new table to the interface table that uses EIM. You can create EIM table mapping objects that import data to tables you define, and you can automate how Siebel Tools creates an EIM attribute map on an extension column that you add to a base table. For more information, see Mapping a Custom Table to an Interface Table for Siebel EIM

You can use the Dock Object Mapping Wizard to map an extension to a dock object. To support data synchronization to Remote users, this wizard allows you to associate the new table with a predefined or custom dock object. For more information, see Configuring Dock Objects for Siebel Remote

Options to Use a Predefined One-to-One Extension Table

Siebel CRM uses one-to-one predefined extension tables for many of the predefined data tables. The predefined extension table contains columns of various types that possess a predefined one-to-one relationship with a base table. This base table uses more columns in the extension table for new functionality without modifying the base table or database schema. For more information, see Guidelines for Modifying a Predefined One-to-One Extension Table.

A one-to-one predefined extension table does not require you to create a new business component because Siebel CRM implicitly defines this type of table as a join. For more information about implicit joins, see How an Extension Table Stores Custom Data.

A one-to-one predefined extension table includes an _X suffix, such as S_PROD_INT_X. Siebel CRM names the columns that these tables contain with ATTRIB_nn, where nn is a value from 01 to 47.

The following table lists the different data types in a Siebel extension table and the number of columns of each data type.

Table Data Types in a Predefined Extension Column

Data Type	Number of Columns
Number	12
Date	10
Varchar(255)	1
Varchar(100)	5
Varchar(50)	10
Varchar(30)	5
Char(1)	4

Determining Availability of a Predefined Extension Column

You must determine whether a predefined Siebel application uses or does not use the table column before you use a predefined extension table. To do this, you search the Siebel repository for fields that Siebel CRM associates with the column.

Caution: If a predefined field references a column, then do not deactivate the field.

To determine availability of a predefined extension column

In Siebel Tools, in the Object Explorer, click the Flat tab.
In the Object Explorer, click Field.

In the Fields list, create a query using values from the following table.

Property	Value
Column	Name of the column you must use.
Join	Name of the extension table you must use.

If the query does not return any Field object definitions, then Siebel CRM does not use the column in the extension table and it is available.

If the query returns one or more object definitions, then you must find another extension column in this table. To identify the extension columns that Siebel CRM currently uses, do the query again using values from the following table.

Property	Value
Column	ATTRIB*
Join	Name of the extension table you must use.

Options to Use a Predefined One-to-Many Extension Table

More than 20 predefined tables exist that contain a one-to-many relationship with a base table. These tables include the _XM suffix. They include generic columns that you can use to store more data. They allow you to track entities that do not exist in a predefined Siebel application, and they include a one-to-many relationship to a predefined base table. The extension tables themselves are already part of the data objects layer, so you are not required to modify the database schema. For more information, see How an Extension Table Stores Custom Data.

Guidelines for Configuring the Data Objects Layer

This topic describes guidelines to configure the data objects layer. It includes the following information:

For more information, see Guidelines for Reusing a Predefined Table.

Overview of Guidelines for Configuring the Data Objects Layer

If you configure the data objects layer, then use the following guidelines:

Do not modify a predefined base table or the columns of a predefined base table.
Do not modify a predefined one-to-one extension table or the column of a predefined one-to-one extension table. For more information, see Options to Use a Predefined One-to-One Extension Table.
The predefined user interface that Siebel CRM displays in the Siebel client does not use all of the relationships that are available in the underlying data objects layer. Most entity relationships are available for you to use. It is recommended that you use predefined objects in the data objects layer, if possible.
To minimize the effect of your modifications on other developers, make any bulk modifications to the Siebel schema at the beginning of each project phase. If you make modifications during a project phase, then you must distribute these modifications to all other remote users. You can use Siebel Anywhere to distribute a schema modification. Otherwise, you must create a new database extract for each remote user before you can progress to the next phase.
If your deployment runs in a DB2 environment, then do not create a column that contains a name that is longer than 18 characters.
The data objects layer includes over 2,000 database tables. Each of these tables uses a consistent naming format to help you identify each individual table. For information on naming formats for tables, see About Siebel Tables.

Guidelines for Creating a New Table

If you create a new table, then use the following guidelines:

You can only create the following types of tables:
- Data (Public)
- Data (Intersection)
- Extension
You must explicitly grant permissions on any table that you define.
Create a new table only after you explore other ways of meeting your business requirements, such as using a predefined extension table.

Guidelines for Adding an Extension Column to a Base Table

You can add an extension column to a predefined base table. Adding an extension column avoids having to add another join to an extension table to store custom data. You can add an extension column to any of the following table types:

Data table
Intersection table
Interface table
Predefined extension table
Custom extension table
Extension (Siebel) table

You cannot add an extension column to a private data table that contains a value of Data (Private) in the Type property. Some interface tables are private, but most are public. Use the following guidelines if you add a column to a table:

Any column you add must conform to the data type limitations of all the RDBMS types that your enterprise uses. Consider your server database and any regional or remote databases.
If you add a new column to a predefined table with one or more rows of data, then the RDBMS does not allow you to add the column unless you include a default value.
You cannot remove a column after you add it to a table. For more information, see the documentation for your database technology.
If you add a column to a table, then do not use a column name that includes a word that is reserved on your server or client database. If you use an underscore (_) at the beginning and end of the reserved word, then you can use a reserved word. For more information, see Naming Format for a Siebel Table.
If you create a new extension column in the Siebel schema, then padding problems might occur with Siebel Remote. For more information, see Siebel Remote and Replication Manager Administration Guide.

Guidelines for Creating a Custom Index

You can create a custom index to improve performance. If you create a custom index, then use the following guidelines:

If you create a custom table, then the custom table typically requires new indexes.
Use caution if you create an index. A custom index can result in a situation where objects reference the custom index instead of the predefined indexes. This situation can result in poor performance.
If at some point you no longer require a custom index that you have defined, then do not delete it from the Siebel repository. Instead, you can deactivate it. Make sure the Inactive property of the index contains a check mark.
You must thoroughly test any custom index in a test environment before you implement it in a production environment.
In a DB2 environment, do not create an index that contains a name that is longer than 18 characters.

Guidelines for Creating a LONG Column

If you create a LONG column, then use the following guidelines:

Only one LONG column can exist for each table.
You can add a LONG column only to a one-to-one extension table whose Base Table property includes a valid base table.
You cannot add a LONG column to a one-to-many extension table because it is a Data (Public) table.
You cannot add a LONG column to a Data (Public) table, such as the S_EVT_ACT table. Only Oracle can create a LONG column in a Data (Public) table.
You can use a LONG column to store a maximum of 16 KB or 16383 characters.
Querying a LONG column starts more input and output operations in your RDBMS that are not necessary with other types of column data. This extra input and output increases the time Siebel CRM requires to get each row of data from the database. This increase can add up to a noticeable reduction in performance if Siebel CRM gets many rows of data from the database.
For DB2 on z/OS, use a 32K tablespace if 16K is too small. If 32K is too small, then convert the LONG type to a CLOB type. For more information, see Implementing Siebel Business Applications on DB2 for z/OS.

Guidelines for Modifying a Predefined One-to-One Extension Table

It is strongly recommended that you add custom extension columns to the base table to store your data instead of storing frequently accessed data in columns in a one-to-one extension column. It is strongly recommended that you do not modify a predefined one-to-one extension table or the column of a predefined one-to-one extension table for the following reasons:

Some of the columns that reside in a predefined extension table are not available to use because Siebel CRM uses them. You must not modify or delete an extension column that Siebel CRM uses.
A C++ class might use the extension table in a reserved way. Modifying this table might cause behavior that you cannot predict.
An upgrade effort might use the extension table, so you cannot predict future use of this table.
Docking rules use some extension columns, so these columns are reserved for use with Siebel Remote. For more information, see Configuring Dock Objects for Siebel Remote
Use of an extension table affects performance because Siebel CRM must include the table in all queries that use the field that Siebel CRM uses to run the query. This situation can become a problem if Siebel CRM joins the table to multiple business components, specifically if a number of extension tables are in use.

It is permissible to use a predefined one-to-one extension table in the following situations:

If you must use a LONG column because the database permits only one LONG column for each database table.
If the implementation of a database constraint is beneficial. For example, to realize the improved performance that results when maximum bytes in a row are used before record chaining occurs.

Guidelines for Creating a Custom One-to-One Extension Table

If you create a custom one-to-one extension table, then use the following guidelines:

If you must configure a table whose type is Extension or Extension (Siebel), then you must extend from the base table of the table, not from the extension table. The Base Table property of the extension table describes the base table to extend. For example, the S_CONTACT table is an extension table of the S_PARTY table. The S_CONTACT table is an Extension (Siebel) table, so you cannot use it as the parent table for an extension table. Instead, you can extend the S_PARTY table and use an implicit join to display the data from the extension table.
A custom one-to-one extension table does not require new docking rules because the Siebel schema implicitly routes the data that this table contains according to the docking rules that the parent table specifies.

Guidelines for Configuring a Base Table or Configuring a One-To-Many Extension Table

You can use the following guidelines to help you decide to add an extension column to a base table or to use columns in a one-to-many extension table:

Try to use a predefined one-to-many extension table or column to meet design requirements. They are predefined and already part of the data objects layer so they do not require you to modify the Siebel schema or the physical database. If a predefined extension table or column is not available, then explore other options, such as creating a new extension table.
Add an extension column to a base table if the data you must store almost always exists for a base record, and if Siebel CRM does not regularly access it. This configuration often results in better performance because it avoids the join that an extension table uses. It can result in slower access to the base table if a lot of data exists where numerous large fields are added and where these fields always contain data. In this situation, fewer rows fit on one page.
If a user query regularly includes an extension column, then it is likely that an index is required on the column that Siebel CRM must include on another base table column. You must add it to the base table.
If one-to-many extension fields are required, and if the user only infrequently accesses the view that displays this data, then you can use columns in a one-to-many extension table. In this situation, Siebel CRM uses the join for the extension table, but only if the user accesses this view.

Guidelines for Configuring a Foreign Key That Affects Enterprise Integration Manager

Use caution if you configure an extension column to contain a foreign key. An extension column that contains a foreign key might be appropriate if it references a business object that is visible to the enterprise. You must avoid an extension column that contains a foreign key if it references a business object whose visibility is limited, such as Opportunity, Contact, Account, or Service Request. Using an extension column as a foreign key column can cause problems if Siebel CRM creates an EIM mapping or if it routes data to a remote user.

You cannot configure EIM to import data to a foreign key column because you cannot configure the required EIM object types.

You cannot add an EIM mapping for a foreign key relationship to a table that does not include a user key.

Guidelines for Creating a Custom Docking Rule

If your enterprise uses the Siebel Mobile Web Client, then note that Dock Object Visibility rules determine how Siebel CRM downloads data to the local database. These rules use predefined relationships to identify the data that Siebel CRM uses from the tables to help it route data to the local database that the remote user uses.

If you create a new relationship, then no Dock Object Visibility rules exist that allow Siebel CRM to download relevant data to the local database. This situation might result in a user who cannot view data. To resolve this problem, you can use the Docking Wizard to create custom docking rules for custom foreign keys. To avoid performance problems with the Transaction Processor and Transaction Router, you must analyze how your configuration affects performance before you create a new Dock Object Visibility rule or object.

If you add a rule, then you might inadvertently add a significant number of database records for Remote users. This configuration might affect initialization and synchronization performance. An increased number of records in the Remote database might affect performance.

For more information, see Configuring Dock Objects for Siebel Remote.