# HR Management System - Database Schema Reference

## Table Relationships Diagram

```
tbl_employees (Core)
├── tbl_employee_salary_templates → tbl_salary_templates
├── tbl_employee_shifts → tbl_shifts
├── tbl_attendance → tbl_shifts
├── tbl_payroll → tbl_salary_templates
└── tbl_payroll_adjustments
```

---

## Complete Table Schemas

### 1. tbl_salary_templates
**Purpose:** Master data for salary structures

| Column | Type | Constraint | Description |
|:---|:---|:---|:---|
| id | INT | PRIMARY KEY, AUTO_INCREMENT | Unique identifier |
| template_name | VARCHAR(255) | NOT NULL, UNIQUE | e.g., "Senior Level" |
| description | TEXT | NULL | Template description |
| **ALLOWANCES** | | | |
| basic_salary | DECIMAL(10,2) | DEFAULT 0 | Base monthly salary |
| hra | DECIMAL(10,2) | DEFAULT 0 | House Rent Allowance |
| da | DECIMAL(10,2) | DEFAULT 0 | Dearness Allowance |
| medical_allowance | DECIMAL(10,2) | DEFAULT 0 | Medical benefits |
| conveyance | DECIMAL(10,2) | DEFAULT 0 | Transportation allowance |
| other_allowances | DECIMAL(10,2) | DEFAULT 0 | Misc allowances |
| **DEDUCTIONS** | | | |
| pf_deduction | DECIMAL(10,2) | DEFAULT 0 | Provident Fund |
| insurance_deduction | DECIMAL(10,2) | DEFAULT 0 | Insurance premium |
| tax_deduction | DECIMAL(10,2) | DEFAULT 0 | Income tax |
| other_deductions | DECIMAL(10,2) | DEFAULT 0 | Other deductions |
| status | ENUM | DEFAULT 'active' | 'active' or 'inactive' |
| created_by | INT | NULL | User who created |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| updated_by | INT | NULL | Last user to update |
| updated_at | TIMESTAMP | NULL, ON UPDATE | Last update timestamp |

**Indexes:**
- PRIMARY: id
- KEY: idx_status (status)

---

### 2. tbl_employee_salary_templates
**Purpose:** Links employees to salary templates

| Column | Type | Constraint | Description |
|:---|:---|:---|:---|
| id | INT | PRIMARY KEY, AUTO_INCREMENT | Unique identifier |
| employee_id | INT | NOT NULL, FK → tbl_employees | Employee reference |
| salary_template_id | INT | NOT NULL, FK → tbl_salary_templates | Template reference |
| assigned_date | DATE | NOT NULL | Date of assignment |
| effective_date | DATE | NULL | When template becomes effective |
| status | ENUM | DEFAULT 'active' | Assignment status |
| assigned_by | INT | NULL, FK → tbl_employees | Admin who assigned |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |

**Indexes:**
- PRIMARY: id
- UNIQUE: (employee_id, salary_template_id)

**Example Data:**
| employee_id | salary_template_id | assigned_date | status |
|:---|:---|:---|:---|
| 1 | 2 | 2026-01-01 | active |
| 2 | 1 | 2026-01-15 | active |

---

### 3. tbl_shifts
**Purpose:** Define shift timings and allowances

| Column | Type | Constraint | Description |
|:---|:---|:---|:---|
| id | INT | PRIMARY KEY, AUTO_INCREMENT | Unique identifier |
| shift_name | VARCHAR(100) | NOT NULL, UNIQUE | e.g., "Morning Shift" |
| start_time | TIME | NOT NULL | Shift start (06:00:00) |
| end_time | TIME | NOT NULL | Shift end (14:00:00) |
| duration_hours | DECIMAL(3,1) | NULL | Calculated hours |
| break_minutes | INT | DEFAULT 0 | Break duration in minutes |
| shift_allowance | DECIMAL(10,2) | DEFAULT 0 | Extra allowance (₹100) |
| status | ENUM | DEFAULT 'active' | 'active' or 'inactive' |
| created_by | INT | NULL | Creator |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation time |
| updated_by | INT | NULL | Last updater |
| updated_at | TIMESTAMP | NULL, ON UPDATE | Last update time |

