Employee Management System | Omer Icar Portfolio

About the Project

The Employee Management System is a robust database solution designed specifically for retail operations. This project focuses on efficient data modeling and SQL queries to manage employee information, shift scheduling, availability tracking, and contract hour management. Built with a normalized relational database structure, it ensures data integrity while providing fast query performance for day-to-day operations.

Key Features

Employee Records

Comprehensive storage of personal details, contact info, and employment history

Shift Scheduling

Dynamic shift assignment with conflict detection and automatic notifications

Availability Tracking

Employee availability management for optimal shift planning

Contract Hours

Automatic tracking of contracted vs actual hours worked

Reporting System

SQL queries for attendance reports, payroll data, and workforce analytics

Data Integrity

Constraints, triggers, and stored procedures ensure data consistency

Database Schema

The system uses a normalized relational database with the following main entities:

Employees: Personal information, contact details, hire date, position
Shifts: Shift details, start/end times, assigned employees
Availability: Employee available days and preferred hours
Contracts: Employment contracts with weekly/monthly hour allocations
Departments: Store departments and team assignments
Attendance: Clock in/out records and time tracking

SQL Capabilities

The database includes advanced SQL features:

Complex Queries: Multi-table joins for comprehensive reporting
Views: Pre-defined views for common queries and reporting
Stored Procedures: Automated processes for scheduling and calculations
Triggers: Automatic updates when shifts are modified
Indexes: Optimized for fast search and retrieval
Constraints: Foreign keys, check constraints for data validation

Use Cases

📊 Weekly Schedule Generation

Automatically generate optimal weekly schedules based on employee availability, contract hours, and business needs.

📈 Payroll Calculations

Calculate hours worked, overtime, and generate payroll reports with a single query.

🔔 Shift Notifications

Query upcoming shifts for automated email/SMS notifications to employees.

📋 Compliance Tracking

Ensure compliance with labor laws by tracking break times, maximum hours, and rest periods.

Technologies Used

SQL MySQL Database Design ERD Modeling Stored Procedures Triggers Indexing

Design Principles

The database follows best practices for relational design:

Normalization: 3NF (Third Normal Form) to eliminate redundancy
Referential Integrity: Foreign keys maintain relationships
Atomicity: Each column stores atomic values
ACID Compliance: Transactions ensure data consistency
Query Optimization: Indexed columns for frequent searches

Project Challenges

The main challenge was designing a schema that could handle complex scheduling scenarios while maintaining performance. This required careful indexing strategies and query optimization. Another challenge was implementing business rules through triggers and constraints to ensure data integrity without sacrificing flexibility.

What I Learned

This project deepened my understanding of database design principles, SQL optimization, and data modeling. I gained hands-on experience with complex queries, stored procedures, and performance tuning. The project taught me how to translate real-world business requirements into efficient database structures and the importance of planning schema design before implementation to avoid costly refactoring.

View on GitHub