10 Commerce Tables you should be familiar with

UPDATE: An updated version of this post is available – Updated: 10 WebSphere Commerce Tables You Should Be Familiar With

So there are 800+ tables in the typical Commerce database – you’re never going to know them all. And getting a start on understanding the data model – even in the more limited way needed when you’re playing primarily the system dba role can be daunting. But developer and dba alike should at least be familiar with these 10.

Note all table details are copied from the WebSphere Commerce 7 Info Center.

MEMBER

So maybe I’m just a geek, but I think of this as “one table to rule them all”. The main reason being that almost every table in the database has a column that has a foreign key to either Member or Users. Deleting a row from one of these tables easily cascades to 300 tables. Try explaining a delete from member sometime if you’re bored – the results are amusing.

USERS and MEMBER are closely related. Every USER has a single MEMBER, but not every MEMBER has a USER. USERS.USERS_ID is a foreign key to MEMBER.MEMBER_ID. Most tables reference the USERS_ID.

The Commerce Info Center tells me there are currently 99 Foreign Keys referencing the MEMBER table.

Generally, MEMBERs that do not have USERS associated with them should not be deleted – they are part of the Commerce system and not actual end-users.

I’ve got one of the pruning areas for this table documented – Guest Users – but there are others.

USERS

Usually the number of USERS is within a hundred of the number of MEMBERs. Some of the system users are in here, and some don’t have entries. All of the site users are represented here (assuming you don’t use AD tie-in, I have no idea what that looks like). The USERS table has 25 Foreign Keys referencing it.

USERREG

The USERREG table is one of the places you can connect identifying data with the less-informative sequential ids used in the USERS and MEMBER tables. The LOGONID column usually represents the email address of the user, and passwords are stored here in an encrypted format. You can reset the configadmin password here manually (by also using the encryptPW function that Commerce provides). As a side note, for other IDs, you should not have to reset passwords for other users – that should be done through the Commerce tooling.

ORDERS

As the name implies, this table holds orders placed in the database. It also includes shopping carts (orders that have not been submitted yet). Note especially the STATUS column – this can tell you where in the process the order is – submitted, completed, shipped, etc. There are a limited set of values and you should understand what they mean in your database.

The total $ is stored here, which is nice when you need to look for $ of orders during certain periods or find the largest orders by $.

Note that many of the columns here reference other tables to pull in the details – 9 of the columns are Foreign Keys to other tables.

ORDERITEMS

For every item on an order, there is a row in the ORDERITEMS table. This is a common structure in OLTP databases, but may not be as familiar to those used to working with DW/DSS databases. Much like orders, there are many Foreign Keys pulling in data from other tables – 14 columns reference other tables. The CATENTRY_ID can help connect the ORDERITEM to an item in the catalog.

Sometimes you’ll need to look for ORDERS with the most items – ORDERS with large numbers of items can cause performance issues. For example, you might have a monitor that tries to add an item to a cart. If you haven’t also implemented proper data pruning, that monitoring process could be adding items to the same ORDER over and over and never checking out – this can cause site-wide performance issues.

Pruning junk ORDERITEMS is also one of the more critical data pruning areas.

STAGLOG

The STAGLOG table holds all of the changes captured by stagingprop to be copied from the staging database to the production database. It is only populated in a staging-role database and not in a production-role database. You’ll find many more details on it in my entries on stagingprop and on dbclean for staglog.

CATENTRY

This is one of the master tables in the catalog. And when I say catalog, I mean the list of items the site/store sells, not the DB2 System Catalog. There are plenty of others in this data model, and you may get familiar with them depending on the work needed.

ATTRIBUTE

In many of the databases I support, the ATTRIBUTE and ATTRVALUE tables are the largest. I can see some of why IBM chose the ATTRIBUTE data model, but it makes for very large tables. The ATTRIBUTE table can hold many rows for each item in the catalog. An ATTRIBUTE might be “size” or “color”, each listed along with the catentry_id it applies to, and with the value (the actual color or size) store in the ATTRVALUE. If you were to add one attribute for all items, it can be millions of rows, depending on the number of items in your catalog.

KEYS

Nearly every Primary Key in a Commerce database is a generated key. Up through Commerce 6, all of those values came from this table – each table with a generated key had a row in KEYS, and Commerce would query the KEYS table to get the right value on insert. In Commerce 7, they started making more significant use of sequences, but the KEYS table is still used for some things.

Often as data is loaded into a new Commerce database, different ranges are chosen to avoid key conflicts. There are also some interesting articles out there on key-splitting and what scenarios you might use that in. I have a few developers who call me every time they get SQL0803, and tell me to check the keys entries for the tables they’re working with. It’s rarely the problem, but it is possible for the value in the keys table to get behind the values in the actual table and end up with PK conflicts as a result. The solution if that happens is simply to increase the value of the COUNTER for that table.

SITE

This table is not one that Commerce itself uses a lot, but it does store the version and FixPack and Feature Pack information, so can be very useful if you somehow have a mismatch between the Commerce application and the Commerce database, or if you need to verify Commerce versions at the database level. It is not one you will generally update.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home