Websphere commerce database design observations

Posted by

When you’ve worked with commerce databases for a while there are some design aspects that become obvious.

First, nearly every table has a generated key. Often, but not always, that key is used as the primary key. Most frequently, the column name is the table name with _ID appended. For example, the ORDERS table has a generated key of ORDERS_ID. The ranges used for these generated keys are generally controlled by the KEYS table, though with Commerce 7, they started using a sequence for the STAGLOG table.

Most tables are related to at least two if not more other tables. All Foreign Keys are created with cascading deletes.

You will have dozens if not hundreds of empty tables. Commerce is a tool that can be weilded in many different ways, and most clients don’t make use of 1/10th of the possible features. However, Commerce creates the tables to support most features up front. This is also why data pruning varies significantly from implementation to implementation.

The base indexes are critical, but are by no means comprehensive. Since different clients use Commerce in such different ways, almost every implementation will require additional indexes. And no, I don’t have a set of additional indexes that every client needs.

The attribute model is a royal pain. For some reason, IBM chose to implement the Attribute model in such a way that the ATTRIBUTE table is very likely to be your largest table at the time of go-live.

A dba really is needed to help support Commerce databases. Commerce does not have facilities for basic DB2 maintenance, nor does it have good information on troubleshooting issues with things like stagingprop.

Loading data into Commerce databases is a specialty in itself. You need help in navigating BODL, massload, and whatever other tools Commerce has come up with in this area. Even I don’t try to navigate this without calling in our company expert.

Commerce isn’t Magic. Just by digging through scripts and XML, you can generally get into some pretty good detail on what Commerce is doing against the database – it is all out there in readible SQL somewhere.

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.