Runtime tools : Service components : Smart channel services : Data management of smart channel : Database design of smart channel : Example
  
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;
Go up to
Database design of smart channel