Field Service Management data model
Normalized (3NF) field-service-management model. Customer/site/asset hierarchy, contract-governed work orders, technician dispatch with travel/labor capture, parts inventory and per-job consumption, and inspection records. Contact PII is classified and masked.
Field-service operations: customers and their service sites, the assets installed at those sites, work orders, technicians and dispatches, the parts inventory and parts consumed on a job, service contracts, and on-site inspections. Customer/technician contact data is PII. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 10
- tables
- 105
- columns
- 15
- relationships
- 8
- 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
Customer -> site -> asset hierarchy
A customer can have many sites and each site many serviceable assets; this hierarchy lets work orders target the precise asset while still rolling up to the customer for billing and contracts.
Work order separate from dispatch
One job may require multiple technician visits; modeling dispatches as children of the work order captures per-visit travel and labor without conflating the job with the people sent to it.
Parts inventory with per-job consumption junction
Parts are shared stock referenced by many jobs; a part_used junction records quantity and warranty coverage per work order while keeping the catalog and on-hand counts normalized.
Service contract drives coverage and SLA
Response SLAs and warranty handling depend on the governing contract; linking work orders to a contract enables entitlement checks and contract-vs-billable cost analysis.
Tables in the Field Service Management 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)
customer
Grain: one row per service customer
Customer contact details are PII.
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_number | varchar | BK |
| customer_name | varchar | |
| customer_type | varchar | |
| primary_contact_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| billing_address | varchar | PII |
| billing_country | varchar | |
| is_active | boolean |
site
Grain: one row per service location/site for a customer
| Column | Type | Keys |
|---|---|---|
| site_key | bigint | PK |
| customer_key | bigint | |
| site_code | varchar | BK |
| site_name | varchar | |
| address_line1 | varchar | PII |
| city | varchar | |
| state_province | varchar | |
| postal_code | varchar | |
| country | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| service_territory | varchar | |
| access_notes | varchar |
asset
Grain: one row per serviceable asset installed at a site
| Column | Type | Keys |
|---|---|---|
| asset_key | bigint | PK |
| site_key | bigint | |
| asset_tag | varchar | BK |
| asset_name | varchar | |
| asset_type | varchar | |
| manufacturer | varchar | |
| model_number | varchar | |
| serial_number | varchar | |
| install_date | date | |
| warranty_expiry_date | date | |
| status | varchar |
service_contract
Grain: one row per service/maintenance contract
| Column | Type | Keys |
|---|---|---|
| service_contract_key | bigint | PK |
| customer_key | bigint | |
| contract_number | varchar | BK |
| contract_type | varchar | |
| coverage_level | varchar | |
| start_date | date | |
| end_date | date | |
| response_sla_hours | integer | |
| annual_value | decimal | |
| currency_code | varchar | |
| status | varchar |
technician
Grain: one row per field technician
Technician contact details are PII.
| Column | Type | Keys |
|---|---|---|
| technician_key | bigint | PK |
| technician_external_id | varchar | BK |
| full_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| home_territory | varchar | |
| skill_set | varchar | |
| certification_level | varchar | |
| is_active | boolean |
work_order
Grain: one row per field work order
| Column | Type | Keys |
|---|---|---|
| work_order_key | bigint | PK |
| customer_key | bigint | |
| site_key | bigint | |
| asset_key | bigint | |
| service_contract_key | bigint | |
| work_order_number | varchar | BK |
| work_type | varchar | |
| priority | varchar | |
| status | varchar | |
| description | varchar | |
| reported_at | timestamp | |
| scheduled_for | timestamp | |
| completed_at | timestamp | |
| billed_amount | decimal |
dispatch
Grain: one row per technician dispatch to a work order
| Column | Type | Keys |
|---|---|---|
| dispatch_key | bigint | PK |
| work_order_key | bigint | |
| technician_key | bigint | |
| dispatch_role | varchar | |
| scheduled_arrival | timestamp | |
| actual_arrival | timestamp | |
| departed_at | timestamp | |
| travel_minutes | integer | |
| labor_minutes | integer | |
| status | varchar |
part
Grain: one row per stockable part/SKU
| Column | Type | Keys |
|---|---|---|
| part_key | bigint | PK |
| part_number | varchar | BK |
| part_name | varchar | |
| category | varchar | |
| unit_of_measure | varchar | |
| unit_cost | decimal | |
| unit_price | decimal | |
| quantity_on_hand | integer | |
| reorder_point | integer |
part_used
Grain: one row per part consumed on a work order (junction)
| Column | Type | Keys |
|---|---|---|
| part_used_key | bigint | PK |
| work_order_key | bigint | |
| part_key | bigint | |
| dispatch_key | bigint | |
| quantity_used | decimal | |
| unit_price | decimal | |
| line_total | decimal | |
| is_warranty_covered | boolean |
inspection
Grain: one row per on-site inspection performed on a work order
| Column | Type | Keys |
|---|---|---|
| inspection_key | bigint | PK |
| work_order_key | bigint | |
| asset_key | bigint | |
| technician_key | bigint | |
| inspection_type | varchar | |
| checklist_score | decimal | |
| passed | boolean | |
| findings | varchar | |
| follow_up_required | boolean | |
| inspected_at | timestamp |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- site.customer_key → customer (many-to-one)
- asset.site_key → site (many-to-one)
- service_contract.customer_key → customer (many-to-one)
- work_order.customer_key → customer (many-to-one)
- work_order.site_key → site (many-to-one)
- work_order.asset_key → asset (many-to-one)
- work_order.service_contract_key → service_contract (many-to-one)
- dispatch.work_order_key → work_order (many-to-one)
- dispatch.technician_key → technician (many-to-one)
- part_used.work_order_key → work_order (many-to-one)
- part_used.part_key → part (many-to-one)
- part_used.dispatch_key → dispatch (many-to-one)
- inspection.work_order_key → work_order (many-to-one)
- inspection.asset_key → asset (many-to-one)
- inspection.technician_key → technician (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.