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.

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

• Tables

• Columns

• Primary and foreign keys

• Indexes

• Triggers

• Stored procedures

• Functions

Tables

We can change the physical properties of a database table such as the table space, or physical property change. To support new JPA entities, 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 the database schema includes custom tables and the site supports workspaces, ensure that our custom tables are synchronized between your base schema and workspace schema. Synchronize your tables after you modify a table on your authoring environment, such as to change a column or index, and when creating 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. We 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. See the following topics:

• Ant target: CM_updateWorkspacesSchema

• Ant target: CM_updateWorkspacesTable

Columns

We 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 for DB2 databases to support case-insensitive, Management Center 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, we might encounter a database performance issue when new fixes or features are added to the site. When you apply a maintenance package, we 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 the database performance closely after you apply a maintenance package. If the 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:

• (DB2) SQL<integer>

• (Oracle) SYS_C<integer>

Triggers

We 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

We 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.

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

• Database column data types considerations
  Several column data types are available for you to use when we are creating new database table or columns.

• Database schema changes from Version 8 to Version 9
  Database tables and columns within the WebSphere Commerce Version 9 database schema can differ from the tables and columns in the database schema for WebSphere Commerce Version 8.

• Deprecated tables and columns
  If a database table or column is marked as deprecated, then the table or column is outdated by newer constructs. Deprecated sections are supported for reasons of compatibility with an earlier version only. These tables or columns are obsolete or can become obsolete in future versions of WebSphere Commerce.

• Data models
  Any given database data model displays the relationship among database tables in the schema.

• Database tables
  The WebSphere Commerce database includes many database tables for storing data about business objects, users, shoppers, and more. Review the table and column descriptions for these database tables to understand the data that can be stored in the database.