This chapter introduces the database design of smart channel.
There are five tables to record the data that collected from the three data collectors.
VISIT_LOG
This table records the data that related the logon, logout, channel information. This table is frequently updated, and unlikely to be deleted. When a user logs on or logs off from the UDTT channel, the field data specified in the following table is recorded:
Field name
Types
Property
Descriptions
VISIT_ID
Varchar(200)
primary key
This data is obtained from the session id.
USER_ID
Varchar(50)
The user logon ID
LOGON_TIME
Timestamp
The logon time
CHANNEL_ID
Varchar(50)
foreign key
The channel type that the user is signing, for example, mobile channel, internet bank channel. This is a foreign key from table “CHANNEL”, which records detailed information about the channels.
IP_ADDRESS
Varchar(20)
The IP address
BROWSER
Varchar(50)
The browser type that the user is using
LOGOUT_TIME
Timestamp
The time that user logout or the session expires.
PREFERENCE_LOG
This table records the data that related to the interested items of users in the channel. Data is frequently added to this table. This table is unlikely to be updated or deleted. When one user changed the customized services and save the settings, it records the following field data in this table:
Field name
Types
Property
Descriptions
PREFERENCE_ID
Varchar(200)
primary key
It's a composition key of USER_ID, ACCESS_TIME, and ITEM_ID. It's gotten from getPreferenceLogId method of SmartDataCollectUtil.
USER_ID
Varchar(50)
The user logon ID. It's designed for searching performance improvement when find by user.
ITEM_ID
Varchar(50)
foreign key
This is a foreign key from table “ITEM_LIST”, which records detailed information about the items.
ACCESS_TIME
Timestamp
The time that the user save the customized items.
VISIT_ID
Varchar(200)
foreign key
This is a foreign key from table “VISIT_LOG”, which records detailed information about the visit.
BEHAVIOR_LOG
This table records the data that related to the user behavior in the channel. Data is frequently added to this table. The table is not frequently updated. Database administrator should archive or remove the data to improve the performance. When one user behaviors on one item, it records the following field data in this table:
Field name
Types
Property
Descriptions
BEHAVIOR_ID
Varchar(200)
primary key
It's a composition key of USER_ID, ACCESS_TIME and ITEM_ID.
VISIT_ID
Varchar(200)
foreign key
This is a foreign key from table “VISIT_LOG”, which records detailed information about the visit.
USER_ID
Varchar(50)
The user logon ID. The field is consistent with the user ID contained in the “VISIT_ID” field. This field is designed for performance consideration.
ITEM_ID
Varchar(50)
foreign key
This is a foreign key from table “SERVICE”, which records detailed information about the services.
BEHAVIOR_TYPE
Varchar(50)
It marks the types of behavior: 0:view 1:operate
ACCESS_TIME
TimesStamp
primary key
The time that the user do the behavior.
BEHAVIOR_EXT
Behavior collector supports collecting the extensive transaction-specific data. This table records the extension data that related to the transaction behavior.
Field name
Types
Property
Descriptions
ID
Varchar(200)
primary key
It's a composition key of BEHAVIOR_ID and current time.
NAME
Varchar(200)
The name of the extension transaction behavior data.
VALUE
Varchar(200)
The value of the extension transaction behavior data.
BEHAVIOR_ID
Varchar(200)
foreign key
This is a foreign key from table “BEHAVIOR_LOG”, which records detailed information about the behavior.
CHANNEL_LIST
This table records the data about the channels. Add or update operation is unlikely to executed in this table. This table is unlikely to be deleted. This table contains following fields:
Field name
Types
Property
Descriptions
CHANNEL_ID
Varchar(50)
primary key
The unique ID of the channel
NAME
Varchar(50)
The name of the channel
DESCRIPTION
Varchar(200)
The description of the channel
ITEM_LIST
This table records the data about the business items. Add or update operation is unlikely to executed in this table. This table is unlikely to be deleted. When the users add new business item to the bank channel, one new record is added. This table contains following fields:
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;
