165 lines
6.9 KiB
SQL
165 lines
6.9 KiB
SQL
-- Work Allocation System Database Schema
|
|
-- MySQL 8.0
|
|
|
|
-- Create departments table
|
|
CREATE TABLE IF NOT EXISTS departments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create sub_departments table
|
|
CREATE TABLE IF NOT EXISTS sub_departments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
department_id INT NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_subdept (department_id, name)
|
|
);
|
|
|
|
-- Create activities table (activities belong to sub-departments)
|
|
CREATE TABLE IF NOT EXISTS activities (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
sub_department_id INT NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
unit_of_measurement ENUM('Per Bag', 'Fixed Rate-Per Person') NOT NULL DEFAULT 'Per Bag',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (sub_department_id) REFERENCES sub_departments(id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_activity (sub_department_id, name)
|
|
);
|
|
|
|
-- Create users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
email VARCHAR(100) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role ENUM('SuperAdmin', 'Supervisor', 'Contractor', 'Employee') NOT NULL,
|
|
department_id INT,
|
|
contractor_id INT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
-- Common fields for Employee and Contractor
|
|
phone_number VARCHAR(20),
|
|
aadhar_number VARCHAR(12),
|
|
bank_account_number VARCHAR(30),
|
|
bank_name VARCHAR(100),
|
|
bank_ifsc VARCHAR(20),
|
|
-- Contractor-specific fields
|
|
contractor_agreement_number VARCHAR(50),
|
|
pf_number VARCHAR(30),
|
|
esic_number VARCHAR(30),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (contractor_id) REFERENCES users(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- Create work_allocations table
|
|
CREATE TABLE IF NOT EXISTS work_allocations (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
employee_id INT NOT NULL,
|
|
supervisor_id INT NOT NULL,
|
|
contractor_id INT NOT NULL,
|
|
sub_department_id INT,
|
|
activity VARCHAR(255),
|
|
description TEXT,
|
|
assigned_date DATE NOT NULL,
|
|
completion_date DATE,
|
|
status ENUM('Pending', 'InProgress', 'Completed', 'Cancelled') DEFAULT 'Pending',
|
|
rate DECIMAL(10, 2),
|
|
units DECIMAL(10, 2),
|
|
total_amount DECIMAL(10, 2),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (employee_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (supervisor_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (contractor_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (sub_department_id) REFERENCES sub_departments(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- Create attendance table
|
|
CREATE TABLE IF NOT EXISTS attendance (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
employee_id INT NOT NULL,
|
|
supervisor_id INT NOT NULL,
|
|
check_in_time DATETIME,
|
|
check_out_time DATETIME,
|
|
work_date DATE NOT NULL,
|
|
status ENUM('CheckedIn', 'CheckedOut', 'Absent', 'HalfDay', 'Late') DEFAULT 'CheckedIn',
|
|
remark VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (employee_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (supervisor_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_attendance (employee_id, work_date)
|
|
);
|
|
|
|
-- Create employee_swaps table for tracking employee department transfers
|
|
CREATE TABLE IF NOT EXISTS employee_swaps (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
employee_id INT NOT NULL,
|
|
original_department_id INT NOT NULL,
|
|
target_department_id INT NOT NULL,
|
|
original_contractor_id INT,
|
|
target_contractor_id INT,
|
|
swap_reason ENUM('LeftWork', 'Sick', 'FinishedEarly', 'Other') NOT NULL,
|
|
reason_details VARCHAR(500),
|
|
work_completion_percentage INT DEFAULT 0,
|
|
swap_date DATE NOT NULL,
|
|
swapped_by INT NOT NULL,
|
|
status ENUM('Active', 'Completed', 'Cancelled') DEFAULT 'Active',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
completed_at TIMESTAMP NULL,
|
|
FOREIGN KEY (employee_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (original_department_id) REFERENCES departments(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (target_department_id) REFERENCES departments(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (original_contractor_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (target_contractor_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (swapped_by) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Create contractor_rates table
|
|
CREATE TABLE IF NOT EXISTS contractor_rates (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
contractor_id INT NOT NULL,
|
|
sub_department_id INT,
|
|
activity VARCHAR(255),
|
|
rate DECIMAL(10, 2) NOT NULL,
|
|
effective_date DATE NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (contractor_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (sub_department_id) REFERENCES sub_departments(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- Create standard_rates table (default rates for comparison with contractor rates)
|
|
CREATE TABLE IF NOT EXISTS standard_rates (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
sub_department_id INT,
|
|
activity VARCHAR(255),
|
|
rate DECIMAL(10, 2) NOT NULL,
|
|
effective_date DATE NOT NULL,
|
|
created_by INT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (sub_department_id) REFERENCES sub_departments(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX idx_users_role ON users(role);
|
|
CREATE INDEX idx_users_department ON users(department_id);
|
|
CREATE INDEX idx_users_contractor ON users(contractor_id);
|
|
CREATE INDEX idx_users_phone ON users(phone_number);
|
|
CREATE INDEX idx_users_aadhar ON users(aadhar_number);
|
|
CREATE INDEX idx_work_allocations_employee ON work_allocations(employee_id);
|
|
CREATE INDEX idx_work_allocations_supervisor ON work_allocations(supervisor_id);
|
|
CREATE INDEX idx_work_allocations_contractor ON work_allocations(contractor_id);
|
|
CREATE INDEX idx_work_allocations_date ON work_allocations(assigned_date);
|
|
CREATE INDEX idx_work_allocations_status ON work_allocations(status);
|
|
CREATE INDEX idx_attendance_employee ON attendance(employee_id);
|
|
CREATE INDEX idx_attendance_date ON attendance(work_date);
|
|
CREATE INDEX idx_attendance_status ON attendance(status);
|
|
CREATE INDEX idx_contractor_rates_contractor ON contractor_rates(contractor_id);
|
|
CREATE INDEX idx_contractor_rates_date ON contractor_rates(effective_date);
|
|
CREATE INDEX idx_standard_rates_subdept ON standard_rates(sub_department_id);
|
|
CREATE INDEX idx_standard_rates_date ON standard_rates(effective_date);
|
|
CREATE INDEX idx_standard_rates_created_by ON standard_rates(created_by);
|