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.
