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 Commerce 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 through the use of 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 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 tablespace, or physical property change. To support new EJB beans, or JSP pages new tables can be added. When adding a new table consider optimistic locking. After migrating to a new release of WebSphere Commerce any customized tables will need to be reapplied.

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

 

Columns

You can increase the size of a column or change the data type to another compatible type. Note that 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:

 

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:

 

Indexes

The following changes are supported

The following changes are not supported:

 

Triggers

You can customize triggers using DDL statements and DB procedural language. Note that customizations 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:

 

Stored procedures

You can add new stored procedures for customized code.

The following changes are not supported:

 

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, Min.

Related concepts

Database commits and rollbacks for controller commands
Primary keys
Extending the WebSphere Commerce object model

Related tasks

Create primary keys
Finding data using an access bean

Related reference

Column type differences between databases