tbl_user_tracking_olddata

-1 rows


Description

Module: HRMS Employee Tracking
Purpose: Provides optimized data storage for historical tracking records by archiving month-wise data from the main tracking table to improve query performance and manage data growth.
Data: Each row represents archived tracking data from previous months, maintaining identical structure to main table but stored separately for performance optimization.
Process Usage:
- Stores historical tracking data (typically 2+ months old) in separate table for faster queries on recent data.
- Enables efficient data retrieval by reducing table size for current period queries.
- Supports compliance requirements for long-term data retention without impacting operational performance.
Key Points:
- Identical schema to main tbl_user_tracking table for seamless data access.
- Month-wise partitioning strategy for optimal query performance.<br-**Maintains data integrity while improving system responsiveness for recent data operations.
Business Impact: Significantly improves application performance for current period operations, maintains complete historical data for compliance and reporting, and enables efficient data management through archival strategies.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id serial 10 √ nextval('tbl_user_tracking_olddata_id_seq'::regclass)

Primary key
Format: Long (auto-increment)
Used as: Unique identifier for each archived tracking record.
Note: Maintains separate sequence from main table to avoid conflicts.
daily_tracker_id int4 10 √ null

FK: public.tbl_daily_tracker.id (historical reference)
Purpose: Maintains relationship to daily summary records from archived period.
Usage: Ensures referential integrity for historical reporting and analysis.
employee_id varchar 500 null

FK: public.tbl_employee.employeeid
Purpose: Identifies the employee associated with this archived tracking event.
Dependency: Maintains employee context for historical reporting and compliance queries.
date timestamp 29,6 null

Format: Timestamp
Purpose: Original date and time when the tracking event was captured.
Archival Logic: Typically contains data from previous months (e.g., 2+ months old).
Query Strategy: Used for partitioning and efficient date-range queries on historical data.
latitude numeric 0 null

Format: BigDecimal (Decimal degrees)
Purpose: Geographic latitude coordinate from original tracking event.
Note: Uses BigDecimal type instead of String (different from main table) for numerical precision in historical calculations.
longitude numeric 0 null

Format: BigDecimal (Decimal degrees)
Purpose: Geographic longitude coordinate from original tracking event.
Note: Uses BigDecimal type instead of String (different from main table) for numerical precision in historical calculations.
punch_type varchar 10 null

Format: String
Purpose: Original punch event classification from archived period.
Values: PUNCH_IN, PUNCH_OUT, BREAK_START, BREAK_END, LOCATION_UPDATE
Usage: Historical attendance pattern analysis and compliance reporting.
createdby varchar 50 √ null
createddate timestamp 29,6 √ null
modifiedby varchar 50 √ null
modifieddate timestamp 29,6 √ null
networking varchar 100 √ null

Format: String
Purpose: Calculated net working hours from the archived period.
Usage: Historical productivity analysis and working hours compliance reporting.
siteid varchar 100 √ null

Format: String
Purpose: Work site or location context from archived tracking events.
Usage: Historical site visitation patterns and project-based reporting.
batterystatus varchar 100 √ null

Format: String
Purpose: Device battery status from original tracking event.
Usage: Historical data reliability assessment and device performance trends.
expensetracking varchar 50 √ null

Format: String (JSON or structured data)
Purpose: Expense-related data from archived tracking events.
Usage: Historical expense validation and travel pattern analysis.
punchfrom int4 10 √ null

Format: Integer
Purpose: Original source/method of punch recording from archived data.
Values: 1=Mobile App, 2=Web Portal, 3=Biometric, 4=Auto-punch, 5=Admin override
Usage: Historical data quality and source analysis.
locationname varchar 500 √ null

Indexes

Constraint Name Type Sort Column(s)
tbl_user_tracking_olddata_pk__tbl_user_tracking_olddata Primary key Asc id
tbl_user_tracking_olddata_idx_employee_punchtype_date_tbl_user_ Performance Asc/Asc/Asc employee_id + punch_type + date

Relationships

Close relationships within degrees of separation