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