**Pre-loaded Shifts:**
| shift_name | start_time | end_time | shift_allowance |
|:---|:---|:---|:---|
| Morning Shift | 06:00:00 | 14:00:00 | 0.00 |
| Evening Shift | 14:00:00 | 22:00:00 | 100.00 |
| Night Shift | 22:00:00 | 06:00:00 | 200.00 |
| Flexible Shift | 09:00:00 | 17:00:00 | 0.00 |

---

### 4. tbl_employee_shifts
**Purpose:** Assign shifts to employees

| Column | Type | Constraint | Description |
|:---|:---|:---|:---|
| id | INT | PRIMARY KEY, AUTO_INCREMENT | Unique identifier |
| employee_id | INT | NOT NULL, FK → tbl_employees | Employee reference |
| shift_id | INT | NOT NULL, FK → tbl_shifts | Shift reference |
| assigned_date | DATE | NOT NULL | Assignment date |
| status | ENUM | DEFAULT 'active' | Assignment status |
| assigned_by | INT | NULL, FK → tbl_employees | Admin who assigned |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation time |

**Indexes:**
- PRIMARY: id
- UNIQUE: (employee_id, shift_id)

---

### 5. tbl_attendance
**Purpose:** Daily attendance records

| Column | Type | Constraint | Description |
|:---|:---|:---|:---|
| id | INT | PRIMARY KEY, AUTO_INCREMENT | Unique identifier |
| employee_id | INT | NOT NULL, FK → tbl_employees | Employee reference |
| attendance_date | DATE | NOT NULL | Date of attendance |
| status | ENUM | NOT NULL, DEFAULT 'present' | 'present', 'absent', 'leave', 'half_day', 'weekend', 'holiday' |
| shift_id | INT | NULL, FK → tbl_shifts | Shift reference |
| check_in_time | TIME | NULL | Time of arrival |
| check_out_time | TIME | NULL | Time of departure |
| notes | TEXT | NULL | Comments/remarks |
| created_by | INT | NULL | Marked by |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation time |
| updated_by | INT | NULL | Last updater |
| updated_at | TIMESTAMP | NULL, ON UPDATE | Last update time |

**Indexes:**
- PRIMARY: id
- UNIQUE: (employee_id, attendance_date)
- KEY: idx_emp_date
- KEY: idx_status

**Status Values:**
- `present` - Employee worked full day
- `absent` - No attendance
- `leave` - Authorized leave
- `half_day` - Half day absence
- `weekend` - Regular weekend
- `holiday` - Public/company holiday

---

### 6. tbl_attendance_summary
**Purpose:** Monthly aggregated attendance summary

| Column | Type | Constraint | Description |
|:---|:---|:---|:---|
| id | INT | PRIMARY KEY, AUTO_INCREMENT | Unique identifier |
| employee_id | INT | NOT NULL, FK → tbl_employees | Employee reference |
| month_year | DATE | NOT NULL | First day of month (YYYY-MM-01) |
| total_days | INT | DEFAULT 0 | Total days in month |
| present_days | INT | DEFAULT 0 | Counted from status='present' |
| absent_days | INT | DEFAULT 0 | Counted from status='absent' |
| leave_days | INT | DEFAULT 0 | Counted from status='leave' |
| half_days | INT | DEFAULT 0 | Counted from status='half_day' |
| weekend_days | INT | DEFAULT 0 | Counted from status='weekend' |
| holiday_days | INT | DEFAULT 0 | Counted from status='holiday' |
| working_days | INT | DEFAULT 0 | total_days - weekends - holidays |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation time |
| updated_at | TIMESTAMP | NULL, ON UPDATE | Last update time |

**Indexes:**
- PRIMARY: id
- UNIQUE: (employee_id, month_year)

