Most tables have generated primary keys
Commerce tends to use sequential numbers for most primary keys, or as a component of a composite primary key. ORDERS_ID, MEMBER_ID, ORDERITEMS_ID are all examples. Commerce does not use Identity columns or Sequences for these, though. I believe the main reason for this is probably that they change the part of the sequence they’re using for things like large data loads and sometimes even use different parts of the sequence when data is entered on both staging and production.
The ranges for generated keys are controlled by the KEYS table
Since Commerce is not using built-in database tools for maintaining the keys, there is a table called simply KEYS that stores for each table the table name, the next value, and the cache size. If you were doing something fancy with the keys like key-splitting(using different ranges for prod and stage), then you would want to keep this table the same across a cross-environment restore.
Most tables are in a web of RI, being related to at least two other tables, often more
A delete on the MEMBER table will cascade to 300 other tables. While MEMBER is connected to more tables that any other, nearly all tables in Commerce databases are connected to at least two others. Some, like ORDERS, are connected to dozens of other tables. This has obvious implications – you can’t import-replace most tables because of this. If you load-replace, you will put hundreds of tables into check-pending, meaning it is rather nice to have a script designed just to take tables out of check pending.
Foreign Keys use cascading deletes
This is critical for data pruning to function properly. If you create a new Foriegn Key, ALWAYS define it with cascading deletes. I’ve actually seen one defined without cascading deletes cause data pruning to fail at a much higher level. I understand the preference for deletes not to cascade, but this application was designed around this, so in this case, it is important.
There are dozens if not hundreds of empty tables
There are probably a million different ways you could make use of WebSphere commerce, and for those ways, Commerce has already created the tables. Instead of requiring database changes as different options are used, Commerce decided to go this way. The consequence is that you’ll have a lot of empty tables. You can’t drop them, because you never know when a developer will make a minor change that will require their use. Just don’t be surprised if you run a reorgchk and see that there are more empty tables than populated ones.
Base indexes are critical to have in place, but you will need to add more indexes over time
So this was emphasized to me over Cyber Monday 2011. I have a client with their own DBA, and we worked together to identify just 4 indexes that they needed to add in October. Cyber Monday rolls around, and the indexes hadn’t made it into production yet. The site was so slow as to be unusable, the over-sized database server was high on CPU and had over 200 active connections at all times. The other DBA added these 4 indexes, and immediately the active connections were between 5 and 15 (more normal for this database), and the over-sized db server was again bored stiff. That’s just 4 indexes, not even clustering ones. So you should be constantly reviewing SQL and looking for indexes to add. You never want to drop base commerce indexes, but adding indexes is certainly reasonable. See this blog entry on analyzing SQL and this one on identifying problem SQL.
The attribute data model is not ideal
So I’m not saying I know how to do it better – data modeling is not my specialty – but the attribute data model stores all attributes for all products in two giant tables. They are nearly always the largest tables in the database. What are attributes? They are things like size, color, style, etc – information about the products. There are nearly always at least 10 per product. Meaning that these tables will each have at least 10 times the number of rows as your CATENTRY and other tables that store information about products. I don’t have specific action items or warnings on this, but it’s just good to be aware of, as I believe DBAs should be familiar with the largest tables in their databases.
Loading data into Commerce databases is a specialty in itself
So we have a developer who is an expert on this. He knows all the utilities and how to use them – massload, etc. With key generation and normalized data in multiple tables, you can’t just export from your old system and import into a Commerce database. It’s important to understand the intricacies and the utilities. And it’s an area I’m happy to have someone to say “go ask him”. =)
Commerce doesn’t perform magic – it’s just an application
It may seem like a black box sometimes, but it’s not a magic black box. A lot of times you can find the exact SQL it is using, and some processes like stagingcopy/stagingprop are all out there for you to see exactly what they are doing. Sure, DB2 and WebSphere Commerce are both IBM products, but WebSphere Commerce doesn’t do anything with the database that any other application couldn’t do.
