The Database Structure (Data Model)
The database holds information about all call and agent related activities which have relation to the Puzzel Contact Centre solution.
There are two “main” tables holding all the events, agent_events and call_events. In addition, there is a set of “basic tables” supporting the two event tables. These basic tables contain more descriptive information about codes, ids, and values used in the event tables.
The call_events table holds a set of records for every call/chat/email/social media request handled by Puzzel, and it also expresses the duration and outcome (result) of each of these events. All events belonging to the same call are related to each other through the column call_id.
The agent_events table holds information about every so-called “agent driven contact centre events”, i.e. every time the agent is logging on or off, or is going to or from pause. In addition, every call/call attempt which agents are involved in will be stored here (as well). It’s important to know that, except from the call/call attempt event, all events in this table will have a duration of 0 (i.e. the time it takes to active the action) – exactly as in Puzzel’s central systems (which is the source for all the data stored in the tables). Though, a set of functions/procedures calculating the duration of all events in this table will be made available in the database (described separately). Those functions/procedures will also be used to calculate other useful values, as well as “generating” events for wrap up and availability.
In order to make the tables easy and faster to work with, some data are de-normalised (i.e. stored in both event tables). This is true for those Conversation events in the call_events table having agents involved – such events will have some of the same columns copied over to the agent_events table.
On the next page a graphical layout of the database model is shown. On the pages there after the content of each table are described in detail.