ITSM Helpdesk data model
Normalized (3NF) ITIL-inspired helpdesk model. Service catalog and SLA policy lookups, incident/request tickets with work-note comments and affected-CI links, problem and change management with implementation tasks, and a CMDB asset register. Requester/agent contact data is PII.
IT service management following ITIL process areas: a service catalog and SLA policies, customers and agents, incident/request tickets with comments, problems and changes, the configuration items (CMDB) they affect, and the category taxonomy classifying work. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 12
- tables
- 106
- columns
- 18
- relationships
- 5
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Separate incident, problem, and change records
ITIL treats incidents (restore service), problems (root cause), and changes (controlled modification) as distinct processes; modeling them as separate linked entities mirrors the practice and keeps lifecycle fields from colliding.
Assets as a CMDB referenced by tickets
Configuration items are reused across many tickets, problems, and changes; a normalized CI register avoids duplicating asset data and supports impact analysis via the ticket-asset junction.
SLA policy as a lookup applied to tickets
Response/resolution targets are policy-driven and shared; storing them once and referencing from service and ticket keeps targets consistent and breach calculation auditable.
Comments split into customer-visible and internal
Work notes must be distinguishable from customer-facing replies; an is_internal flag on a normalized comment table preserves the full conversation thread at one grain.
Tables in the ITSM Helpdesk model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (10)
service_catalog
Grain: one row per offered IT service
| Column | Type | Keys |
|---|---|---|
| service_catalog_key | bigint | PK |
| sla_policy_key | bigint | |
| service_code | varchar | BK |
| service_name | varchar | |
| service_category | varchar | |
| description | varchar | |
| owner_group | varchar | |
| is_active | boolean |
customer
Grain: one row per requesting customer/end user
End-user contact details are PII.
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_external_id | varchar | BK |
| full_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| department | varchar | |
| organization | varchar | |
| location | varchar | |
| vip | boolean |
agent
Grain: one row per support agent/technician
Agent contact details are PII.
| Column | Type | Keys |
|---|---|---|
| agent_key | bigint | PK |
| agent_external_id | varchar | BK |
| full_name | varchar | PII |
| varchar | PII | |
| support_group | varchar | |
| tier | varchar | |
| is_active | boolean |
asset
Grain: one row per configuration item (CMDB CI)
| Column | Type | Keys |
|---|---|---|
| asset_key | bigint | PK |
| category_key | bigint | |
| ci_number | varchar | BK |
| ci_name | varchar | |
| ci_type | varchar | |
| status | varchar | |
| environment | varchar | |
| serial_number | varchar | |
| assigned_to_external_id | varchar | |
| acquired_date | date |
problem
Grain: one row per problem (root cause of incidents)
| Column | Type | Keys |
|---|---|---|
| problem_key | bigint | PK |
| category_key | bigint | |
| problem_number | varchar | BK |
| title | varchar | |
| description | varchar | |
| status | varchar | |
| root_cause | varchar | |
| workaround | varchar | |
| priority | varchar | |
| identified_at | timestamp | |
| resolved_at | timestamp |
change
Grain: one row per change request
| Column | Type | Keys |
|---|---|---|
| change_key | bigint | PK |
| category_key | bigint | |
| problem_key | bigint | |
| change_number | varchar | BK |
| title | varchar | |
| change_type | varchar | |
| risk_level | varchar | |
| status | varchar | |
| requested_by_external_id | varchar | |
| scheduled_start | timestamp | |
| scheduled_end | timestamp | |
| implemented_at | timestamp |
ticket
Grain: one row per incident/service-request ticket
| Column | Type | Keys |
|---|---|---|
| ticket_key | bigint | PK |
| customer_key | bigint | |
| assigned_agent_key | bigint | |
| category_key | bigint | |
| sla_policy_key | bigint | |
| service_catalog_key | bigint | |
| asset_key | bigint | |
| problem_key | bigint | |
| ticket_number | varchar | BK |
| ticket_type | varchar | |
| subject | varchar | |
| description | varchar | |
| priority | varchar | |
| status | varchar | |
| channel | varchar | |
| opened_at | timestamp | |
| first_response_at | timestamp | |
| resolved_at | timestamp | |
| sla_breached | boolean |
ticket_comment
Grain: one row per comment/work-note on a ticket
| Column | Type | Keys |
|---|---|---|
| ticket_comment_key | bigint | PK |
| ticket_key | bigint | |
| author_agent_key | bigint | |
| comment_text | varchar | |
| is_internal | boolean | |
| created_at | timestamp |
change_task
Grain: one row per implementation task within a change
| Column | Type | Keys |
|---|---|---|
| change_task_key | bigint | PK |
| change_key | bigint | |
| assigned_agent_key | bigint | |
| task_number | varchar | BK |
| description | varchar | |
| status | varchar | |
| planned_start | timestamp | |
| completed_at | timestamp |
ticket_asset
Grain: one row per asset affected by a ticket (junction)
| Column | Type | Keys |
|---|---|---|
| ticket_asset_key | bigint | PK |
| ticket_key | bigint | |
| asset_key | bigint | |
| impact | varchar |
Lookup tables (2)
category
Grain: reference taxonomy classifying tickets/problems/changes
| Column | Type | Keys |
|---|---|---|
| category_key | bigint | PK |
| category_code | varchar | BK |
| category_name | varchar | |
| parent_category_code | varchar | |
| applies_to | varchar |
sla_policy
Grain: reference list of SLA policies
| Column | Type | Keys |
|---|---|---|
| sla_policy_key | bigint | PK |
| policy_code | varchar | BK |
| policy_name | varchar | |
| priority | varchar | |
| response_target_minutes | integer | |
| resolution_target_minutes | integer | |
| business_hours_only | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- service_catalog.sla_policy_key → sla_policy (many-to-one)
- asset.category_key → category (many-to-one)
- problem.category_key → category (many-to-one)
- change.category_key → category (many-to-one)
- change.problem_key → problem (many-to-one)
- ticket.customer_key → customer (many-to-one)
- ticket.assigned_agent_key → agent (many-to-one)
- ticket.category_key → category (many-to-one)
- ticket.sla_policy_key → sla_policy (many-to-one)
- ticket.service_catalog_key → service_catalog (many-to-one)
- ticket.asset_key → asset (many-to-one)
- ticket.problem_key → problem (many-to-one)
- ticket_comment.ticket_key → ticket (many-to-one)
- ticket_comment.author_agent_key → agent (many-to-one)
- change_task.change_key → change (many-to-one)
- change_task.assigned_agent_key → agent (many-to-one)
- ticket_asset.ticket_key → ticket (many-to-one)
- ticket_asset.asset_key → asset (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.