Reusing Tables versus Creating a New Table Definition

Sometime we have to go for creating a custom business component because no existing business component is suitable or match or requirement and alos have to decide whether to go with existing table or create a new one. It will always be preferred to use the custom table for the functionality because of some of the below reasons :

1. The table's indexing is tuned for its originally intended use. Using it for alternative purposes may result in performance.

2. The table's user key and unique indexes may not suit your requirements. Inappropriately populating user key columns may compromise the uniqueness of the record, performance, as well as Siebel EIM. 3. The dock object visibility rules may not suit your intended use. Misusing the table may compromise Siebel Remote. If a table is reused inappropriately, it may make it difficult to reuse that table in the future for its original purpose. When you decide to implement out-of-the-box list management functionality, unrelated data will be displayed in the views. The addition of search specifications to remove this data may compromise performance that may or may not be fixable with the addition of indexes. The following are some factors that may influence your decision to reuse versus create a new table definition.


1. Overloading tables. Occurs when you reuse the same table multiple times on different business components and each business component is "typed" with a search specification. For example, you may choose to use the S_EVT_ACT table to store regular activities, audit logs, error logs, messages, EAI logs, and so forth. When you decide to overload a table, it is often necessary to add a search specification against a "Type" field to prevent data from one business component displaying in another. Overloading of tables may cause several issues:

a. The search specification used to "type" the table into various business components may cause performance issues. Often, the table is not designed to be overloaded. For example, on the S_EVT_ACT table, the TODO_CD column, which is often used for "typing" the table, is not denormalized on to the S_ACT_EMP activity/employee intersection table and queries using SalesRep visibility against a business component based on the S_EVT_ACT table may result in compromised performance.

b. There is no guarantee that the addition of indexes against these "type" columns will resolve any performance issues because adding one may compromise performance elsewhere. Again, the fact that the "type" columns are often not denormalized onto position, employee, or organization intersection tables will have an impact on queries in certain views. c. Overloading of tables increases the table size. NOTE:  Some tables in the Siebel repository have been built to be overloaded. For example, in Siebel Industry Applications, the S_ASSET table uses the TYPE_CD column to "type" various business components. This column is denormalized and indexed onto the S_ASSET_POSTN and S_ASSET_BU intersection tables to aid performance in SalesRep and Organization visibility views. Also, XM tables such as S_ORG_EXT_XM are built for overloading.

2. 1:1 mapping. Siebel EIM assume that the PAR_ROW_ID and ROW_ID columns on these tables are equivalent and that the PAR_ROW_ID column points to a valid parent table record.

3. New 1:1 tables mapping. There are rare instances when you would need to create a new 1:1 table. In most cases, you would simply extend the base table or reuse an ATTRIB column on a 1:1 _X table. However, you may need to create a new 1:1 table to add LONG columns, since they cannot be added to base tables and only one LONG column is supported on a given table.

4. New 1:M tables. In most cases you would simply reuse an existing XM table to support a 1:M relationship off a base table. The following guidelines should be followed when using an XM table:

a. There are very few cases where you would need to create a new XM table. XM tables are already tuned to support large data volumes and of multiple data types.

b. In some instances, a base table does not have any XM tables. In that case we can go forward with creating the custom table with the similar functionality like XM table.



5. Intersection tables. You should reuse an intersection table only where it is a true intersection between the two tables. The table should also be of type "Data Intersection". You should not use a non-intersection table as an intersection table just because it contains foreign keys to the desired tables. This results in an overloading of the table. Also, 1:M XM tables should not be used as intersection tables. Performance of an XM table is not tuned with this in mind, and using it as an intersection table may cause performance degradation. In addition, a custom foreign key will most likely need to be created to support one side of the relationship, resulting in issues with Siebel Remote and EIM. Where no suitable intersection table exists between two tables and one is required, it would be necessary to use DB Extensibility to create it. Custom party types. The S_PARTY table should not be reused to support custom party types. The S_PARTY.PARTY_TYPE_CD column only supports one of the following: AccessGroup, Household, Organization, Person, Position, UserList

6. Repository tables. Repository tables should not be misused by the developer. Repository tables are those that are used by business components prefixed with "Repository."

3 comments on “Reusing Tables versus Creating a New Table Definition”

  1. Sean McKeown Reply

    Certainly an interesting post.

    Well in my experience…

    DBs like Oracle work at the block level.

    So if a the table is re-used via several BCs if the BCs are both references heavily then having the blocks in Oracle’s buffer cache will be advantageous. If however the BSc are not references in tandem often, lets say mutually exclusive, then there is disadvantage in polluting Oracle buffer cache.

    The are other considerations like the effect on triggers, remote routing etc etc

  2. Gopal Raturi Reply

    Really Mandeep it is the knowledge earns with experience and very informative for learner.
    Keep it up.

Leave A Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!