Defining the database schema

In this step, you customize the physical layer by adding tables to contain recipe information in the WebSphere Commerce database schema.

About this task

The following diagram outlines the changes to the WebSphere Commerce database schema:

Diagram displaying the Recipes tool schema customization.

Procedure

  1. Start the WebSphere Commerce test server.
  2. Open a new browser window and enter the following URL: http://hostname/webapp/wcs/admin/servlet/db.jsp
  3. In the text box, type:
    Apache DerbyDB2
    CREATE TABLE XPROJECT
(XPROJECT_ID BIGINT NOT NULL,
TIME INTEGER ,
DIFFICULTY INTEGER ,
PRJNAME VARCHAR(64) NOT NULL,
STOREENT_ID INTEGER NOT NULL,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPROJECT PRIMARY KEY (XPROJECT_ID),
CONSTRAINT F_XPROJECT FOREIGN KEY (STOREENT_ID) REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE,
CONSTRAINT I_XPROJECT UNIQUE (PRJNAME)
);

CREATE TABLE XPRJDES (
XPROJECT_ID BIGINT NOT NULL,
LANGUAGE_ID INTEGER NOT NULL,
SHORTDESC VARCHAR(254),
LONGDESC VARCHAR(1024),
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJDES PRIMARY KEY (XPROJECT_ID,LANGUAGE_ID),
CONSTRAINT F_XPRJDES1 FOREIGN KEY (XPROJECT_ID) REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJDES2 FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJMTR (
XPRJMTR_ID BIGINT NOT NULL,
XPROJECT_ID BIGINT NOT NULL,
PRJMTRNAME VARCHAR(64) NOT NULL,
AMOUNT DOUBLE,
QTYUNIT_ID CHAR(16),
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJMTR PRIMARY KEY (XPRJMTR_ID),
CONSTRAINT F_XPRJMTR1 FOREIGN KEY (XPROJECT_ID) REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJMTRDES (
XPRJMTR_ID BIGINT NOT NULL,
LANGUAGE_ID INTEGER NOT NULL,
SHORTDESC VARCHAR(254),
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJMTRDES PRIMARY KEY (XPRJMTR_ID,LANGUAGE_ID),
CONSTRAINT F_XPRJMTRDES1 FOREIGN KEY (XPRJMTR_ID) REFERENCES XPRJMTR(XPRJMTR_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJMTRDES2 FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJMTRCATREL (
XPRJMTR_ID BIGINT NOT NULL,
CATENTRY_ID BIGINT NOT NULL,
STOREENT_ID INTEGER NOT NULL,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJMTRCATREL PRIMARY KEY (XPRJMTR_ID,STOREENT_ID ),
CONSTRAINT F_XPRJMTRCATREL1 FOREIGN KEY (XPRJMTR_ID) REFERENCES XPRJMTR(XPRJMTR_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJMTRCATREL2 FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY(CATENTRY_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJMTRCATREL3 FOREIGN KEY (STOREENT_ID) REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJINS(
XPRJINS_ID BIGINT NOT NULL,
XPROJECT_ID BIGINT NOT NULL,
SEQ INTEGER,
ISOPTIONAL INTEGER,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJINS PRIMARY KEY (XPRJINS_ID),
CONSTRAINT F_XPRJINS FOREIGN KEY (XPROJECT_ID) REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJINSDES (
XPRJINS_ID BIGINT NOT NULL,
LANGUAGE_ID INTEGER NOT NULL,
SHORTDESC VARCHAR(1024),
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJINSDES PRIMARY KEY (XPRJINS_ID,LANGUAGE_ID),
CONSTRAINT F_XPRJINSDES1 FOREIGN KEY (XPRJINS_ID) REFERENCES XPRJINS(XPRJINS_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJINSDES2 FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJCATREL (
XPROJECT_ID BIGINT NOT NULL,
CATENTRY_ID BIGINT NOT NULL,
STOREENT_ID INTEGER NOT NULL,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJCATREL PRIMARY KEY (XPROJECT_ID,CATENTRY_ID ),
CONSTRAINT F_XPRJCATREL1 FOREIGN KEY (XPROJECT_ID) REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJCATREL2 FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY(CATENTRY_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJCATREL3 FOREIGN KEY (STOREENT_ID) REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJCOL
(XPRJCOL_ID BIGINT NOT NULL,
PRJCOLNAME VARCHAR(64) NOT NULL,
STOREENT_ID INTEGER NOT NULL,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJCOL PRIMARY KEY (XPRJCOL_ID),
CONSTRAINT F_XPRJCOL FOREIGN KEY (STOREENT_ID) REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE,
CONSTRAINT I_XPRJCOL UNIQUE (PRJCOLNAME)
);

CREATE TABLE XPRJCOLDES (
XPRJCOL_ID BIGINT NOT NULL,
LANGUAGE_ID INTEGER NOT NULL,
SHORTDESC VARCHAR(254),
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJCOLDES PRIMARY KEY (XPRJCOL_ID,LANGUAGE_ID),
CONSTRAINT F_XPRJCOLDES1 FOREIGN KEY (XPRJCOL_ID) REFERENCES XPRJCOL(XPRJCOL_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJCOLDES2 FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE
);

CREATE TABLE XPRJPRJCOLREL (
XPROJECT_ID BIGINT NOT NULL,
XPRJCOL_ID BIGINT NOT NULL,
STOREENT_ID INTEGER NOT NULL,
OPTCOUNTER SMALLINT,
CONSTRAINT P_XPRJPRJCOLREL PRIMARY KEY (XPROJECT_ID,XPRJCOL_ID ),
CONSTRAINT F_XPRJPRJCOLREL1 FOREIGN KEY (XPROJECT_ID) REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJPRJCOLREL2 FOREIGN KEY (XPRJCOL_ID) REFERENCES XPRJCOL(XPRJCOL_ID) ON DELETE CASCADE,
CONSTRAINT F_XPRJPRJCOLREL3 FOREIGN KEY (STOREENT_ID) REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE
);
    Oracle
    CREATE TABLE XPROJECT 
(XPROJECT_ID NUMBER NOT NULL, 
TIME INTEGER , 
DIFFICULTY INTEGER , 
PRJNAME VARCHAR2(64) NOT NULL, 
STOREENT_ID INTEGER NOT NULL, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPROJECT PRIMARY KEY (XPROJECT_ID), 
CONSTRAINT F_XPROJECT FOREIGN KEY (STOREENT_ID) REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE, 
CONSTRAINT I_XPROJECT UNIQUE (PRJNAME) 
); 

CREATE TABLE XPRJDES ( 
XPROJECT_ID NUMBER NOT NULL, 
LANGUAGE_ID INTEGER NOT NULL, 
SHORTDESC VARCHAR2(254), 
LONGDESC VARCHAR2(1024), 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJDES PRIMARY KEY (XPROJECT_ID,LANGUAGE_ID), 
CONSTRAINT F_XPRJDES1 FOREIGN KEY (XPROJECT_ID) REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJDES2 FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJMTR ( 
XPRJMTR_ID NUMBER NOT NULL, 
XPROJECT_ID NUMBER NOT NULL, 
PRJMTRNAME VARCHAR2(64) NOT NULL, 
AMOUNT NUMBER, 
QTYUNIT_ID VARCHAR2(16), 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJMTR PRIMARY KEY (XPRJMTR_ID), 
CONSTRAINT F_XPRJMTR1 FOREIGN KEY (XPROJECT_ID) REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJMTRDES ( 
XPRJMTR_ID NUMBER NOT NULL, 
LANGUAGE_ID INTEGER NOT NULL, 
SHORTDESC VARCHAR2(254), 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJMTRDES PRIMARY KEY (XPRJMTR_ID,LANGUAGE_ID), 
CONSTRAINT F_XPRJMTRDES1 FOREIGN KEY (XPRJMTR_ID) REFERENCES XPRJMTR(XPRJMTR_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJMTRDES2 FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJMTRCATREL ( 
XPRJMTR_ID NUMBER NOT NULL, 
CATENTRY_ID NUMBER NOT NULL, 
STOREENT_ID INTEGER NOT NULL, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJMTRCATREL PRIMARY KEY (XPRJMTR_ID,STOREENT_ID ), 
CONSTRAINT F_XPRJMTRCATREL1 FOREIGN KEY (XPRJMTR_ID) REFERENCES XPRJMTR(XPRJMTR_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJMTRCATREL2 FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY(CATENTRY_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJMTRCATREL3 FOREIGN KEY (STOREENT_ID) REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJINS( 
XPRJINS_ID NUMBER NOT NULL, 
XPROJECT_ID NUMBER NOT NULL, 
SEQ INTEGER, 
ISOPTIONAL INTEGER, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJINS PRIMARY KEY (XPRJINS_ID), 
CONSTRAINT F_XPRJINS FOREIGN KEY (XPROJECT_ID) REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJINSDES ( 
XPRJINS_ID NUMBER NOT NULL, 
LANGUAGE_ID INTEGER NOT NULL, 
SHORTDESC VARCHAR2(1024), 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJINSDES PRIMARY KEY (XPRJINS_ID,LANGUAGE_ID), 
CONSTRAINT F_XPRJINSDES1 FOREIGN KEY (XPRJINS_ID) REFERENCES XPRJINS(XPRJINS_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJINSDES2 FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJCATREL ( 
XPROJECT_ID NUMBER NOT NULL, 
CATENTRY_ID NUMBER NOT NULL, 
STOREENT_ID INTEGER NOT NULL, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJCATREL PRIMARY KEY (XPROJECT_ID,CATENTRY_ID ), 
CONSTRAINT F_XPRJCATREL1 FOREIGN KEY (XPROJECT_ID) REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJCATREL2 FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY(CATENTRY_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJCATREL3 FOREIGN KEY (STOREENT_ID) REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJCOL 
(XPRJCOL_ID NUMBER NOT NULL, 
PRJCOLNAME VARCHAR2(64) NOT NULL, 
STOREENT_ID INTEGER NOT NULL, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJCOL PRIMARY KEY (XPRJCOL_ID), 
CONSTRAINT F_XPRJCOL FOREIGN KEY (STOREENT_ID) REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE, 
CONSTRAINT I_XPRJCOL UNIQUE (PRJCOLNAME) 
); 

CREATE TABLE XPRJCOLDES ( 
XPRJCOL_ID NUMBER NOT NULL, 
LANGUAGE_ID INTEGER NOT NULL, 
SHORTDESC VARCHAR2(254), 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJCOLDES PRIMARY KEY (XPRJCOL_ID,LANGUAGE_ID), 
CONSTRAINT F_XPRJCOLDES1 FOREIGN KEY (XPRJCOL_ID) REFERENCES XPRJCOL(XPRJCOL_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJCOLDES2 FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGE(LANGUAGE_ID) ON DELETE CASCADE 
); 

CREATE TABLE XPRJPRJCOLREL ( 
XPROJECT_ID NUMBER NOT NULL, 
XPRJCOL_ID NUMBER NOT NULL, 
STOREENT_ID INTEGER NOT NULL, 
OPTCOUNTER SMALLINT, 
CONSTRAINT P_XPRJPRJCOLREL PRIMARY KEY (XPROJECT_ID,XPRJCOL_ID ), 
CONSTRAINT F_XPRJPRJCOLREL1 FOREIGN KEY (XPROJECT_ID) REFERENCES XPROJECT(XPROJECT_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJPRJCOLREL2 FOREIGN KEY (XPRJCOL_ID) REFERENCES XPRJCOL(XPRJCOL_ID) ON DELETE CASCADE, 
CONSTRAINT F_XPRJPRJCOLREL3 FOREIGN KEY (STOREENT_ID) REFERENCES STOREENT(STOREENT_ID) ON DELETE CASCADE 
);
  4. Click Submit Query.
  5. Add entries to the KEYS table for the newly created tables.
    In the text box, type:
    INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER) VALUES ((SELECT MAX(KEYS_ID)+1 from KEYS), 'XPROJECT', 'XPROJECT_ID', 10000);
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER) VALUES ((SELECT MAX(KEYS_ID)+1 from KEYS), 'XPRJMTR', 'XPRJMTR_ID', 10000);
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER) VALUES ((SELECT MAX(KEYS_ID)+1 from KEYS), 'XPRJINS', 'XPRJINS_ID', 10000);
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER) VALUES ((SELECT MAX(KEYS_ID)+1 from KEYS), 'XPRJCOL', 'XPRJCOL_ID', 10000);
  6. Click Submit Query.

Results

The following tables provide additional information about the Project schema, and are provided as a reference:
XPROJECT
This table holds Project information.
Column Name Data Type Description Constraint (PK, FK)
XPROJECT_ID BIGINT NOT NULL The identifier for the project PK
TIME INTEGER The time used for the project
DIFFICULTY INTEGER The difficulty level for the project
PRJNAME VARCHAR(64) NOT NULL The name of this project
STOREENT_ID INTEGER NOT NULL The store which the project belongs to. FK to STOREENT table
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJDES
This table holds language-dependent information related to a project.
Column Name Data Type Description Constraint (PK, FK)
XPROJECT_ID BIGINT NOT NULL The identifier for the project PK, FK to XPROJECT table
LANGUAGE_ID INTEGER NOT NULL The identifier of the language PK, FK to LANGUAGE table
SHORTDESC VARCHAR(254) The short description for the project
LONGDESC VARCHAR(1024) The long description for the project
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJMTR
This table holds material information related to a Project.
Column Name Data Type Description Constraint (PK, FK)
XPROJECT_ID BIGINT NOT NULL The identifier for the project FK to XPROJECT table
XPRJMTR_ID BIGINT NOT NULL The identifier for the project material. PK
PRJMTRNAME VARCHAR(64) NOT NULL The name of the material
AMOUNT DOUBLE The amount of the material
QTYUNIT_ID CHAR(16) The quantity unit of the material.
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJMTRDES
This table holds material-related language-dependent information.
Column Name Data Type Description Constraint (PK, FK)
XPRJMTR_ID BIGINT NOT NULL The identifier for the project material. PK, FK to XPRJMTR table
LANGUAGE_ID INTEGER NOT NULL The identifier of the language PK, FK to LANGUAGE table
SHORTDESC VARCHAR(254) The short description for the material
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJMTRCATREL
This table contains relationships between materials and catalog entries.
Column Name Data Type Description Constraint (PK, FK)
XPRJMTR_ID BIGINT NOT NULL The identifier for the project material. PK, FK to XPRJMTR table
CATENTRY_ID BIGINT NOT NULL The identifier of the catentry FK to CATENTRY table
STOREENT_ID INTEGER NOT NULL The store which the relationship belongs to. PK, FK to STOREENT table
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJINS
This table holds instruction information related to a project.
Column Name Data Type Description Constraint (PK, FK)
XPRJINS_ID BIGINT NOT NULL The identifier for the project instruction. PK
XPROJECT_ID BIGINT NOT NULL The identifier for the project FK to XPROJECT table
SEQ INTEGER The sequence number for the project instruction
ISOPTIONAL INTEGER If the step is optional
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJINSDES
This table holds language-dependent information related to an instruction.
Column Name Data Type Description Constraint (PK, FK)
XPRJINS_ID BIGINT NOT NULL The identifier for the project instruction. PK, FK to XPRJINS table
LANGUAGE_ID INTEGER NOT NULL The identifier of the language PK, FK to LANGUAGE table
SHORTDESC VARCHAR(1024) The short description for the material
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJCATREL
This table hold the relationship between project and catentry.
Column Name Data Type Description Constraint (PK, FK)
XPROJECT_ID BIGINT NOT NULL The identifier for the project PK, FK to XPROJECT table
CATENTRY_ID BIGINT NOT NULL The identifier of the catentry PK,FK to CATENTRY table
STOREENT_ID INTEGER NOT NULL The store which the relationship belongs to. FK to STOREENT table
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJCOL
This table holds information related to a project collection.
Column Name Data Type Description Constraint (PK, FK)
XPRJCOL_ID BIGINT NOT NULL The identifier for the project collection PK
PRJCOLNAME VARCHAR(64) NOT NULL The name of this project collection
STOREENT_ID INTEGER NOT NULL The store which the project collection belongs to. FK to STOREENT table
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJCOLDES
This table holds language-dependent information related to a project collection.
Column Name Data Type Description Constraint (PK, FK)
XPRJCOL_ID BIGINT NOT NULL The identifier for the project collection PK, FK to XPRJCOL table
LANGUAGE_ID INTEGER NOT NULL The identifier of the language PK, FK to LANGUAGE table
SHORTDESC VARCHAR(254) The short description for the project collection
OPTCOUNTER SMALLINT Reserved for IBM internal use.
XPRJPRJCOLREL
This table holds the relationship between project and project collection.
Column Name Data Type Description Constraint (PK, FK)
XPROJECT_ID BIGINT NOT NULL The identifier for the project PK, FK to XPROJECT table
XPRJCOL_ID BIGINT NOT NULL The identifier of the project collection PK,FK to XPRJCOL table
STOREENT_ID INTEGER NOT NULL The store which the relationship belongs to. FK to STOREENT table
OPTCOUNTER SMALLINT Reserved for IBM internal use.