Module Relationship Diagram (ERD) (Attendance Management)
erDiagram
attendance_punches {
int id PK "1120"
int employee_id FK "101"
timestamp punch_time "2025-09-15 09:12:30"
enum punch_type "in"
}
daily_attendances {
int id PK "2340"
int employee_id FK "101"
date attendance_date "2025-09-15"
time first_in_time "09:12"
time last_out_time "17:02"
enum status "Present, Absent, Leave, Half-Day"
int total_work_minutes "410"
}
monthly_attendance_summaries {
int id PK "45"
int employee_id FK "101"
year year "2025"
int month "9"
int total_present_days "21"
int total_late_days "1"
int total_leave_days "1"
}
employee_leaves {
int id PK "55"
int employee_id FK "101"
int leave_type_id FK "2"
date start_date "2025-09-16"
date end_date "2025-09-16"
enum status "Approved, Pending, Rejected"
int approved_by FK "12"
}
leave_types {
int id PK "2"
string name "Sick Leave"
boolean is_paid "true"
}
employees }o--|| attendance_punches : "makes"
employees }o--|| daily_attendances : "has summary for"
employees }o--|| employee_leaves : "requests"
leave_types }o--|| employee_leaves : "is of type"
employees }o--|| monthly_attendance_summaries : "has (Monthly Report)"
5. Automated Attendance Processing
flowchart TD
subgraph "Daily Employee Action"
A_START((Employee Action
Punches IN/OUT)) -- via Biometric/App --> A1["Raw record created in
attendance_punches"]
end
subgraph "Nightly Automated Processing Job"
B_START((Trigger:
Midnight Scheduler)) --> B1["1. Loop through each 'Active' Employee"]
B1 --> B2{"2. Check for today's status"}
B2 -- Is it a Weekend/Holiday? --> B3a["3a. Create daily_attendances record
Status: 'weekend'/'holiday'"] --> B_UPDATE
B2 -- Is there an Approved Leave? --> B3b["3b. Create daily_attendances record
Status: 'on_leave'"] --> B_UPDATE
B2 -- Normal Workday --> B3c["3c. Fetch all of today's
attendance_punches"]
B3c --> B4{Punches Found?}
B4 -- No --> B5a["4a. Create daily_attendances record
Status: 'absent'"] --> B_UPDATE
B4 -- Yes --> B5b["4b. Process Punches"]
B5b --> B6["- Find earliest IN & latest OUT
- Calculate work & late minutes"]
B6 --> B7["- Determine final status ('present' or 'late')"]
B7 --> B8["5. Create daily_attendances record
with all calculated data"] --> B_UPDATE
B_UPDATE["6. Update monthly_attendance_summaries"]
B_UPDATE --> B_LOOP((Loop to Next Employee))
end
Bulk insertion of Attendance
flowchart TD
subgraph "Phase 1: Data Upload (Manual Trigger)"
A_START((Start:
HR uploads monthly attendance file)) --> A1["1. System parses the file (CSV/TXT)"]
A1 --> A2["2. System performs a BULK INSERT of all records into the attendance_punches table"]
A2 --> A3["3. HR triggers the 'Process Attendance' job for the specified month"]
end
subgraph "Phase 2: Automated Processing Job (for the whole month)"
B_START((Trigger:
Manual job start)) --> B1["1. Job defines the date range (e.g., Sep 1 to Sep 30)"]
B1 --> B2["2. Loop through each DAY in the date range"]
B2 --> B3["3. For each day, loop through each EMPLOYEE"]
B3 --> B4["4. Run the same daily logic as before:"]
B4 --> B4a["- Check for Holiday/Weekend/Leave"]
B4a --> B4b["- Fetch all punches for that employee on that day"]
B4b --> B4c["- Calculate status, times, and metrics"]
B4c --> B5["5. Update or Create the record in daily_attendances for that day"]
B5 --> B6((Next Employee)) -- Loop back --> B3
B6 -- Day Complete --> B7((Next Day)) -- Loop back --> B2
B7 -- Month Complete --> B8["6. Recalculate the entire monthly_attendance_summaries table for all affected employees"]
B8 --> B_END((End:
Entire month's attendance is processed))
end
A3 --> B_START