Entity Relationships

Version: Draft

Overview

Six tables form the Driver Performance Feedback system. They cover incident tracking per order, monthly score aggregation, and performance tier management.

Entity Relationship Diagram

┌─────────────────────────────┐
│   PerformanceIncidentType   │  Master lookup — defines incident templates
│─────────────────────────────│  (code, description, points, severity,
│ PK id                       │   incidentCategory, isQualify, isActive)
│    incidentCode             │
│    incidentDescription      │
│    incidentCategory         │
│    points                   │
│    severity                 │
│    isQualify                │
│    isActive                 │
└──────────────┬──────────────┘
               │ 1
               │ (referenced at time of incident creation;
               │  values are copied into the detail record)
               │ M
┌──────────────▼──────────────┐         ┌──────────────────┐
│  DriverOrderFeedbackDetail  │         │      Order       │
│─────────────────────────────│    ┌────│ PK id            │
│ PK id                       │    │    └──────────────────┘
│ FK driverOrderFeedbackId ───┼──┐ │              1
│ FK performanceIncidentTypeId│  │ │              │
│    incidentCode (snapshot)  │  │ │    ┌─────────▼────────────────────┐
│    incidentDescription      │  │ │    │     DriverOrderFeedback      │
│    incidentCategory         │  │ │    │──────────────────────────────│
│    points (snapshot)        │  │ └───►│ PK id                        │
│    severity (snapshot)      │  │      │ FK orderId                   │
│    isQualify (snapshot)     │  │      │ FK driverId ─────────────────┼──┐
│    status                   │  │      │    driverName                │  │
│    approvalRemark           │  └─────►│    totalPoints               │  │
│    approvalDatetime         │         │    status                    │  │
│    approvalBy               │         │    supplierDoDatetime        │  │
└─────────────────────────────┘         └──────────────────────────────┘  │
                                                                           │
               ┌───────────────────────────────────────────────────────────┘
               │  Driver (external master)
               │  1
               │
               │ M
┌──────────────▼──────────────────────────┐
│      DriverPerformanceMonthlyScore      │  Monthly aggregation per driver
│─────────────────────────────────────────│
│ PK id                                   │
│ FK driverId                             │
│ FK performanceTierId ───────────────────┼──┐
│    driverName                           │  │
│    payrollMonth                         │  │
│    performanceTierName (snapshot)       │  │
│    performanceTierIncentiveAmount       │  │
│    performanceTierStatusResult          │  │
│    totalBasicSalary                     │  │
│    totalOrder                           │  │
│    totalPoints                          │  │
│    averagePoints                        │  │
│    majorIncidentCount                   │  │
│    criticalIncidentCount                │  │
│    status                               │  │
└─────────────────────────────────────────┘  │
                                             │ M
                              ┌──────────────▼──────────────┐
                              │       PerformanceTier        │  Master — tier thresholds
                              │──────────────────────────────│
                              │ PK id                        │
                              │    tierName                  │
                              │    minAveragePoints          │
                              │    maxAveragePoints          │
                              │    minTripCount              │
                              │    minBasicSalary            │
                              │    incentiveAmount           │
                              │    maxCriticalIncident       │
                              │    maxMajorIncident          │
                              │    statusResult              │
                              │    priority                  │
                              │    isActive                  │
                              └──────────────┬───────────────┘
                                             │ 1
                                             │ M
                              ┌──────────────▼───────────────┐
                              │    PerformanceTierLogging    │  Audit trail for tier changes
                              │──────────────────────────────│
                              │ PK id                        │
                              │ FK performanceTierId         │
                              │    actionType                │
                              │    oldValue (JSON text)      │
                              │    newValue (JSON text)      │
                              │    createdDatetime           │
                              │    createdBy                 │
                              └──────────────────────────────┘

Relationship Summary

Key Design Notes

1. Snapshot pattern — DriverOrderFeedbackDetail copies incidentCode, incidentDescription, points, severity, isQualify from PerformanceIncidentType at creation. Historical records are unaffected if the master type is later edited.

2. Same pattern in monthly score — DriverPerformanceMonthlyScore copies performanceTierName and performanceTierIncentiveAmount from PerformanceTier at calculation time.

3. Approval workflow on detail — DriverOrderFeedbackDetail has status, approvalRemark, approvalDatetime, approvalBy — individual incidents can be approved/rejected independently.

4. PerformanceIncidentType is pure master — no @OneToMany back-relation defined. Snapshots handle history preservation.

5. PerformanceTierLogging is audit-only — tracks oldValue/newValue as JSON text for full config history of tier changes.

Source Files