Module Relationship Diagram (ERD)
This ERD illustrates the definitive, best-practice architecture for the module. It separates policies for Attendance (time) and Salary (money), and utilizes a powerful, scalable "rules engine" approach for all incentives. Core HR tables are included to show the complete data ecosystem.
erDiagram
%% direction TD
SALARY_POLICIES {
int id PK
string policy_name "eg Sales Team Q4 2025"
int designation_id FK
string effective_date
string salary_type "ENUM: fixed, commission_only, target_based_fixed"
decimal base_salary_amount "Base pay for fixed or target-based"
int created_by FK
}
INCENTIVE_RULES {
int id PK
int salary_policy_id FK
string rule_name "e.g., iPhone 16 Launch Bonus"
enum rule_type "'target_sale', 'product_sale', 'recovery', etc."
enum sales_level "'individual', 'branch'"
enum payout_type "'fixed_amount', 'percentage'"
decimal payout_value
string group_code "Nullable, groups rules into tiers"
decimal condition_target_amount "Nullable, e.g., 500000"
enum condition_operator "'>=', '<=', '=='"
int condition_product_id "FK, Nullable"
int condition_product_group_id "FK, Nullable"
int condition_recovery_age_months "Nullable, e.g., 6"
}
ATTENDANCE_POLICIES {
int id PK
string policy_name "e.g., Standard 9-to-5 Office Schedule"
int designation_id FK
unsignedInt in_gracetime_minutes
unsignedInt out_gracetime_minutes
unsignedInt advance_percentage_limit "Nullable, max % of salary"
decimal fixed_advance_limit "Nullable, fixed max amount"
}
POLICY_WORK_SCHEDULES {
int id PK
int attendance_policy_id FK
enum day_of_week "'Monday', 'Tuesday', ..."
time from_time
time to_time
bool is_work_day
}
HOLIDAYS {
int id PK
int branch_id FK
string name
date holiday_date
string description
}
ATTENDANCE_DEDUCTION_RULES {
int id PK
int attendance_policy_id FK
string rule_description "e.g., 'Late by > 30 mins results in half-day'"
enum condition_type "'late_arrival', 'early_departure', 'short_hours', 'absent'"
int condition_minutes_threshold "Nullable. e.g., 30 (for late_arrival)"
enum deduction_type "'fixed_amount', 'percentage_of_daily_salary', 'mark_as_half_day', 'mark_as_absent'"
decimal deduction_value "e.g., 500 (for fixed_amount) or 50 (for percentage)"
}
LEAVE_TYPES {
int id PK
string name "e.g., Sick, Casual, Annual"
bool is_paid "Determines if salary is deducted"
}
LEAVE_POLICY_RULES {
int id PK
int attendance_policy_id FK
int leave_type_id FK
int days_allowed_annually "e.g., 12"
}
EMPLOYEE_LEAVE_APPLICATIONS {
int id PK
int employee_id FK
int leave_type_id FK
date start_date
date end_date
string reason
enum status "'pending', 'approved', 'rejected'"
}
SALARY_CALCULATIONS {
int id PK
int employee_id FK
int branch_id FK
int month
int year
date compute_date
decimal gross_salary
decimal net_salary
enum status "'draft', 'approved', 'paid'"
string invoice "nullable"
}
SALARY_COMPONENTS {
int id PK
int calculation_id FK "SALARY_CALCULATIONS.id"
enum component_type "'earning', 'deduction'"
enum source_type "'base', 'incentive', 'attendance', 'advance', 'manual'"
string label "e.g., Marketing Commission, Absent Deduction"
decimal amount "nullable"
string source_type "'AttendanceDeductionRule'"
int source_id "FK to ATTENDANCE_DEDUCTION_RULES.id"
}
INCENTIVE_CALCULATIONS {
int id PK
int calculation_id FK "SALARY_CALCULATIONS.id"
int rule_id FK "INCENTIVE_RULES.id"
decimal target_amount
decimal achieved_amount
decimal payout_amount
}
EMPLOYEES }o--|| SALARY_POLICIES : "is governed by"
EMPLOYEES }o--|| ATTENDANCE_POLICIES : "is governed by"
SALARY_POLICIES ||--|{ INCENTIVE_RULES : "is composed of"
ATTENDANCE_POLICIES ||--|{ POLICY_WORK_SCHEDULES : "has schedule"
ATTENDANCE_POLICIES ||--|{ POLICY_WORK_SCHEDULES : "has schedule"
ATTENDANCE_POLICIES ||--|{ ATTENDANCE_DEDUCTION_RULES : "has deduction rules"
SALARY_CALCULATIONS }o--|| SALARY_COMPONENTS : "has components"
SALARY_CALCULATIONS }o--|| INCENTIVE_CALCULATIONS : "has incentives"
ATTENDANCE_POLICIES ||--|{ LEAVE_POLICY_RULES : "defines allowances in"
LEAVE_TYPES ||--|{ LEAVE_POLICY_RULES : "are allocated by"
EMPLOYEES ||--|{ EMPLOYEE_LEAVE_APPLICATIONS : "submits"
LEAVE_TYPES ||--|{ EMPLOYEE_LEAVE_APPLICATIONS : "is of type"
1. System Configuration (Admin/HR)
flowchart TD
subgraph "A. Global Setup"
A1["1. Populate HOLIDAYS Table"] -- Pakistan Day, Eid Holidays, etc. --> A2
A2["2. Populate LEAVE_TYPES Table"] -- Sick, Casual, Annual (Paid/Unpaid) --> A3
end
A3((Start Policy Creation)) --> A4{"Policy Type?"}
subgraph "B. Attendance Policy Setup"
A4 -- Attendance --> B1["3a. Create ATTENDANCE_POLICIES Record
e.g., 'Head Office Staff Policy'"]
B1 --> B2["4a. Define Work Week
Create POLICY_WORK_SCHEDULES records
Set Saturday/Sunday as is_work_day=false"]
B2 --> B3["5a. Define Leave Entitlements
Create LEAVE_POLICY_RULES
12 Casual, 10 Sick leaves per year"]
B3 --> B4["6a. Define Tiered Fines
Create ATTENDANCE_DEDUCTION_RULES
- Late >10min = 100 Rs fine
- Late >30min = 250 Rs fine"]
B4 --> B5["7a. Link Policy to Designation"]
end
subgraph "C. Salary Policy Setup"
A4 -- Salary --> C1["3b. Create SALARY_POLICIES Record
e.g., 'Sales Team Commission Q4'"]
C1 --> C2["4b. Define Incentive Logic
Create multiple INCENTIVE_RULES
- 5% commission on Product X sales
- 5000 Rs bonus on 1M target"]
C2 --> C3["5b. Link Policy to Designation"]
end
B5 & C3 --> FINAL_END((End: Policies are Active & Linked))
3. Daily Operations (Employee & System)
This flowchart now includes a detailed breakdown of the punch processing logic, showing precisely how an employee's schedule, grace time, and actual punch time are compared to determine their final attendance status.
flowchart TD
subgraph "Employee Actions (During the Day)"
EA1["Employee Punches In/Out
Data saved to attendance_punches"]
EA2{"Needs Time Off?"} -- Yes --> EA3["Creates EMPLOYEE_LEAVE_APPLICATIONS
Status: 'pending'"] --> EA4("Approval by Manager")
EA1 & EA2 -- No --> EA_END
end
subgraph "System's Nightly Automated Job (e.g., 2 AM)"
S_START((Trigger: Midnight Job)) --> S1["For each 'Active' Employee..."]
S1 --> S2{"1. Is today a scheduled day off?
Checks POLICY_WORK_SCHEDULES"}
S2 -- Yes --> S_END1["Mark Status: 'Weekend'"] --> S_LOOP
S2 -- No --> S3{"2. Is today a public holiday?
Checks HOLIDAYS table"}
S3 -- Yes --> S_END2["Mark Status: 'Holiday'"] --> S_LOOP
S3 -- No --> S4{"3. Is employee on approved leave?
Checks EMPLOYEE_LEAVE_APPLICATIONS"}
S4 -- Yes --> S_END3["Mark Status: 'On Leave'"] --> S_LOOP
S4 -- No --> S5["4. Process Punches"]
S5 --> S6{Punches Found?}
S6 -- No --> S_END4["Mark Status: 'Absent'"] --> S_LOOP
S6 -- Yes --> S7(Enter Punch Processing Logic)
end
subgraph "Detailed Punch Processing Logic"
S7 --> D1["A. Fetch Policy Rules for Today"]
D1 --> D2["- Get POLICY_WORK_SCHEDULES (e.g., Start: 9:00, End: 17:00)
- Get ATTENDANCE_POLICY (e.g., Grace In: 15min, Grace Out: 10min)"]
D2 --> D3["B. Analyze Arrival Time"]
D3 --> D4["Fetch first attendance_punch for the day (e.g., 9:25)"]
D4 --> D5{"Is Punch Time <= (Start Time + Grace In)?
Is 9:25 <= (9:00 + 15min)? --> No"}
D5 -- No (Late) --> D6a["Calculate Late Minutes
(e.g., 9:25 - 9:00 = 25 min)
Set Status: 'Late'"]
D5 -- Yes (On Time) --> D6b["Set Status: 'Present'"]
D6a & D6b --> D7["C. Analyze Departure Time"]
D7 --> D8["Fetch last attendance_punch for the day (e.g., 16:45)"]
D8 --> D9{"Is Punch Time >= (End Time - Grace Out)?
Is 16:45 >= (17:00 - 10min)? --> No"}
D9 -- No (Early Exit) --> D10a["Calculate Early Minutes
(e.g., 17:00 - 16:45 = 15 min)
Set Status: 'Early Exit' (or 'Late & Early Exit')"]
D9 -- Yes (On Time) --> D10b["Status: 'On Time'"]
D10a & D10b --> D11["D. Finalize Daily Record"]
D11 --> D12["Create/Update daily_attendance record
with final status and calculated minutes"]
end
D12 --> S_LOOP((Loop to Next Employee))
4. Monthly Payroll Engine (System & Finance)
This flowchart now includes a detailed subgraph for the earnings calculation. It shows precisely how the system accesses the SALARY_POLICIES and INCENTIVE_RULES tables to compute an employee's total gross pay before deductions.
flowchart TD
subgraph "System: Automated Calculation (e.g., 1st of Month)"
P_START((Start Payroll Run)) --> P1["For each 'Active' Employee..."]
P1 --> P2["1. Create master SALARY_CALCULATIONS record"]
P2 --> P3(Enter Detailed Earnings Calculation Logic)
end
subgraph "Detailed Earnings Calculation Logic"
P3 --> E1["2a. Calculate Base Salary"]
E1 --> E2["Fetch employee's assigned SALARY_POLICY"]
E2 --> E3{"Check `salary_type`"}
E3 -- 'fixed' or 'target_based_fixed' --> E4a["Base Pay = `base_salary_amount`
(Pro-rated for unpaid days/mid-month joiners)"]
E3 -- 'commission_only' --> E4b["Base Pay = 0"]
E4a & E4b --> E5["Create EARNING salary_component record for Base Pay"]
E5 --> E6["2b. Calculate Incentives"]
E6 --> E7["Fetch all INCENTIVE_RULES linked to the Salary Policy"]
E7 --> E8["Fetch employee's monthly performance data
(e.g., total sales, product sales, recoveries from other systems)"]
E8 --> E9["Loop through each fetched Incentive Rule"]
E9 --> E10{Rule Condition Met?
e.g., Does `achieved_amount` >= `condition_target_amount`?}
E10 -- Yes --> E11["Calculate Payout based on `payout_type` ('fixed' or 'percentage')"]
E11 --> E12["Create incentive_calculations audit record
(Logs the rule, target, achievement, and payout)"]
E12 --> E13["Create EARNING salary_component record for this incentive"] --> E9
E10 -- No --> E9
E9 -- All Rules Checked --> P4
end
subgraph "Deductions & Finalization"
P4["3. Calculate All Deductions
(Attendance Fines, Advances, etc.)"]
P4 --> P5["4. Final Calculation
Net Pay = (Sum of all Earnings) - (Sum of all Deductions)"]
P5 --> P6["5. Update SALARY_CALCULATIONS status to 'approved'"]
P6 --> P_LOOP((Loop to Next Employee))
end