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:
- Change the data type of a column to a non-compatible type
- Add a column
- Dropping a column
- Renaming a column.
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:
- Change primary keys
- Remove primary keys
- Change existing foreign keys
- Add new foreign keys between existing tables
Indexes
The following changes are supported
- Add new indexes.
- Altering an existing index
- Add a column or more to the end.
- Dropping a column or more.
- Dropping an existing index.
The following changes are not supported:
- Add unique indexes
- Change the uniqueness
- Functions on columns and indexes.
- Over-indexing
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:
- Change existing triggers
- Change the timing
- Dropping existing triggers
Stored procedures
You can add new stored procedures for customized code.
The following changes are not supported:
- Change an existing procedure
- Add 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, Min.
Related concepts
Database commits and rollbacks for controller commands
Primary keys
Extending the WebSphere Commerce object modelRelated tasks
Create primary keys
Finding data using an access beanRelated reference
Column type differences between databases