Altering database objects in Commerce databases
Like many vended applications, Commerce creates the whole schema of it’s database and this is largely something you don’t mess with. There are, however, some changes that developers or administrators may make. My suggestion is that if you don’t know if a particular modification is supported by Commerce, you call IBM support and ask them. Then if you end up having issues you can reference the PMR/conversation to ensure they don’t blame you for the problems.
This part of the info center talks about what changes are allowed and not:
Essentially you can:
- Extend the length of a varchar column
- Change the data type of a column to a compatible data type (i.e. Varchar to CLOB)
- Add custom tables
- Add new Foreign keys involving custom tables
- Adding new indexes
- Add new triggers
- Add new Stored Procedures for custom code
The most common thing you might use these scripts for is dropping and creating the Staging triggers.
How to track it
If you read the info center closely, it tells you that any changes to the schema are lost on upgrade. I find that many clients will re-do their website at the same time they do a Commerce upgrade, meaning that most modifications are null and void anyway. But if you don’t, you’ll likely find modifications missing.
Having your own naming standards will help you identify custom objects. Generally, custom tables start with an ‘X’. Since Commerce indexes all have either system default names (for the primary keys) or are of the format INNNNNNN where the N’s are replaced with digits, of course. For indexes I create, I like them to include the table name.
As with any coding, it is good to have a code repository to track your changes. Simply storing the SQL scripts you’ve used to make the changes.
Things you should specifically not do:
Never add a unique index on the Member table – this is specifically stated as unsupported
Never add a trigger without cascading deletes. If you add one with “on delete restrict”, you may not be able to delete objects (such as users or orders).
Avoid dropping base commerce indexes – they’re often needed for things that Commerce is likely to do.
Don’t over-index. Consider the impact of new indexes – in OLTP applications each index slows down an insert, so consider the importance of the positive impact indexes will have.
Don’t forget, when adding a custom table, to also add foreign keys, primary key, and indexes for expected activity. Many performance problems stem from custom tables that were added without these things in place.