**Example Calculation:**
```
month_year: 2026-02-01
total_days: 28
present_days: 24
absent_days: 1
leave_days: 1
half_days: 1
weekend_days: 2
holiday_days: 0
working_days: 26 (28 - 2 weekends)
```

---

### 7. tbl_payroll
**Purpose:** Monthly payroll records for each employee

| Column | Type | Constraint | Description |
|:---|:---|:---|:---|
| id | INT | PRIMARY KEY, AUTO_INCREMENT | Unique identifier |
| employee_id | INT | NOT NULL, FK → tbl_employees | Employee reference |
| salary_month | DATE | NOT NULL | Month for payroll (YYYY-MM-01) |
| salary_template_id | INT | NULL, FK → tbl_salary_templates | Template used |
| **ATTENDANCE** | | | |
| working_days | INT | DEFAULT 0 | Calculated working days |
| attendance_days | INT | DEFAULT 0 | Actual attendance days |
| leave_days | INT | DEFAULT 0 | Leave count |
| half_days | INT | DEFAULT 0 | Half-day count |
| absence_days | INT | DEFAULT 0 | Absence count |
| per_day_salary | DECIMAL(10,2) | DEFAULT 0 | Basic / working_days |
| **EARNINGS** | | | |
| basic_salary | DECIMAL(10,2) | DEFAULT 0 | From template |
| hra | DECIMAL(10,2) | DEFAULT 0 | From template |
| da | DECIMAL(10,2) | DEFAULT 0 | From template |
| allowances | DECIMAL(10,2) | DEFAULT 0 | From template |
| shift_allowance | DECIMAL(10,2) | DEFAULT 0 | shift × attendance_days |
| gross_salary | DECIMAL(10,2) | DEFAULT 0 | Sum of all earnings |
| **DEDUCTIONS** | | | |
| pf_deduction | DECIMAL(10,2) | DEFAULT 0 | From template |
| insurance_deduction | DECIMAL(10,2) | DEFAULT 0 | From template |
| tax_deduction | DECIMAL(10,2) | DEFAULT 0 | From template |
| other_deductions | DECIMAL(10,2) | DEFAULT 0 | From template |
| total_deductions | DECIMAL(10,2) | DEFAULT 0 | Sum of all deductions |
| net_salary | DECIMAL(10,2) | DEFAULT 0 | gross - deductions + shift |
| **STATUS** | | | |
| status | ENUM | DEFAULT 'draft' | 'draft', 'approved', 'paid' |
| notes | TEXT | NULL | Comments |
| **WORKFLOW** | | | |
| created_by | INT | NULL | Generator |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Generation time |
| approved_by | INT | NULL | Approver |
| approved_at | TIMESTAMP | NULL | Approval timestamp |
| paid_date | DATE | NULL | Payment date |
| updated_at | TIMESTAMP | NULL, ON UPDATE | Last update |

**Indexes:**
- PRIMARY: id
- UNIQUE: (employee_id, salary_month)
- KEY: idx_month
- KEY: idx_status

**Payroll Formula:**
```
gross_salary = basic + hra + da + allowances + shift_allowance
net_salary = gross_salary - total_deductions
```

---

### 8. tbl_payroll_adjustments
**Purpose:** Track bonus, deductions, and corrections to payroll

| Column | Type | Constraint | Description |
|:---|:---|:---|:---|
| id | INT | PRIMARY KEY, AUTO_INCREMENT | Unique identifier |
| payroll_id | INT | NOT NULL, FK → tbl_payroll | Payroll reference |
| adjustment_type | ENUM | DEFAULT 'correction' | 'bonus', 'incentive', 'deduction', 'correction' |
| amount | DECIMAL(10,2) | NOT NULL | Adjustment amount |
| description | TEXT | NULL | Reason for adjustment |
| created_by | INT | NULL, FK → tbl_employees | Who made adjustment |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation time |

**Adjustment Types:**
- `bonus` - Performance bonus
- `incentive` - Commission/incentive
- `deduction` - Advance/damage recovery
- `correction` - Manual correction

