WebSphere Commerce database schema

The WebSphere Commerce database model was designed for data integrity and optimal performance. WebSphere Commerce provides several hundred tables that store WebSphere Commerce instance data. To maintain data integrity, and to ease maintenance referential integrity, constraints are widely used in the database model. Indexes are used carefully on tables to avoid over-indexing and to provide a good balance between data retrieval and data manipulation activities (insert and update). The business rules are implemented at the application level rather than by using database trigger. Triggers, however, are used to facilitate data staging and optimistic locking. A limited number of SQL-based database stored procedures are used for data intensive activities.

You cannot customize the database schema for a . You can use any customizable columns for a database table to meet your business needs. Customizable database columns, such as the FIELD1, FIELD2, FIELD3, FIELD4, and FIELD5 columns for the CATENTRY database table, are identified as customizable within the column descriptions for a database table.

You can extend the schema, the following section lists supported extensions to the WebSphere Commerce schema:

Tables

You can change the physical properties of a database table such as the table space, or physical property change. To support new EJB beans, or JSP pages new tables can be added. When you add a table, consider optimistic locking. After you migrate to a new release of WebSphere Commerce, any customized tables must to be reapplied.

Do not drop or rename an existing table. This action can cause code breakage and migration problems.

If your database schema includes custom tables and your site supports workspaces, ensure that your custom tables are synchronized between your base schema and workspace schema. Synchronize your tables after you modify a table on your authoring server, such as to change a column or index, and when you create a table, regardless of whether your new or changed table is content-managed. Use the appropriate Ant task to synchronize your base schema and workspace schema. You can run the update workspaces schema task when you change your base schema, such as to include a new table, or run the update workspace table task when you change only a table, such as when you modify a column. For more information, see the following topics:

Columns

You can increase the size of a column or change the data type to another compatible type. There might be a performance impact if the size of the column is increased.

To customize, issue DDL statements, then change all JSP pages that use the columns. Data might need to be moved, for example DB2 long to CLOB.

The following changes to columns are not supported:

  • Changing the data type of a column to a non-compatible type
  • Adding a column
  • Dropping a column
  • Renaming a column.

The columns that are prefixed " UP_ " are auto-generated columns to support case-insensitive, search. These prefixed columns are direct copies of the referenced non-prefixed columns, but with the content of the prefixed column in all uppercase characters for use in non-case-sensitive searches. Do not edit these prefixed columns or the content within the prefixed columns. If the contents of the prefixed and non-prefixed column do not match, an error is thrown.

The columns that are prefixed " UP_ " are auto-generated columns for DB2 databases to support case-insensitive, search. These prefixed columns are direct copies of the referenced non-prefixed columns, but with the content of the prefixed column in all uppercase characters for use in non-case-sensitive searches. Do not edit these prefixed columns or the content within the prefixed columns. If the contents of the prefixed and non-prefixed column do not match, an error is thrown.

Primary and foreign keys

Foreign keys can be added between a new custom table and an existing table, or between two custom tables. The cascade delete condition can be changed. If the tables are not used, existing foreign keys can be removed.

The following changes are not supported:

  • Changing primary keys
  • Removing primary keys
  • Changing existing foreign keys
  • Add new foreign keys between existing tables

Indexes

The following changes are supported

  • Adding new indexes.
  • Altering an existing index
    • Adding a column or more to the end.
    • Dropping a column or more.
  • Dropping an existing index.
    Note: If you drop an index, you might encounter a database performance issue when new fixes or features are added to your site. When you apply a maintenance package, you can introduce new database queries that rely on the dropped index in your instance. Without the index in place, the query performance can be negatively impacted. When you do drop an index, ensure that you monitor your database performance closely after you apply a maintenance package. If your database performance is negatively affected, consider adding the dropped index back onto the appropriate table.

The following changes are not supported:

  • Adding unique indexes
  • Changing the uniqueness
  • Functions on columns and indexes.
  • Over-indexing
Note: The Primary Key name for tables can be system-generated. If this name is system generated it displays in the database table descriptions in the following format:
  • <SYSTEM-GENERATED>
Previously, the system generated names display in one of the following formats:
  • DB2SQL<integer>
  • OracleSYS_C<integer>

Triggers

You can customize triggers by using DDL statements and DB procedural language. Any customization to triggers might have a performance impact as the new triggers could slow down DDL statements on other tables.

Additional staging, performance, and business auditing triggers can be added.

The following changes are not supported:

  • Changing existing triggers
  • Changing the timing
  • Dropping existing triggers

Stored procedures

You can add new stored procedures for customized code.

The following changes are not supported:

  • Changing an existing procedure
  • Adding new stored procedure to existing code
  • Dropping an existing service procedure

Functions

The WebSphere Commerce schema does not contain any custom functions. Custom functions are not supported.

You can add column functions such as AVG, Max, and Min.