darudev.online
Case Study

HRIS System

Transforming inconsistent attendance logs into reliable, payroll-ready data used in daily business operations.

PHP (CodeIgniter) MySQL Data Normalization
data_flow.php
<?php
$pipeline = [
  'source' => 'attendance_logs',
  'process' => 'Normalization Logic',
  'target' => 'attendance_daily',
  'output' => 'Payroll & Reporting',
];
?>
Overview

Context & Problem

The Context

The system handles employee attendance, leave, and payroll-related data. Raw data comes from multiple sources and is often incomplete or inconsistent.

The Problem

Raw attendance logs could not be directly used for payroll due to missing check-ins, cross-day shifts, and overlapping leave records. This resulted in frequent manual corrections and unreliable reports.

Execution

Challenges & Solution

!
Obstacles

Key Challenges

  • Cross-day attendance (check-in and check-out on different dates)
  • Overlapping leave and attendance records
  • Missing or incomplete log data
  • Inconsistent business rules across departments
✓
Architecture

The Solution

Designed and implemented a data normalization pipeline that enforces one-record-per-day consistency per employee, ensuring reliable downstream processing.

Raw attendance_logs are passed through the normalizer, cleaned, and safely stored in attendance_daily for payroll usage.

Logic

Edge Cases Handled

Late Check-in

Employees arriving after working hours must be flagged without breaking automated payroll calculations.

Cross-day Attendance

Properly linking night shifts where the physical check-in and check-out occur on completely different dates.

Missing Check-in / out

Incomplete logs are intelligently handled with validation flags instead of silently corrupting the daily records.

Overlaps & Weekends

Prioritizing correct status when employees appear present but are on leave, and treating weekend attendance as valid overtime.

Impact

Results & Methodology

Why This Approach Works

  • Data Consistency: By transforming raw logs into a single daily record per employee, the system avoids conflicting data during reporting.
  • Separation of Concerns: Raw data is preserved in attendance_logs, while processed data lives in attendance_daily, aiding debugging.
  • Deterministic Calculations: Payroll-related metrics are computed during normalization, ensuring identical results across reports.
  • Error Handling & Scalability: Edge cases use flags instead of modifying raw data. Pre-aggregated data severely reduces query load.
Manual CorrectionsReduced by ~70–80%

Drastic reduction in the need for human intervention before processing payroll.

Reporting ConsistencyStandardized

Improved and unified reporting consistency across all operational departments.

Payroll ProcessingAutomated

Enabled end-of-month payroll processing with minimal to no manual data wrangling.

Outcome: This system became a highly reliable source of truth for attendance and payroll data in daily business operations.