wehub-resource-sync
254 lines
12 KiB
PL/PgSQL
254 lines
12 KiB
PL/PgSQL
-- HR Database Schema
|
|
|
|
-- Table: Departments
|
|
-- Stores information about different departments within the organization.
|
|
CREATE TABLE Departments (
|
|
department_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for the department (AUTO_INCREMENT for MySQL/SQL Server, SERIAL for PostgreSQL)
|
|
department_name VARCHAR(100) NOT NULL UNIQUE, -- Name of the department, must be unique
|
|
location VARCHAR(255), -- Physical location of the department
|
|
phone_number VARCHAR(20), -- Contact number for the department
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when the record was created
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- Timestamp for last update (for MySQL, similar triggers needed for others)
|
|
);
|
|
|
|
-- Table: JobTitles
|
|
-- Stores information about different job titles/roles.
|
|
CREATE TABLE JobTitles (
|
|
job_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for the job title
|
|
job_title VARCHAR(100) NOT NULL UNIQUE, -- Name of the job title, must be unique
|
|
min_salary DECIMAL(10, 2), -- Minimum salary for this job title
|
|
max_salary DECIMAL(10, 2), -- Maximum salary for this job title
|
|
job_description TEXT, -- Detailed description of the job
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Table: Employees
|
|
-- Core table storing employee personal and professional information.
|
|
CREATE TABLE Employees (
|
|
employee_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for the employee
|
|
first_name VARCHAR(50) NOT NULL, -- Employee's first name
|
|
last_name VARCHAR(50) NOT NULL, -- Employee's last name
|
|
email VARCHAR(100) NOT NULL UNIQUE, -- Employee's email address, must be unique
|
|
phone_number VARCHAR(20), -- Employee's phone number
|
|
hire_date DATE NOT NULL, -- Date when the employee was hired
|
|
job_id INT NOT NULL, -- Foreign Key to JobTitles
|
|
department_id INT NOT NULL, -- Foreign Key to Departments
|
|
manager_id INT, -- Foreign Key to Employees (self-referencing for managers)
|
|
salary DECIMAL(10, 2) NOT NULL, -- Employee's current salary
|
|
date_of_birth DATE, -- Employee's date of birth
|
|
gender ENUM('Male', 'Female', 'Other', 'Prefer not to say'), -- Employee's gender (ENUM is MySQL specific, use CHECK constraint for others)
|
|
address VARCHAR(255), -- Employee's home address
|
|
city VARCHAR(100), -- Employee's city
|
|
state VARCHAR(100), -- Employee's state/province
|
|
zip_code VARCHAR(20), -- Employee's zip/postal code
|
|
country VARCHAR(100), -- Employee's country
|
|
is_active BOOLEAN DEFAULT TRUE, -- Is the employee currently active?
|
|
termination_date DATE, -- Date of termination if applicable
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (job_id) REFERENCES JobTitles(job_id),
|
|
FOREIGN KEY (department_id) REFERENCES Departments(department_id),
|
|
FOREIGN KEY (manager_id) REFERENCES Employees(employee_id)
|
|
);
|
|
|
|
-- Table: Dependents
|
|
-- Stores information about employee's dependents (for benefits, emergency contacts, etc.).
|
|
CREATE TABLE Dependents (
|
|
dependent_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for the dependent
|
|
employee_id INT NOT NULL, -- Foreign Key to Employees
|
|
first_name VARCHAR(50) NOT NULL,
|
|
last_name VARCHAR(50) NOT NULL,
|
|
relationship VARCHAR(50), -- e.g., 'Spouse', 'Child', 'Parent'
|
|
date_of_birth DATE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) ON DELETE CASCADE -- If employee deleted, dependents also deleted
|
|
);
|
|
|
|
-- Table: Benefits
|
|
-- Defines different types of benefits offered by the company.
|
|
CREATE TABLE Benefits (
|
|
benefit_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for the benefit
|
|
benefit_name VARCHAR(100) NOT NULL UNIQUE, -- Name of the benefit (e.g., 'Health Insurance', 'Dental', '401k')
|
|
description TEXT, -- Description of the benefit
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Table: EmployeeBenefits
|
|
-- Links employees to the benefits they are enrolled in.
|
|
CREATE TABLE EmployeeBenefits (
|
|
employee_id INT NOT NULL,
|
|
benefit_id INT NOT NULL,
|
|
enrollment_date DATE, -- Date when the employee enrolled
|
|
coverage_start_date DATE, -- Date when coverage begins
|
|
coverage_end_date DATE, -- Date when coverage ends
|
|
PRIMARY KEY (employee_id, benefit_id), -- Composite primary key
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (benefit_id) REFERENCES Benefits(benefit_id)
|
|
);
|
|
|
|
-- Table: PerformanceReviews
|
|
-- Stores information about employee performance reviews.
|
|
CREATE TABLE PerformanceReviews (
|
|
review_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for the review
|
|
employee_id INT NOT NULL, -- Foreign Key to Employees (employee being reviewed)
|
|
reviewer_id INT NOT NULL, -- Foreign Key to Employees (employee conducting the review)
|
|
review_date DATE NOT NULL, -- Date of the review
|
|
rating DECIMAL(3, 1), -- Numerical rating (e.g., 1.0-5.0)
|
|
comments TEXT, -- General comments
|
|
goals_achieved TEXT, -- Text describing goals achieved
|
|
development_areas TEXT, -- Text describing areas for development
|
|
next_review_date DATE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id),
|
|
FOREIGN KEY (reviewer_id) REFERENCES Employees(employee_id)
|
|
);
|
|
|
|
-- Table: TrainingPrograms
|
|
-- Defines available training programs.
|
|
CREATE TABLE TrainingPrograms (
|
|
program_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for the training program
|
|
program_name VARCHAR(150) NOT NULL UNIQUE, -- Name of the program
|
|
description TEXT,
|
|
duration_hours INT, -- Duration in hours
|
|
cost DECIMAL(10, 2),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Table: EmployeeTraining
|
|
-- Records employee's participation in training programs.
|
|
CREATE TABLE EmployeeTraining (
|
|
employee_id INT NOT NULL,
|
|
program_id INT NOT NULL,
|
|
completion_date DATE, -- Date training was completed
|
|
grade VARCHAR(10), -- Grade or status (e.g., 'Pass', 'Fail', 'In Progress')
|
|
PRIMARY KEY (employee_id, program_id), -- Composite primary key
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (program_id) REFERENCES TrainingPrograms(program_id)
|
|
);
|
|
|
|
-- Table: Absences
|
|
-- Tracks employee absences (sick leave, vacation, etc.).
|
|
CREATE TABLE Absences (
|
|
absence_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for the absence record
|
|
employee_id INT NOT NULL, -- Foreign Key to Employees
|
|
absence_type VARCHAR(50) NOT NULL, -- e.g., 'Sick Leave', 'Vacation', 'Unpaid Leave'
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
reason TEXT,
|
|
approved_by INT, -- Foreign Key to Employees (approving manager)
|
|
status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending', -- (ENUM is MySQL specific, use CHECK constraint for others)
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id),
|
|
FOREIGN KEY (approved_by) REFERENCES Employees(employee_id)
|
|
);
|
|
|
|
-- Table: EmergencyContacts
|
|
-- Stores emergency contact information for employees.
|
|
CREATE TABLE EmergencyContacts (
|
|
contact_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique identifier for the contact
|
|
employee_id INT NOT NULL, -- Foreign Key to Employees
|
|
name VARCHAR(100) NOT NULL,
|
|
relationship VARCHAR(50),
|
|
phone_number VARCHAR(20) NOT NULL,
|
|
email VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Table: Credentials
|
|
-- Stores information about employee's professional licenses, certifications etc.
|
|
CREATE TABLE Credentials (
|
|
credential_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
employee_id INT NOT NULL,
|
|
credential_name VARCHAR(100) NOT NULL,
|
|
issuing_organization VARCHAR(100),
|
|
issue_date DATE,
|
|
expiration_date DATE,
|
|
credential_number VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Table: Skills
|
|
-- Defines various skills that employees might possess.
|
|
CREATE TABLE Skills (
|
|
skill_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
skill_name VARCHAR(100) NOT NULL UNIQUE,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Table: EmployeeSkills
|
|
-- Associates employees with their skills and proficiency levels.
|
|
CREATE TABLE EmployeeSkills (
|
|
employee_id INT NOT NULL,
|
|
skill_id INT NOT NULL,
|
|
proficiency_level ENUM('Beginner', 'Intermediate', 'Advanced', 'Expert'), -- (ENUM is MySQL specific, use CHECK constraint for others)
|
|
years_experience DECIMAL(4, 1),
|
|
PRIMARY KEY (employee_id, skill_id),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (skill_id) REFERENCES Skills(skill_id)
|
|
);
|
|
|
|
|
|
-- DDL for PostgreSQL Specifics (if using PostgreSQL, replace AUTO_INCREMENT with SERIAL, and ENUM with CHECK)
|
|
/*
|
|
-- Example for PostgreSQL:
|
|
CREATE TABLE Departments (
|
|
department_id SERIAL PRIMARY KEY,
|
|
department_name VARCHAR(100) NOT NULL UNIQUE,
|
|
location VARCHAR(255),
|
|
phone_number VARCHAR(20),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- For gender/status ENUMs in PostgreSQL, use CHECK constraints:
|
|
gender VARCHAR(20),
|
|
CONSTRAINT check_gender CHECK (gender IN ('Male', 'Female', 'Other', 'Prefer not to say')),
|
|
|
|
status VARCHAR(20) DEFAULT 'Pending',
|
|
CONSTRAINT check_status CHECK (status IN ('Pending', 'Approved', 'Rejected')),
|
|
|
|
proficiency_level VARCHAR(20),
|
|
CONSTRAINT check_proficiency_level CHECK (proficiency_level IN ('Beginner', 'Intermediate', 'Advanced', 'Expert')),
|
|
|
|
-- To automatically update 'updated_at' in PostgreSQL, you typically use a trigger:
|
|
CREATE OR REPLACE FUNCTION update_timestamp()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER update_departments_timestamp
|
|
BEFORE UPDATE ON Departments
|
|
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
|
|
-- (Repeat for other tables)
|
|
*/
|