So I have several posts already on this topic. It’s a big topic, and I imagine I’ll write many more posts on it. But I thought something kind of general would be good.
I’ve talked about 10 Commerce Tables You Should Be Familiar With, and about making alterations to Commerce objects, and about general observations about Commerce databases. The main purpose of this post is to give you links to places in the WebSphere Commerce Info center that talk about the data model and that may be useful.
The data model in the infocenter
I generally love IBM Infocenters – both for DB2 and for WebSphere Commerce. The only problem is that they’re not always great for newbies. If you know their structure and know what you’re looking for, then the info is there in great detail, but they’re not the sort of thing you just sit down and read through to get an education on the product.
You can find the entirety of the Date Model for a WebSphere Commerce database in the WebSphere Commerce Info center. This includes data model diagrams and a detailed page on each and every table that includes all columns in a table with some details about their purposes, and a list of all base indexes, all foreign keys that reference the table and all foreign keys that the table uses to reference other tables. What’s more, all of this information is nicely linked, so if you’re looking at a table and you want to get more details on a table that references it, you can simply click on the table name.
Details of the data models
So the master page of the data model is: http://www.ibm.com/support/knowledgecenter/SSZLC2_7.0.0/com.ibm.commerce.data.doc/refs/rdmindex.htm. This page contains links to some of the individual data models.
The problem is that the data model is huge. You’re talking 900+ tables. So there isn’t one diagram to start with. I pulled it into Visio once, and it took a long time and finally I ended up with this spaghetti of lines and tables. It’s just not reasonable to look at it as a whole, due to the number of tables, and even more to the number of relationships. So what IBM did was break it up into models by topic. And the topics are Business concepts – so it’s broken down into a number of major categories, some of which are then broken up into minor categories.
For example, on the page above, click on “Member data models” and you get another page, which lists “Access control data model”, “Access logging data model”, “Authentication data model” and “Member data model”. Click on any of those and you get a data model which includes anywhere from 4 to about 40 tables. A table can be in multiple data models – you’ll find MEMBER or USERS in most of them.
Reading the data models
So to my eyes, anyway, the data models don’t use very standard notation. Because of this, they have a page on reading the data models: http://www.ibm.com/support/knowledgecenter/SSZLC2_7.0.0/com.ibm.commerce.data.doc/refs/rdmlegend.htm. I’ll mostly let that page speak for itself. One thing that may not be obvious is that on any of the data models, you can click on the name of a table to be taken to the detailed page for that table.
Table detail pages
You can get to the detailed page for a table by clicking on the table name in a data model, by clicking on a table name in the foreign key definitions on a different table detail page, or by searching on the table name and the keyword “table”. Some of the searches, you’ll have a hard time getting the table page you want because there are so many results for say “ORDER” – in that case, I generally find a related table detail page, and then click through using one of the foreign keys.
When you’re looking at a table detail page, you’ll see several tables of data about the table. The first is a listing of columns. Look carefully at the “Description” column. This column contains detailed data about the column, including in many cases the range of values and what they mean.
The second table is a list of indexes. Each of these first two tables have no clickable links as they are data strictly about the table in question.
The remaining two tables, “Referenced by” and “Constraints”, list the details of foreign keys either on this table or that this table uses to reference other tables. Each row has a table name that you can click on to get the details on the table in question.
Finally, at the bottom of the page you’ll see a list called “Related reference” – this list will include all the data models that this table is a member of.
How to use this information
Overall, you’re not going to just sit down and read through all of the data models. 900 tables with umpteen million foreign keys is just too much to memorize right off the bat. Mostly, you’ll refer to the info center as you go through issues/indexes. There are some that with time you’ll have memorized. I rarely have to look at the STAGLOG page anymore – as I know it by heart. But I do have to refer back to most of them because I can’t remember the column names or can’t remember what the various statuses mean – things like that. If you’re looking for a starting place, I really recommend starting with my own posts on 10 Commerce Tables You Should Be Familiar With and general observations about Commerce databases.
