IBM WebSphere Multichannel Bank Transformation Toolkit, Version 7.1

Database design of smart channel

This chapter introduces the database design of smart channel.

There are five tables to record the data that collected from the three data collectors. Following is the description for each table:

Example

There are three .ddl files to create the tables in the bttsmartchannel.jar for three different databases: DB2: dataCollector_DB2.ddl Oracle: dataCollector_ORACLE.ddl SQLServer: dataCollector_SQLSERVER.ddl.

Use the following commands to create databases that used in smart channel in DB2.

-- ============================================================

-- ============================================================
--   Table: ITEM_LIST                                          
-- ========================= ===================================
create table ITEM_LIST
(
    ITEM_ID        VARCHAR(20)           not null ,
    NAME           VARCHAR(200)                   ,
    DESCRIPTION    VARCHAR(200)                   ,
    constraint PK_4 primary key (ITEM_ID)
);

-- ============================================================
--   Table: CHANNEL_LIST                                       
-- ============================================================
create table CHANNEL_LIST
(    CHANNEL_ID     VARCHAR(50)           not null ,
    NAME           VARCHAR(50)                    ,
    DESCRIPTION    VARCHAR(200)                   ,
    constraint PK_5 primary key (CHANNEL_ID)
);

-- ============================================================
--   Table: VISIT_LOG                                          
-- ============================================================
create table VISIT_LOG
(
    VISIT_ID       VARCHAR(200)          not null ,
    CHANNEL_ID     VARCHAR(50)           not null ,
    USER_ID        VARCHAR(50)           not null ,
    LOGON_TIME     TIMESTAMP             not null ,
    IP_ADDRESS     VARCHAR(20)                    ,
    BROWSER        VARCHAR(50)                    ,
    LOGOUT_TIME    TIMESTAMP                      ,
    constraint PK_3 primary key (VISIT_ID)
);

-- ============================================================
--   Index: VISIT_INDEX                                        
-- ============================================================
create unique index VISIT_INDEX on VISIT_LOG (USER_ID asc, LOGON_TIME asc, LOGOUT_TIME asc);

-- ============================================================
--   Table: BEHAVIOR_LOG                                       
-- ============================================================
create table BEHAVIOR_LOG
(
    BEHAVIOR_ID    VARCHAR(50)           not null ,
    USER_ID        VARCHAR(50)           not null ,
    ITEM_ID        VARCHAR(20)           not null ,
    BEHAVIOR_TYPE  VARCHAR(2)            not null ,
    ACCESS_TIME    TIMESTAMP             not null ,
    VISIT_ID       VARCHAR(200)          not null ,
    constraint PK_1 primary key (BEHAVIOR_ID)
);

-- ============================================================
--   Index: BEHAVIOR_INDEX                                     
-- ============================================================
create unique index BEHAVIOR_INDEX on BEHAVIOR_LOG (ACCESS_TIME asc, USER_ID asc, BEHAVIOR_TYPE asc, ITEM_ID asc);

-- ============================================================
--   Table: PREFERENCE_LOG                                     
-- ============================================================
create table PREFERENCE_LOG
(
    PREFERENCE_ID  VARCHAR(50)           not null ,
    USER_ID        VARCHAR(50)           not null ,    ITEM_ID        VARCHAR(20)           not null ,
    ACCESS_TIME    TIMESTAMP             not null ,
    VISIT_ID       VARCHAR(200)          not null ,
    constraint PK_8 primary key (PREFERENCE_ID)
);

-- ============================================================
--   Index: PREFERENCE_INDEX                                   
-- ============================================================
create unique index PREFERENCE_INDEX on PREFERENCE_LOG (USER_ID asc, ACCESS_TIME asc, ITEM_ID asc);

alter table VISIT_LOG
    foreign key FK_41 (CHANNEL_ID)
       references CHANNEL_LIST (CHANNEL_ID) on delete cascade;

alter table BEHAVIOR_LOG
    foreign key FK_51 (ITEM_ID)
       references ITEM_LIST (ITEM_ID) on delete cascade;

alter table BEHAVIOR_LOG
    foreign key FK_54 (VISIT_ID)
       references VISIT_LOG (VISIT_ID) on delete cascade;

alter table PREFERENCE_LOG
    foreign key FK_45 (VISIT_ID)
       references VISIT_LOG (VISIT_ID) on delete cascade;

alter table PREFERENCE_LOG
    foreign key FK_48 (ITEM_ID)
       references ITEM_LIST (ITEM_ID) on delete cascade;


Feedback