Employee Attendance Tracker – SQL Project Example

Overview

An Employee Attendance Tracker is a database system used by organizations to monitor employee presence, punctuality, and working hours. Building this system with SQL helps learners understand data modeling, relationship management, time tracking, and reporting—all using real-world business logic.

Key Features of an Attendance Tracker

• Maintain employee records
• Record daily check-in and check-out times
• Track working hours
• Monitor absences, leaves, and late arrivals
• Generate attendance reports

Database Design – Tables and Structure

1. Employees Table

Stores basic information about each employee.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    position VARCHAR(50),
    email VARCHAR(100),
    status VARCHAR(20) DEFAULT 'Active'
);

2. Attendance Table

Tracks daily attendance including check-in and check-out times.

CREATE TABLE attendance (
    attendance_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    date DATE,
    check_in TIME,
    check_out TIME,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

3. Leaves Table

Optional table to log approved leaves (e.g., vacation, sick leave).

CREATE TABLE leaves (
    leave_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    leave_date DATE,
    leave_type VARCHAR(50),
    reason TEXT,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Sample SQL Queries

Record Check-In

INSERT INTO attendance (employee_id, date, check_in)
VALUES (1, CURDATE(), CURTIME());

Record Check-Out

UPDATE attendance
SET check_out = CURTIME()
WHERE employee_id = 1 AND date = CURDATE();

Calculate Total Working Hours Per Day

SELECT employee_id, date,
TIMEDIFF(check_out, check_in) AS work_duration
FROM attendance
WHERE check_out IS NOT NULL;

Find Late Arrivals (e.g., after 9:00 AM)

SELECT e.first_name, e.last_name, a.date, a.check_in
FROM attendance a
JOIN employees e ON a.employee_id = e.employee_id
WHERE a.check_in > '09:00:00';

Monthly Attendance Summary

SELECT employee_id, COUNT(*) AS days_present
FROM attendance
WHERE MONTH(date) = MONTH(CURDATE())
GROUP BY employee_id;

Best Practices

• Use constraints to ensure data accuracy (e.g., no check-out before check-in)
• Add indexes on employee_id and date for performance
• Normalize data where possible (e.g., use enums or lookup tables for leave types)
• Ensure referential integrity with foreign keys
• Implement triggers to auto-validate or log data changes if needed