Defining the database schema

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

About this task

The following diagram identifies the changes to the HCL Commerce database schema. The new tables that you add to the schema in this lesson are highlighted within the image:

Diagram displaying the Recipes tool schema customization.

Procedure

  1. Start the HCL Commerce test server.
  2. Open a new browser window and enter the following URL: http://localhost/webapp/wcs/admin/servlet/db.jsp
  3. In the text box, type: 
    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) );
  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);
  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 that the project belongs to. FK to STOREENT table.
OPTCOUNTER SMALLINT Reserved for HCL internal use.