296 lines
9.8 KiB
SQL
296 lines
9.8 KiB
SQL
-- Hospital Payroll Management System - Database Schema V1.0 & V2.0
|
|
|
|
CREATE DATABASE IF NOT EXISTS hospital_payroll CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE hospital_payroll;
|
|
|
|
-- ==========================================
|
|
-- 1. Authentication & Authorization (RBAC)
|
|
-- ==========================================
|
|
|
|
CREATE TABLE roles (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(50) NOT NULL UNIQUE,
|
|
description VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE permissions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
description VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE role_permissions (
|
|
role_id INT NOT NULL,
|
|
permission_id INT NOT NULL,
|
|
PRIMARY KEY (role_id, permission_id),
|
|
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role_id INT NOT NULL,
|
|
employee_id VARCHAR(50) NULL,
|
|
email VARCHAR(100),
|
|
first_name VARCHAR(100),
|
|
last_name VARCHAR(100),
|
|
status ENUM('active', 'inactive') DEFAULT 'active',
|
|
last_login TIMESTAMP NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
|
|
);
|
|
|
|
-- ==========================================
|
|
-- 2. Master Settings (Income & Deduction)
|
|
-- ==========================================
|
|
|
|
CREATE TABLE income_master (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(20) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
display_order INT DEFAULT 0,
|
|
color_code VARCHAR(20),
|
|
icon_class VARCHAR(50),
|
|
is_taxable BOOLEAN DEFAULT TRUE,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE deduction_master (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(20) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
display_order INT DEFAULT 0,
|
|
color_code VARCHAR(20),
|
|
icon_class VARCHAR(50),
|
|
is_tax_deductible BOOLEAN DEFAULT FALSE,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- ==========================================
|
|
-- 3. Payroll Management
|
|
-- ==========================================
|
|
|
|
CREATE TABLE salary_year (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
year_no INT NOT NULL UNIQUE,
|
|
status ENUM('open', 'closed') DEFAULT 'open',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE salary_month (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
year_id INT NOT NULL,
|
|
month_no INT NOT NULL,
|
|
status ENUM('Draft', 'Imported', 'Pending Review', 'HR Approved', 'Finance Approved', 'Director Approved', 'Locked', 'Published', 'Cancelled') DEFAULT 'Draft',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE(year_id, month_no),
|
|
FOREIGN KEY (year_id) REFERENCES salary_year(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE employee_salary (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
salary_month_id INT NOT NULL,
|
|
employee_code VARCHAR(50) NOT NULL,
|
|
national_id VARCHAR(20) NOT NULL,
|
|
first_name VARCHAR(100) NOT NULL,
|
|
last_name VARCHAR(100) NOT NULL,
|
|
position VARCHAR(100),
|
|
department VARCHAR(100),
|
|
base_salary DECIMAL(10,2) DEFAULT 0.00,
|
|
total_income DECIMAL(10,2) DEFAULT 0.00,
|
|
total_deduction DECIMAL(10,2) DEFAULT 0.00,
|
|
net_salary DECIMAL(10,2) DEFAULT 0.00,
|
|
remark TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE(salary_month_id, employee_code),
|
|
FOREIGN KEY (salary_month_id) REFERENCES salary_month(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE salary_income (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
employee_salary_id INT NOT NULL,
|
|
income_master_id INT NOT NULL,
|
|
amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (employee_salary_id) REFERENCES employee_salary(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (income_master_id) REFERENCES income_master(id) ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE TABLE salary_deduction (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
employee_salary_id INT NOT NULL,
|
|
deduction_master_id INT NOT NULL,
|
|
amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (employee_salary_id) REFERENCES employee_salary(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (deduction_master_id) REFERENCES deduction_master(id) ON DELETE RESTRICT
|
|
);
|
|
|
|
-- ==========================================
|
|
-- 4. Import Data Logging
|
|
-- ==========================================
|
|
|
|
CREATE TABLE import_history (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
salary_month_id INT NOT NULL,
|
|
file_name VARCHAR(255),
|
|
status ENUM('success', 'failed', 'partial') DEFAULT 'success',
|
|
total_records INT DEFAULT 0,
|
|
success_records INT DEFAULT 0,
|
|
error_records INT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
|
|
FOREIGN KEY (salary_month_id) REFERENCES salary_month(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE import_detail (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
import_history_id INT NOT NULL,
|
|
line_number INT,
|
|
national_id VARCHAR(20),
|
|
error_message TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (import_history_id) REFERENCES import_history(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- ==========================================
|
|
-- 5. Logging and Auditing
|
|
-- ==========================================
|
|
|
|
CREATE TABLE audit_log (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NULL,
|
|
action VARCHAR(100) NOT NULL,
|
|
table_name VARCHAR(100),
|
|
record_id INT,
|
|
old_value JSON,
|
|
new_value JSON,
|
|
ip_address VARCHAR(45),
|
|
user_agent VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE salary_slip_log (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NULL,
|
|
employee_salary_id INT NOT NULL,
|
|
action ENUM('view', 'print', 'download', 'email') NOT NULL,
|
|
ip_address VARCHAR(45),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (employee_salary_id) REFERENCES employee_salary(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE report_log (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
report_name VARCHAR(100) NOT NULL,
|
|
parameters JSON,
|
|
ip_address VARCHAR(45),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- ==========================================
|
|
-- 6. Tax Certificates
|
|
-- ==========================================
|
|
|
|
CREATE TABLE tax_certificate (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
year_id INT NOT NULL,
|
|
employee_code VARCHAR(50) NOT NULL,
|
|
total_income DECIMAL(12,2) DEFAULT 0.00,
|
|
total_tax DECIMAL(12,2) DEFAULT 0.00,
|
|
total_provident_fund DECIMAL(12,2) DEFAULT 0.00,
|
|
total_social_security DECIMAL(12,2) DEFAULT 0.00,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (year_id) REFERENCES salary_year(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE tax_certificate_log (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NULL,
|
|
tax_certificate_id INT NOT NULL,
|
|
action ENUM('view', 'print', 'download') NOT NULL,
|
|
ip_address VARCHAR(45),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (tax_certificate_id) REFERENCES tax_certificate(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- ==========================================
|
|
-- 7. File Attachments
|
|
-- ==========================================
|
|
|
|
CREATE TABLE attachments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
related_table VARCHAR(50),
|
|
related_id INT,
|
|
file_name VARCHAR(255) NOT NULL,
|
|
file_path VARCHAR(255) NOT NULL,
|
|
file_type VARCHAR(100),
|
|
file_size INT,
|
|
uploaded_by INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- ==========================================
|
|
-- 8. System Integrations & Settings
|
|
-- ==========================================
|
|
|
|
CREATE TABLE system_setting (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
setting_key VARCHAR(100) NOT NULL UNIQUE,
|
|
setting_value TEXT,
|
|
description VARCHAR(255),
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE external_database (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
connection_name VARCHAR(100) NOT NULL UNIQUE,
|
|
host VARCHAR(100) NOT NULL,
|
|
port INT DEFAULT 3306,
|
|
db_name VARCHAR(100) NOT NULL,
|
|
db_user VARCHAR(100) NOT NULL,
|
|
db_password VARCHAR(255),
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE backup_history (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
file_name VARCHAR(255) NOT NULL,
|
|
file_size INT NOT NULL,
|
|
status ENUM('success', 'failed') DEFAULT 'success',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
-- ==========================================
|
|
-- Default Data Inserts
|
|
-- ==========================================
|
|
|
|
INSERT INTO roles (name, description) VALUES
|
|
('Administrator', 'Full system access'),
|
|
('HR', 'Human Resources access'),
|
|
('Finance', 'Financial review access'),
|
|
('Director', 'Final approval access'),
|
|
('Auditor', 'Read-only access for auditing');
|
|
|
|
-- admin / admin123
|
|
INSERT INTO users (username, password_hash, role_id, first_name, last_name)
|
|
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 'System', 'Administrator');
|