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:
- 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 user logon or logoff from BTT channel,
it records the following field data in this table:
Table 1. VISIT_LOGField 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:
Table 2. PREFERENCE_LOGField 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: 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:
Table 3. BEHAVIOR_LOGField 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.
Table 4. BEHAVIOR_EXTField 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:
Table 5. CHANNEL_LISTField 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:
Table 6. ITEM_LISTField name |
Types |
Property |
Descriptions |
ITEM_ID |
Varchar(50) |
primary key |
The unique ID of the item. |
NAME |
Varchar(200) |
|
The name of the item. |
DESCRIPTION |
Varchar(50) |
|
The description of the item. |
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;