Customizing the WebSphere Commerce schema

In this lesson, you customize the physical layer by creating a table to store the data that is required for the day-of-month schedule type, the new schedule type, and the selected day-of-month.

The following diagram outlines the changes to the WebSphere Commerce schema:
Subscription noun

The diagram shows the new XSUBSCRSCHTYPE table and how it relates to the existing WebSphere Commerce SUBSCRIPTION table. The XSUBSCRSCHTYPE table has a foreign key to the SUBSCRIPTION table. This key allows the Data Service Layer (DSL) to populate data from the XSUBSCRSCHTYPE table in the user data element of the ScheduleInfo noun part.

When you add custom database tables, take into account the following restrictions:

The custom tables must have a foreign key to the base table for the noun or noun part that contains the data. A base table is a table that contains the unique key for the noun or noun part. For example, the SUBSCRIPTION table is the base table for the ScheduleInfo noun part. These relationships are used when fetching or updating a noun or noun part to access the custom data.
The relationship between the custom table and the base table must be a one-to-one relationship. User data is essentially a map of name-value pairs. Each name represents a database column name and each value is the value in that database column. One-to-many relationships are not supported as the names in the map would collide.

Notes:

If you are using workspaces, you must run the utility to enable the tables. For more information, see Ant target: CM_updateWorkspacesSchema.
All foreign key relationships in the custom tables specify ON DELETE CASCADE.

Procedure

Connect to the development database.

Create the custom table by running one of the following SQL statements:

CREATE TABLE XSUBSCRSCHTYPE(
SUBSCRIPTION_ID BIGINT NOT NULL,
SCHTYPE VARCHAR(25),
DAYOFMONTH SMALLINT,
OPTCOUNTER SMALLINT,
CONSTRAINT XSUBSCRSCHTYPE_PK PRIMARY KEY (SUBSCRIPTION_ID),
CONSTRAINT XSUBSCRSCHTYPE_FK FOREIGN KEY (SUBSCRIPTION_ID)
REFERENCES SUBSCRIPTION(SUBSCRIPTION_ID) ON DELETE CASCADE
);

CREATE TABLE XSUBSCRSCHTYPE(           
SUBSCRIPTION_ID NUMBER NOT NULL, 
SCHTYPE VARCHAR(25), 
DAYOFMONTH SMALLINT, 
OPTCOUNTER SMALLINT, 
CONSTRAINT XSUBSCRSCHTYPE_PK PRIMARY KEY (SUBSCRIPTION_ID), 
CONSTRAINT XSUBSCRSCHTYPE_FK FOREIGN KEY (SUBSCRIPTION_ID)
REFERENCES SUBSCRIPTION(SUBSCRIPTION_ID) ON DELETE CASCADE 
);