Archive database schema for messaging 

---

Types of data stored in the database

The database is designed to store and connect the following information about each message:

• Processing Agent�the agent handling the message call

• Received Message Data�the original message including tracking number, subject, text, header information, and file attachments

• Current State�the status of the message

• Responses Sent/Outbound Data�the outbound messages (does not include auto-acknowledgments) created by agents and sent to the customer or messages forwarded to SMEs within your organization

• Responses Received�responses from SMEs and the customer

• Agent Notes�notes created by an agent

• Message History�history which includes timestamps and, where relevant, agent IDs for each event

• Agent �Workbench�� stores messages saved by the agent.

Structure of the message archive database

The message archive database has the ODBC Data Source name �Message Care SQL Archive.� Within the message Database, message data is organized in the following tables:

The Messages Table contains �Received Message Data� and �CurrentState� information. It also has fields (as marked with �%�) needed to facilitate reporting and responses.

The messaging software assigns unique message tracking numbers to each message and inserts them in the subject line of any subsequent reply or forward messages. In this manner, all messages generated in response to the original message inherit the original tracking number (assuming the subject line is not manually modified by the sender). Provided the tracking number remains intact in the subject line, both the original message and all ensuing reply and forward messages will be linked on the basis of their shared tracking number.

Responses are distinguished from original messages by the fact that the Original Message flag is set to �No� for responses. In addition, other fields (for example, State) are not set for response messages, but are set in the corresponding Original Message.

The following table describes each database item found in the Messages Table:

The Attachments Table stores attachments associated with the message. Since there may be zero or more attachments in a message, they are stored in a separate table and linked by MsgID. The AttachCount in the Messages table serves to �flag� the existence of attachments.

The following table describes each database item found in the Attachments Table:

The Outbound Table stores outbound data (forwards or replies created by agents).

The following table describes each database item found in the Outbound Table:

The Notes Table stores notes created by an agent.

The following table describes each database item found in the Notes Table:

The Events Table contains data corresponding to the Message History object.

The following table describes each database item found in the Events Table:

The Workbench Table stores saved versions of composed replies associated with the message. Since there may be 0, 1, or 2 of these per message and they may be large, they are each stored in a separate table and linked by MsgID. The WorkBench field in the Messages table is a �flag� as to whether there is a previously saved response. The layout of this table is very similar to the Outbound Table (minus the AgentID and Timestamp).

The following table describes each database item found in the Workbench Table

Important!

Information about database customization refers only to the archive database. Under no circumstances should you attempt to edit the active messages database.