Data Modeling Documentation

Overview

This document outlines the data modeling approach for the Dispatch Center Application, including entity relationships, data flow patterns, and integration strategies.

Table of Contents

• Entity Relationship Overview
• Core Entities
• Data Flow Architecture
• Integration Data Models
• Data Governance
• Performance Considerations
• Migration Strategy

Entity Relationship Overview

The data model follows Domain-Driven Design (DDD) principles with clear bounded contexts for each business domain.

flowchart TB
    customer["Customer<br/>Domain"]
    service["Service<br/>Domain"]
    technician["Technician<br/>Domain"]
    billing["Billing<br/>Domain"]
    dispatch["Dispatch<br/>Domain"]
    store["Store<br/>Domain"]

    customer <--> service
    service <--> technician
    customer --> billing
    service --> dispatch
    technician --> store

Core Entities

Customer Domain

Customer

Table: Customers
- CustomerID (PK, uniqueidentifier)
- CustomerNumber (varchar(50), unique)
- CompanyName (nvarchar(255))
- ContactFirstName (nvarchar(100))
- ContactLastName (nvarchar(100))
- Email (nvarchar(255))
- Phone (varchar(20))
- CreatedDate (datetime2)
- ModifiedDate (datetime2)
- IsActive (bit)

CustomerAddress

Table: CustomerAddresses
- AddressID (PK, uniqueidentifier)
- CustomerID (FK to Customers)
- AddressType (varchar(20)) -- 'Billing', 'Service', 'Mailing'
- Street1 (nvarchar(255))
- Street2 (nvarchar(255))
- City (nvarchar(100))
- State (varchar(10))
- ZipCode (varchar(20))
- Country (varchar(10))
- IsDefault (bit)

Service Domain

ServiceRequest

Table: ServiceRequests
- ServiceRequestID (PK, uniqueidentifier)
- CustomerID (FK to Customers)
- StoreID (FK to Stores)
- ServiceType (varchar(50))
- Priority (varchar(20)) -- 'Low', 'Normal', 'High', 'Emergency'
- Status (varchar(20)) -- 'Open', 'Assigned', 'InProgress', 'Completed', 'Cancelled'
- Description (nvarchar(max))
- RequestedDate (datetime2)
- ScheduledDate (datetime2)
- CompletedDate (datetime2)
- CreatedBy (uniqueidentifier)
- AssignedTechnicianID (FK to Technicians)

WorkOrder

Table: WorkOrders
- WorkOrderID (PK, uniqueidentifier)
- ServiceRequestID (FK to ServiceRequests)
- WorkOrderNumber (varchar(50), unique)
- TechnicianID (FK to Technicians)
- Status (varchar(20))
- StartTime (datetime2)
- EndTime (datetime2)
- LaborHours (decimal(5,2))
- Notes (nvarchar(max))
- CustomerSignature (varbinary(max))

Technician Domain

Technician

Table: Technicians
- TechnicianID (PK, uniqueidentifier)
- EmployeeNumber (varchar(50), unique)
- FirstName (nvarchar(100))
- LastName (nvarchar(100))
- Email (nvarchar(255))
- Phone (varchar(20))
- HireDate (date)
- StoreID (FK to Stores)
- IsActive (bit)
- SkillLevel (varchar(20)) -- 'Junior', 'Senior', 'Lead', 'Specialist'

TechnicianSkill

Table: TechnicianSkills
- TechnicianSkillID (PK, uniqueidentifier)
- TechnicianID (FK to Technicians)
- SkillType (varchar(100))
- ProficiencyLevel (varchar(20)) -- 'Basic', 'Intermediate', 'Advanced', 'Expert'
- CertificationDate (date)
- ExpirationDate (date)

Store Domain

Store

Table: Stores
- StoreID (PK, uniqueidentifier)
- StoreNumber (varchar(50), unique)
- StoreName (nvarchar(255))
- Address (nvarchar(500))
- Phone (varchar(20))
- ManagerID (FK to Employees)
- Territory (varchar(100))
- IsActive (bit)
- OpenDate (date)

Billing Domain

Invoice

Table: Invoices
- InvoiceID (PK, uniqueidentifier)
- InvoiceNumber (varchar(50), unique)
- CustomerID (FK to Customers)
- InvoiceDate (date)
- DueDate (date)
- SubTotal (decimal(10,2))
- TaxAmount (decimal(10,2))
- TotalAmount (decimal(10,2))
- Status (varchar(20)) -- 'Draft', 'Sent', 'Paid', 'Overdue', 'Cancelled'
- PaidDate (date)

InvoiceLineItem

Table: InvoiceLineItems
- LineItemID (PK, uniqueidentifier)
- InvoiceID (FK to Invoices)
- WorkOrderID (FK to WorkOrders, nullable)
- Description (nvarchar(255))
- Quantity (decimal(10,2))
- UnitPrice (decimal(10,2))
- LineTotal (decimal(10,2))
- ServiceDate (date)

Dispatch Domain

DispatchBoard

