Database design of smart channel

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.

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.

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 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.

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

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:

Field 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.