Database Schema
The field staff tracking system uses three core tables with a parent-detail relationship. The main GPS record links to location waypoints and captured images.
Entity Relationship Diagram
Table: t_SiteVisit_GPS
The primary tracking table storing each field duty session.
| Column | Type | Description |
|---|---|---|
| SiteVisitGPSID | bigint (PK) | Auto-increment primary key |
| RefNo | varchar | Reference number for the visit |
| UserId | bigint (FK) | Staff user performing the visit |
| Purpose | varchar | Visit purpose (e.g., "Follow Up CP", "Booking CP") |
| Remarks | varchar | Staff notes at check-in |
| StartingDateAndTime | datetime | Check-in timestamp |
| EndingDateAndTime | datetime | Check-out timestamp (null while active) |
| StartingLatitude | decimal | Check-in latitude |
| StartingLongitude | decimal | Check-in longitude |
| EndingLatitude | decimal | Check-out latitude |
| EndingLongitude | decimal | Check-out longitude |
| StartingLocation | varchar | Reverse-geocoded start address |
| EndingLocation | varchar | Reverse-geocoded end address |
| ClosingRemarks | varchar | Staff notes at check-out |
| CallLogId | bigint (FK) | Linked call log entry |
| IsApproved | bit | Approval status |
| ApprovalBy | bigint (FK) | Manager who approved/rejected |
| ApprovalOn | datetime | Approval timestamp |
| Reason | varchar | Approval/rejection reason |
Table: t_SiteVisit_GPS_Detail
Location waypoints captured throughout the duty period.
| Column | Type | Description |
|---|---|---|
| SiteVisitGPSDetailId | bigint (PK) | Auto-increment primary key |
| SiteVisitGPSID | bigint (FK) | Parent GPS record |
| Latitude | decimal | Waypoint latitude |
| Longitude | decimal | Waypoint longitude |
| IsManuallyCaptured | bit | True if user triggered, false if automatic |
| Description | varchar | Location description (from geocoding) |
Table: t_SiteVisit_GPS_Images
Photos captured and uploaded by field staff.
| Column | Type | Description |
|---|---|---|
| SiteVisitGPSImagesID | bigint (PK) | Auto-increment primary key |
| SiteVisitGPSID | bigint (FK) | Parent GPS record |
| SiteVisitGPSDetailId | bigint (FK) | Optional linked waypoint |
| ImagePath | varchar | Server file storage path |
| CreatedDateAndTime | datetime | Upload timestamp |
Table: MobileAttendance
Mobile punch records linked to the daily attendance system.
| Column | Type | Description |
|---|---|---|
| MobileAttendanceId | bigint (PK) | Auto-increment primary key |
| EmployeeId | bigint (FK) | Employee reference |
| InDateAndTime | datetime | Punch-in timestamp |
| OutDateAndTime | datetime | Punch-out timestamp (null while active) |
| StartingLocation | varchar | Check-in address |
| EndingLocation | varchar | Check-out address |
| StartingDistanceInMetres | decimal | Distance from office at check-in |
| EndingDistanceInMetres | decimal | Distance from office at check-out |
Key SQL Queries
Get GPS record with duration:
SELECT sv.*, u.FullName AS UserName,
LEFT(CONVERT(VARCHAR(10),
EndingDateAndTime - StartingDateAndTime, 108), 8) AS TotalDuration
FROM t_SiteVisit_GPS sv
INNER JOIN UM_Users u ON sv.UserId = u.UserId
WHERE sv.SiteVisitGPSID = @SiteVisitGPSIDGet waypoints for a visit:
SELECT Latitude, Longitude,
ISNULL(IsManuallyCaptured, 0) AS IsManuallyCaptured,
ISNULL(Description, '') AS LocationDescription
FROM t_SiteVisit_GPS_Detail
WHERE SiteVisitGPSID = @SiteVisitGPSIDGet images with location data:
SELECT SVD.Latitude, SVD.Longitude,
ISNULL(SVD.IsManuallyCaptured, 0) AS IsManuallyCaptured,
ISNULL(SVD.Description, '') AS LocationDescription
FROM t_SiteVisit_GPS_Images SI
LEFT JOIN t_SiteVisit_GPS_Detail SVD
ON SI.SiteVisitGPSDetailId = SVD.SiteVisitGPSDetailId
WHERE SI.SiteVisitGPSID = @SiteVisitGPSID
AND ISNULL(SVD.Latitude, 0) <> 0
AND ISNULL(SVD.Longitude, 0) <> 0