**Example:**
```
payroll_id: 1
adjustment_type: bonus
amount: 5000
description: "Performance bonus - Q1 2026"
```

---

## Key Relationships

### Employee → Salary Template
```sql
SELECT e.name, st.template_name, st.basic_salary
FROM tbl_employees e
JOIN tbl_employee_salary_templates est ON e.id = est.employee_id
JOIN tbl_salary_templates st ON est.salary_template_id = st.id
WHERE est.status = 'active' AND e.status = 'active';
```

### Employee → Shift
```sql
SELECT e.name, s.shift_name, s.start_time, s.end_time
FROM tbl_employees e
JOIN tbl_employee_shifts es ON e.id = es.employee_id
JOIN tbl_shifts s ON es.shift_id = s.id
WHERE es.status = 'active';
```

### Monthly Payroll with Template Details
```sql
SELECT
    e.name,
    p.salary_month,
    st.template_name,
    p.attendance_days,
    p.gross_salary,
    p.total_deductions,
    p.net_salary,
    p.status
FROM tbl_payroll p
JOIN tbl_employees e ON p.employee_id = e.id
LEFT JOIN tbl_salary_templates st ON p.salary_template_id = st.id
WHERE p.salary_month = '2026-02-01'
ORDER BY e.name;
```

---

## Data Integrity Rules

### Constraints:

1. **Salary Templates**
   - Template names must be unique
   - Cannot delete if assigned to active employees
   - Status can be toggled without affecting assignments

2. **Employee Assignments**
   - One active template per employee (enforced by UI logic)
   - Assignment date must be ≤ effective date
   - Cannot assign inactive templates

3. **Attendance**
   - Only one attendance record per employee per day
   - Cannot mark future dates
   - Attendance changes are tracked in updated_at

4. **Payroll**
   - One payroll per employee per month
   - Cannot have duplicate salary_month + employee_id
   - Status progression: draft → approved → paid
   - Cannot revert status once approved

---

## Useful Aggregate Queries

### Monthly Payroll Summary
```sql
SELECT
    salary_month,
    COUNT(*) as employees,
    SUM(net_salary) as total_payroll,
    AVG(net_salary) as avg_salary,
    MIN(net_salary) as min_salary,
    MAX(net_salary) as max_salary
FROM tbl_payroll
GROUP BY salary_month
ORDER BY salary_month DESC;
```

### Attendance by Status
```sql
SELECT
    DATE_FORMAT(attendance_date, '%Y-%m') as month,
    status,
    COUNT(*) as count
FROM tbl_attendance
GROUP BY DATE_FORMAT(attendance_date, '%Y-%m'), status
ORDER BY month DESC, status;
```

### Employee Salary Comparison
```sql
SELECT
    e.name,
    st.template_name,
    st.basic_salary,
    (st.basic_salary + st.hra + st.da + st.medical_allowance + st.conveyance) as gross,
    (st.pf_deduction + st.insurance_deduction + st.tax_deduction) as deductions
FROM tbl_employees e
LEFT JOIN tbl_employee_salary_templates est ON e.id = est.employee_id AND est.status = 'active'
LEFT JOIN tbl_salary_templates st ON est.salary_template_id = st.id
WHERE e.status = 'active'
ORDER BY st.basic_salary DESC;
```

---

## Migration Checkpoints

After running migration, verify:

```sql
-- Check all tables exist
SHOW TABLES LIKE 'tbl_%';

-- Verify foreign keys
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME IS NOT NULL
  AND TABLE_SCHEMA = DATABASE();

-- Check sample shifts loaded
SELECT COUNT(*) FROM tbl_shifts;

-- Check sample salary templates loaded
SELECT COUNT(*) FROM tbl_salary_templates;
```

---

**Schema Version:** 1.0
**Created:** 2026-02-16
**Database Type:** MySQL 5.7+
**Character Set:** utf8mb4
**Status:** Production Ready
