PCM Raw data Basic tables
The PCM Raw data basic tables are:
- cm_category_choices
- cm_channels
- cm_customers
- cm_forms
- cm_organisations
- cm_parent_sub_tickets
- cm_team_queues
- cm_teams
- cm_templates
- cm_tenants
- cm_users
cm_category_choices
Contains/used for: All tickets may (or may not) be related to categories, for which a set of category choices are available - and can be chosen/tagged. What's chosen is stored in this table.
|
Column name |
Data type |
Description |
|
category_choice_id |
bigint |
Identifier for the category field choice |
|
category_id |
int |
Identifier for the category field |
|
ticket_id |
bigint |
Ref. to the ticket, foreign key to cm_tickets. |
|
tenant_id |
int |
Ref. to tenant, foreign key to cm_tenants. |
|
category_name |
nvarchar(200) |
Name given to a category field |
|
category_value |
nvarchar(1000) |
Value of the populated category field |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |
|
ticket_number |
int |
Ticket reference number |
cm_channels
Contains/used for: <...to be filled out....>
|
Column name |
Data type |
Description |
|
channel_id |
int |
Unique ID of the channel |
|
tenant_id |
int |
Ref. to tenant, foreign key to cm_tenants. |
|
channel_type |
nvarchar(200) |
SMS / Email / API |
|
name |
nvarchar(1000) |
Name applied to the channel |
|
dte_created_at |
datetime2 |
The date/time the channel was created |
|
dte_updated_at |
datetime2 |
The date/time the channel was last updated |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |
cm_customers
Contains/used for: All the external customers/contacts for a tenant (= a Puzzel customer) is represented with a record in this table. All tickets (in table cm_tickets) will refer to such a customer through the customer_id column.
|
Column name |
Data type |
Description |
|
customer_id |
bigint |
Unique ID of the customer record |
|
tenant_id |
int |
Ref. to tenant, foreign key to cm_tenants. |
|
name |
nvarchar(1000) |
Name of the customer |
|
dte_created_at |
datetime2 |
Date/Time of the customer record creation |
|
dte_updated_at |
datetime2 |
Date/Time of the customer record last update |
|
dte_deleted_at |
datetime2 |
Date/Time of the customer record deletion |
|
dte_expiration_date |
datetime2 |
Date/Time of retention period expiration |
|
created_from |
nvarchar(200) |
Indicate source of the customer record creation |
|
email |
nvarchar(1000) |
Email address associated to customer record |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |
cm_forms
Contains/used for: A ticket may (or may not) relate to a form. If such a relation is made, the form_id column in the cm_tickets table will refer to a form in this table.
|
Column name |
Data type |
Description |
|
form_id |
int |
Unique ID of a Form |
|
tenant_id |
int |
Ref. to tenant, foreign key to cm_tenants. |
|
name |
nvarchar(1000) |
Name given to a Form |
|
dte_created_at |
datetime2 |
Date/Time of Form creation |
|
dte_updated_at |
datetime2 |
Date/Time of Form latest update |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |
cm_organisations
Contains/used for: A tenant (i.e. a Puzzel customer) can optionally use the dimentsion 'organisations'. If used, you will find the name and description for each organisation record in this table.
|
Column name |
Data type |
Description |
|
org_id |
int |
Unique ID for an Organisation |
|
tenant_id |
int |
Ref. to tenant, foreign key to cm_tenants. |
|
name |
nvarchar(1000) |
Name given to the Organisation |
|
description |
nvarchar(1000) |
Description given to the Organisation |
|
dte_deleted_at |
datetime2 |
Date/Time of Organisation deletion |
|
dte_created_at |
datetime2 |
Date/Time of Organisation creation |
|
dte_updated_at |
datetime2 |
Date/Time of Organisation latest update |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |
cm_parent_sub_tickets
Contains/used for: Tickets might be linked together through a parent/child relationship. When done, such relations are shown in this table.
|
Column name |
Data type |
Description |
|
parent_sub_id |
int |
Unique ID for a child ticket record |
|
parent_ticket_id |
bigint |
Unique ticket_id from table: cm_tickets of linked Parent Ticket |
|
sub_ticket_id |
bigint |
Unique ticket_id from table: cm_tickets of linked Child Ticket |
|
dte_created_at |
datetime2 |
Date child ticket created |
|
dte_updated_at |
datetime2 |
Date child ticket latest update |
|
tenant_id |
int |
Ref. to tenant, foreign key to cm_tenants. |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |
cm_team_queues
Contains/used for: Mapping 'Teams' in Puzzel Case Management to Puzzel Contact Center 'Queues'
|
Column name |
Data type |
Description |
|
team_queue_id |
int |
Unique ID for the team/queue record |
|
tenant_id |
int |
Ref. to tenant, foreign key to cm_tenants. |
|
team_id |
int |
Ref. to table cm_teams. |
| queue_key | nvarchar(100) | Ref. of the mapped PCC queue |
|
dte_created_at |
datetime2 |
Date/Time the mapping was created |
|
dte_updated_at |
datetime2 |
Date/Time the mapping was updated |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |
cm_teams
Contains/used for: Information relating to the definition of a Team
|
Column name |
Data type |
Description |
|
team_id |
int |
Unique ID for the 'Team' database record |
|
tenant_id |
int |
Ref. to tenant, foreign key to cm_tenants. |
|
name |
nvarchar(1000) |
Name given to the 'Team' |
|
dte_created_at |
datetime2 |
Date/Time of the Team creation |
|
dte_updated_at |
datetime2 |
Date/Time of the Team latest update |
|
dte_deleted_at |
datetime2 |
Date/Time of the Team deletion |
|
puzzel_id |
int |
Relating to legacy PCC integration records |
|
pcc_access_point |
nvarchar(200) |
Relating to legacy PCC integrations |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |
cm_templates
Contains/used for: Storing template records
|
Column name |
Data type |
Description |
|
template_id |
int |
Unique ID for a template record |
|
tenant_id |
int |
Ref. to tenant, foreign key to cm_tenants. |
|
name |
nvarchar(500) |
Name of the template |
| description | nvarchar(500) | Description field of the template |
|
dte_created_at |
datetime2 |
Date/Time template was created |
|
dte_updated_at |
datetime2 |
Date/Time template was last updated |
|
dte_deleted_at |
datetime2 |
Date/Time template was last deleted |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |
cm_tenants
Contains/used for: A record here represent a PCM customer seen from Puzzel's side. Normally we will have only one record here, but bringing the tenant_id column into most of the tables, combined with this cm_tenants table, allows us to have more than one tenant represented in one rawdata database. Some bigger customers might want this.
|
Column name |
Data type |
Description |
|
tenant_id |
int |
Unique ID given to a tenant record |
|
name |
nvarchar(1000) |
Name of the Tenant |
|
dte_deleted_at |
datetime |
Recorded date of a PCM tenant deletion |
|
dte_created_at |
datetime |
Recorded date of a PCM tenant creation |
|
dte_updated_at |
datetime |
Recorded date of a PCM tenant configuration change |
|
puzzel_service_id |
nvarchar(200) |
?? |
|
puzzel_username |
nvarchar(200) |
?? |
|
puzzel_customer_number |
nvarchar(200) |
Customer number used for Puzzel integrations with PCM |
|
solution_id |
nvarchar(200) |
Unique ID given to each Puzzel solution attached to the customer |
|
customer_key |
nvarchar(200) |
Master Puzzel Customer Record Number |
|
description |
nvarchar(510) |
?? |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |
cm_users
Contains/used for: This table is used for User configurations within PCM
|
Column name |
Data type |
Description |
|
user_id |
bigint |
Unique ID given to a user record within PCM |
|
tenant_id |
int |
Ref. to tenant, foreign key to cm_tenants. |
|
puzzel_id |
int |
Linked PCC User ID (used for mapping between PCM and PCC) |
|
name |
nvarchar(1000) |
Name given to the user |
|
username |
nvarchar(200) |
username specified for the user |
|
dte_created_at |
datetime2 |
Date/Time the user record was created |
|
dte_updated_at |
datetime2 |
Date/Time the user record was last edited |
|
dte_deleted_at |
datetime2 |
Date/Time the user record was deleted |
|
utc_transfer_timestamp |
datetime2 |
Date/timestamp that the data was transferred to RawData |