Table: DispatchEntries
- DispatchEntryID (PK, uniqueidentifier)
- ServiceRequestID (FK to ServiceRequests)
- TechnicianID (FK to Technicians)
- DispatchDate (date)
- TimeSlot (varchar(20)) -- 'Morning', 'Afternoon', 'Evening', 'Emergency'
- EstimatedDuration (int) -- minutes
- ActualDuration (int) -- minutes
- TravelTime (int) -- minutes
- Status (varchar(20))
- Notes (nvarchar(max))

Data Flow Architecture

1. Data Ingestion Layer

• Real-time: Service Bus messages from integration systems
• Batch: Scheduled ETL processes for bulk data synchronization
• API: RESTful endpoints for direct data entry

2. Data Processing Layer

• Validation: Business rule validation and data quality checks
• Transformation: Data normalization and enrichment
• Routing: Event-driven data distribution to appropriate domains

3. Data Storage Layer

• Transactional: Azure SQL Database for OLTP operations
• Analytical: Azure Synapse for reporting and analytics
• Cache: Redis cache for frequently accessed data

4. Data Access Layer

• Repository Pattern: Abstracted data access with dependency injection
• Entity Framework Core: ORM for database operations
• CQRS: Separate read/write models for performance optimization

Integration Data Models

Reach Integration

{
  "external_system": "reach",
  "data_sharing_policy": "minimal_required",
  "entities": {
    "customer_basic": ["customer_id", "name", "phone"],
    "service_status": ["request_id", "status", "assigned_tech"],
    "location_data": ["address", "coordinates", "access_notes"]
  },
  "sync_frequency": "real_time",
  "security": "encrypted_api_key"
}

Maddenco Integration

{
  "external_system": "maddenco",
  "entities": {
    "equipment": ["equipment_id", "model", "serial_number", "location"],
    "maintenance": ["schedule", "history", "warranty_status"]
  },
  "sync_frequency": "daily_batch",
  "security": "oauth2_client_credentials"
}

GeoTab Integration

{
  "external_system": "geotab",
  "entities": {
    "vehicle_location": ["vehicle_id", "latitude", "longitude", "timestamp"],
    "driver_data": ["driver_id", "vehicle_id", "hours_of_service"],
    "route_optimization": ["planned_route", "actual_route", "efficiency_metrics"]
  },
  "sync_frequency": "real_time",
  "security": "api_token"
}

Data Governance

Data Quality Standards

• Completeness: Required fields validation
• Accuracy: Cross-reference validation with source systems
• Consistency: Data format and value standardization
• Timeliness: Data freshness requirements and SLA compliance

Data Privacy & Security

• PII Classification: Automatic detection and classification of sensitive data
• Access Controls: Role-based data access with audit logging
• Data Masking: Dynamic data masking for non-production environments
• Retention Policies: Automated data lifecycle management

Master Data Management

• Customer Master: Single source of truth for customer data
• Product Catalog: Centralized service and product definitions
• Location Master: Standardized address and geographic data
• Reference Data: Code tables and lookup values

Performance Considerations

Indexing Strategy

-- Customer lookup optimization
CREATE NONCLUSTERED INDEX IX_Customers_Number ON Customers (CustomerNumber);
CREATE NONCLUSTERED INDEX IX_Customers_Email ON Customers (Email);

-- Service request performance
CREATE NONCLUSTERED INDEX IX_ServiceRequests_Customer_Date 
ON ServiceRequests (CustomerID, RequestedDate DESC);

-- Technician scheduling optimization
CREATE NONCLUSTERED INDEX IX_DispatchEntries_Tech_Date 
ON DispatchEntries (TechnicianID, DispatchDate);

-- Billing performance
CREATE NONCLUSTERED INDEX IX_Invoices_Customer_Date 
ON Invoices (CustomerID, InvoiceDate DESC);

Partitioning Strategy

• ServiceRequests: Partitioned by month for historical data management
• InvoiceLineItems: Partitioned by year for billing performance
• AuditLogs: Partitioned by month with automated archival

Caching Strategy

• Customer Data: 15-minute cache for frequently accessed customers
• Technician Schedules: 5-minute cache for dispatch optimization
• Product Catalog: 1-hour cache for service configurations
• Reference Data: 24-hour cache for lookup tables

Migration Strategy

Phase 1: Foundation (Months 1-2)

• Core entity setup and relationships
• Basic CRUD operations
• Integration framework establishment

Phase 2: Core Features (Months 3-4)

• Customer and service request management
• Basic technician scheduling
• Simple billing functionality

Phase 3: Advanced Features (Months 5-6)

• Advanced dispatch optimization
• Complete billing and payment processing
• Comprehensive reporting

Phase 4: Integration & Optimization (Months 7-8)

• External system integrations
• Performance optimization
• Advanced analytics and reporting

Data Migration Approach

1. Assessment: Current Reach system data analysis
2. Mapping: Field-level mapping between systems
3. Validation: Data quality assessment and cleanup
4. Migration: Phased migration with parallel running
5. Verification: Post-migration data validation and reconciliation

---

Document Version: 1.0
Last Updated: November 2025
Next Review: December 2025