Whether you’re using WebSphere Commerce or some other vended application or using a custom application, there are four basic categories that your tables will fall into. Keep in mind that there WILL be relationships between tables in different categories, but it’s good to know the category of the table for a number of reasons. There could also be hundreds of tables in each category.
People
You could have dozens of tables representing different kinds of people – end-users, guest users, suppliers, customer service representatives, site administrators, infrastructure administrators, etc. Or you could have all types of users stored in a single table. You’ll also have some separation of some attributes of users into separate table, too – how can you store a number of shipping addresses for a registered customer if you don’t have a separate addresses table?
Performance of inserts on these tables is important because they’ll be used both in user registration and in the order/checkout process. So you’ll want to be exceedingly careful when adding indexes – make sure they’re worth it first. And you’ll be less likely to index for index-only access, and more likely to find indexes that will help more than just one query.
It’s also possible that this data will be pulled out to live in a CRM solution or in a data warehouse on a regular basis. Access to these tables should be restricted, as it may constitute identifying data under PCI or just generally be the type of data you don’t want to get out – do you want to be the next company that has to notify its users that personal data has been hacked?
WebSphere Commerce examples in this category:
USERS
MEMBER
ADDRESS
USERREG
Products
Most frequently you’re going to have one large product table with a whole cluster of related tables. Usually there are tables for product groups or categories, for defining relationships between products, for listing attributes of products, and maybe even for defining packages of related products.
Insert performance is not as important here, because most of your inserts and updates are going to be done by a back-end system or by business off-hours. The end users of your website are not going to see the impact of insert performance here. However, there’s going to be a heavy select volume on these tables, especially if your application doesn’t have appropriate caching in place, and the performance of those selects is going to be important.
You’re more likely to have exactly the same query executed over and over again here, so indexing for index-only access is more likely to be worth it. You will see both individual item queries and queries for groups of items based on various attributes, depending on your search solution.
WebSphere Commerce examples in this category:
CATENTRY
CATENTREL
CATGROUP
ATTRIBUTE
Orders
You’ll likely have an orders table with a status to indicate where in the process an order is, along with an orderitems or order detail table to list all of the products on the order. You may also have a table for suborders or backorders, and will likely also have tables for payment information and for the configuration/use of any promotions.
Insert performance is absolutely critical here as inserts are done during your checkout process, so you want to be especially careful with your indexes, avoiding looking for index-only access and opting instead for indexes that are likely to help many different queries.
Access to these tables is largely to a single row or to a small subset of rows with a high cardinality index – only reporting would be likely to summarize data or query many rows. Promotion tables can cause particular concurrency changes, as you’re likely to have a high volume of people trying to use a particularly good promotion code at the same time, and large emails of such codes can lead them all to be at the same time.
WebSphere Commerce examples in this category:
ORDERS
ORDERITEMS
SUBORDERS
PX_PROMOTION
Configuration
This is the most diverse category of the 4 – ranging from small infrequently accessed tables which store application version information to tables storing language information, scheduled jobs within the application and even tables that are used by the application for data movement.
Because of the variety, it is a bit harder to generalize on these tables. Just remember that even a 5-row table can benefit from an index if it is frequently accessed.
WebSphere Commerce examples in this category:
SITE
STORENT
SRCHCONF
SCHCONF
SCHSTATUS
STGMERTAB
STGSITETAB
In Summary
Sometimes it is hard to know every table in your database, and if you can place them in one of these categories, it can be helpful for knowing what kind of indexing is appropriate. These apply to both vended and custom databases.
