Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

数据库设计

设计概述

本系统采用PostgreSQL作为主数据库，Redis作为缓存数据库，设计遵循第三范式，确保数据一致性、完整性和可扩展性。数据库设计支持完整的HR业务流程，包括用户管理、员工管理、组织架构、考勤管理、招聘管理、薪酬管理等核心功能模块。

设计原则

1. 数据完整性

  • 实体完整性: 主键约束确保实体唯一性
  • 参照完整性: 外键约束确保数据关联正确性
  • 域完整性: 数据类型和约束确保数据有效性
  • 业务完整性: 业务规则约束确保数据逻辑正确性

2. 数据一致性

  • ACID特性: 支持原子性、一致性、隔离性、持久性
  • 事务管理: 支持事务回滚和提交
  • 并发控制: 支持多用户并发访问
  • 数据同步: 确保主从数据库数据同步

3. 性能优化

  • 索引设计: 关键字段建立合适索引
  • 查询优化: 优化复杂查询性能
  • 分区策略: 大表分区提高查询效率
  • 缓存机制: 多级缓存提高访问速度

4. 安全设计

  • 数据加密: 敏感数据加密存储
  • 访问控制: 基于角色的数据访问控制
  • 审计日志: 完整的数据操作审计
  • 备份恢复: 定期备份和快速恢复

核心实体设计

1. 用户管理模块

1.1 用户表 (user)

CREATE TABLE "user" (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'employee' CHECK (role IN ('admin', 'hr', 'manager', 'employee')),
    department_id INTEGER REFERENCES "department"(id),
    position_id INTEGER REFERENCES "position"(id),
    phone VARCHAR(20),
    avatar_url VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE,
    notes TEXT
);

-- 索引
CREATE INDEX idx_user_username ON "user"(username);
CREATE INDEX idx_user_email ON "user"(email);
CREATE INDEX idx_user_role ON "user"(role);
CREATE INDEX idx_user_department ON "user"(department_id);
CREATE INDEX idx_user_active ON "user"(is_active);

1.2 用户会话表 (user_session)

CREATE TABLE "user_session" (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_accessed TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    ip_address INET,
    user_agent TEXT
);

-- 索引
CREATE INDEX idx_session_token ON "user_session"(session_token);
CREATE INDEX idx_session_user ON "user_session"(user_id);
CREATE INDEX idx_session_expires ON "user_session"(expires_at);

2. 员工管理模块

2.1 员工表 (employee)

CREATE TABLE "employee" (
    id SERIAL PRIMARY KEY,
    employee_id VARCHAR(20) UNIQUE NOT NULL,
    user_id INTEGER REFERENCES "user"(id),
    
    -- 基本信息
    name VARCHAR(100) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),
    gender VARCHAR(10) CHECK (gender IN ('male', 'female', 'other')),
    birth_date DATE,
    id_number VARCHAR(20) UNIQUE,
    passport_number VARCHAR(20),
    
    -- 联系信息
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    emergency_contact VARCHAR(100),
    emergency_phone VARCHAR(20),
    
    -- 工作信息
    department_id INTEGER NOT NULL REFERENCES "department"(id),
    position_id INTEGER NOT NULL REFERENCES "position"(id),
    manager_id INTEGER REFERENCES "employee"(id),
    hire_date DATE NOT NULL,
    probation_end_date DATE,
    contract_type VARCHAR(20) NOT NULL CHECK (contract_type IN ('full_time', 'part_time', 'contract', 'intern')),
    employment_status VARCHAR(20) DEFAULT 'active' CHECK (employment_status IN ('active', 'probation', 'terminated', 'resigned')),
    level VARCHAR(20),
    
    -- 薪资信息
    base_salary DECIMAL(10,2),
    currency VARCHAR(3) DEFAULT 'CNY',
    
    -- 教育背景
    education VARCHAR(100),
    major VARCHAR(100),
    work_experience TEXT,
    resume TEXT,
    skills JSONB,
    awards JSONB,
    avatar VARCHAR(500),
    notes TEXT,
    
    -- 系统字段
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_employee_employee_id ON "employee"(employee_id);
CREATE INDEX idx_employee_user_id ON "employee"(user_id);
CREATE INDEX idx_employee_department ON "employee"(department_id);
CREATE INDEX idx_employee_position ON "employee"(position_id);
CREATE INDEX idx_employee_manager ON "employee"(manager_id);
CREATE INDEX idx_employee_status ON "employee"(employment_status);
CREATE INDEX idx_employee_active ON "employee"(is_active);
CREATE INDEX idx_employee_hire_date ON "employee"(hire_date);

2.2 员工状态历史表 (employee_status_history)

CREATE TABLE "employee_status_history" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id) ON DELETE CASCADE,
    from_status VARCHAR(20) NOT NULL,
    to_status VARCHAR(20) NOT NULL,
    reason VARCHAR(200) NOT NULL,
    change_date DATE NOT NULL,
    changed_by VARCHAR(100) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_status_history_employee ON "employee_status_history"(employee_id);
CREATE INDEX idx_status_history_date ON "employee_status_history"(change_date);

2.3 员工家庭成员表 (employee_family_member)

CREATE TABLE "employee_family_member" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id) ON DELETE CASCADE,
    relationship_type VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    age INTEGER,
    political_status VARCHAR(50),
    work_unit VARCHAR(200),
    position VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_family_employee ON "employee_family_member"(employee_id);

3. 组织架构模块

3.1 部门表 (department)

CREATE TABLE "department" (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    parent_id INTEGER REFERENCES "department"(id),
    manager_id INTEGER REFERENCES "employee"(id),
    level INTEGER NOT NULL DEFAULT 1,
    budget DECIMAL(15,2),
    headcount_limit INTEGER,
    location VARCHAR(200),
    phone VARCHAR(20),
    email VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_department_code ON "department"(code);
CREATE INDEX idx_department_parent ON "department"(parent_id);
CREATE INDEX idx_department_manager ON "department"(manager_id);
CREATE INDEX idx_department_level ON "department"(level);
CREATE INDEX idx_department_active ON "department"(is_active);

3.2 岗位表 (position)

CREATE TABLE "position" (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    department_id INTEGER NOT NULL REFERENCES "department"(id),
    level VARCHAR(20) NOT NULL,
    category VARCHAR(50) NOT NULL,
    description TEXT,
    requirements TEXT,
    responsibilities TEXT,
    min_salary DECIMAL(10,2),
    max_salary DECIMAL(10,2),
    headcount_limit INTEGER NOT NULL DEFAULT 1,
    reporting_to INTEGER REFERENCES "position"(id),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_position_code ON "position"(code);
CREATE INDEX idx_position_department ON "position"(department_id);
CREATE INDEX idx_position_level ON "position"(level);
CREATE INDEX idx_position_category ON "position"(category);
CREATE INDEX idx_position_active ON "position"(is_active);

4. 考勤管理模块

4.1 考勤记录表 (attendance)

CREATE TABLE "attendance" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id),
    date DATE NOT NULL,
    check_in_time TIME,
    check_out_time TIME,
    work_hours INTEGER, -- 以分钟为单位
    overtime_hours INTEGER, -- 以分钟为单位
    status VARCHAR(20) DEFAULT 'present' CHECK (status IN ('present', 'absent', 'late', 'early_leave')),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE,
    
    UNIQUE(employee_id, date)
);

-- 索引
CREATE INDEX idx_attendance_employee ON "attendance"(employee_id);
CREATE INDEX idx_attendance_date ON "attendance"(date);
CREATE INDEX idx_attendance_status ON "attendance"(status);
CREATE INDEX idx_attendance_employee_date ON "attendance"(employee_id, date);

4.2 请假申请表 (leave_request)

CREATE TABLE "leave_request" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id),
    leave_type VARCHAR(20) NOT NULL CHECK (leave_type IN ('annual', 'sick', 'personal', 'maternity', 'paternity', 'bereavement', 'other')),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    days_requested INTEGER NOT NULL,
    reason TEXT,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'cancelled')),
    approved_by INTEGER REFERENCES "employee"(id),
    approved_at TIMESTAMP WITH TIME ZONE,
    rejection_reason TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_leave_employee ON "leave_request"(employee_id);
CREATE INDEX idx_leave_type ON "leave_request"(leave_type);
CREATE INDEX idx_leave_status ON "leave_request"(status);
CREATE INDEX idx_leave_dates ON "leave_request"(start_date, end_date);
CREATE INDEX idx_leave_approver ON "leave_request"(approved_by);

4.3 加班申请表 (overtime)

CREATE TABLE "overtime" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id),
    date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    hours INTEGER NOT NULL, -- 以分钟为单位
    reason TEXT,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
    approved_by INTEGER REFERENCES "employee"(id),
    approved_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_overtime_employee ON "overtime"(employee_id);
CREATE INDEX idx_overtime_date ON "overtime"(date);
CREATE INDEX idx_overtime_status ON "overtime"(status);
CREATE INDEX idx_overtime_approver ON "overtime"(approved_by);

5. 招聘管理模块

5.1 招聘需求表 (recruitment_requirement)

CREATE TABLE "recruitment_requirement" (
    id SERIAL PRIMARY KEY,
    requirement_number VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(200) NOT NULL,
    department_id INTEGER NOT NULL REFERENCES "department"(id),
    position_id INTEGER NOT NULL REFERENCES "position"(id),
    requested_by INTEGER NOT NULL REFERENCES "employee"(id),
    headcount INTEGER NOT NULL DEFAULT 1,
    current_headcount INTEGER DEFAULT 0,
    max_headcount INTEGER NOT NULL,
    level VARCHAR(20),
    priority VARCHAR(20) DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
    urgency VARCHAR(20) DEFAULT 'normal' CHECK (urgency IN ('normal', 'urgent', 'critical')),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'recruiting', 'completed', 'rejected')),
    job_description TEXT,
    requirements TEXT[],
    skills TEXT[],
    experience VARCHAR(100),
    education VARCHAR(100),
    salary_range VARCHAR(100),
    location VARCHAR(200),
    target_start_date DATE,
    budget DECIMAL(12,2),
    submitted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    approved_by INTEGER REFERENCES "employee"(id),
    approved_at TIMESTAMP WITH TIME ZONE,
    rejection_reason TEXT,
    recruitment_plan_id VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_recruitment_number ON "recruitment_requirement"(requirement_number);
CREATE INDEX idx_recruitment_department ON "recruitment_requirement"(department_id);
CREATE INDEX idx_recruitment_position ON "recruitment_requirement"(position_id);
CREATE INDEX idx_recruitment_status ON "recruitment_requirement"(status);
CREATE INDEX idx_recruitment_priority ON "recruitment_requirement"(priority);
CREATE INDEX idx_recruitment_requester ON "recruitment_requirement"(requested_by);

5.2 简历表 (resume)

CREATE TABLE "resume" (
    id SERIAL PRIMARY KEY,
    recruitment_requirement_id INTEGER NOT NULL REFERENCES "recruitment_requirement"(id),
    candidate_name VARCHAR(100) NOT NULL,
    candidate_email VARCHAR(100) NOT NULL,
    candidate_phone VARCHAR(20),
    resume_file_path VARCHAR(500),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'screened', 'interviewing', 'hired', 'rejected')),
    source VARCHAR(100),
    experience_years INTEGER,
    education_level VARCHAR(50),
    current_salary DECIMAL(10,2),
    expected_salary DECIMAL(10,2),
    skills TEXT[],
    notes TEXT,
    submitted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_resume_requirement ON "resume"(recruitment_requirement_id);
CREATE INDEX idx_resume_candidate ON "resume"(candidate_name);
CREATE INDEX idx_resume_status ON "resume"(status);
CREATE INDEX idx_resume_email ON "resume"(candidate_email);

5.3 面试表 (interview)

CREATE TABLE "interview" (
    id SERIAL PRIMARY KEY,
    resume_id INTEGER NOT NULL REFERENCES "resume"(id),
    candidate_name VARCHAR(100),
    interview_type VARCHAR(20) NOT NULL CHECK (interview_type IN ('first', 'second', 'final', 'hr')),
    interviewer_id INTEGER NOT NULL REFERENCES "employee"(id),
    interview_date DATE NOT NULL,
    interview_time TIME NOT NULL,
    location VARCHAR(200),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'cancelled')),
    score INTEGER CHECK (score >= 0 AND score <= 100),
    feedback TEXT,
    recommendation VARCHAR(20) CHECK (recommendation IN ('pass', 'fail', 'pending')),
    next_interview_date DATE,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_interview_resume ON "interview"(resume_id);
CREATE INDEX idx_interview_interviewer ON "interview"(interviewer_id);
CREATE INDEX idx_interview_date ON "interview"(interview_date);
CREATE INDEX idx_interview_status ON "interview"(status);
CREATE INDEX idx_interview_type ON "interview"(interview_type);

6. 入职离职模块

6.1 入职流程表 (onboarding_process)

CREATE TABLE "onboarding_process" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id),
    start_date DATE NOT NULL,
    expected_completion_date DATE,
    actual_completion_date DATE,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'in_progress', 'completed', 'delayed')),
    progress INTEGER DEFAULT 0 CHECK (progress >= 0 AND progress <= 100),
    assigned_mentor INTEGER REFERENCES "employee"(id),
    mentor_email VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_onboarding_employee ON "onboarding_process"(employee_id);
CREATE INDEX idx_onboarding_status ON "onboarding_process"(status);
CREATE INDEX idx_onboarding_mentor ON "onboarding_process"(assigned_mentor);

6.2 入职任务表 (onboarding_task)

CREATE TABLE "onboarding_task" (
    id SERIAL PRIMARY KEY,
    process_id INTEGER NOT NULL REFERENCES "onboarding_process"(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'in_progress', 'completed', 'cancelled')),
    category VARCHAR(50) NOT NULL,
    assigned_to VARCHAR(100) NOT NULL,
    due_date DATE,
    completed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_task_process ON "onboarding_task"(process_id);
CREATE INDEX idx_task_status ON "onboarding_task"(status);
CREATE INDEX idx_task_category ON "onboarding_task"(category);
CREATE INDEX idx_task_assigned ON "onboarding_task"(assigned_to);

6.3 试用期记录表 (probation_record)

CREATE TABLE "probation_record" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    duration_months INTEGER NOT NULL,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'extended', 'passed', 'failed', 'terminated')),
    progress INTEGER DEFAULT 0 CHECK (progress >= 0 AND progress <= 100),
    performance_score INTEGER CHECK (performance_score >= 0 AND performance_score <= 100),
    manager_id INTEGER NOT NULL REFERENCES "employee"(id),
    manager_email VARCHAR(100),
    mentor_id INTEGER REFERENCES "employee"(id),
    mentor_email VARCHAR(100),
    evaluation_date DATE,
    evaluation_notes TEXT,
    decision VARCHAR(20) CHECK (decision IN ('pass', 'fail', 'extend')),
    decision_date DATE,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_probation_employee ON "probation_record"(employee_id);
CREATE INDEX idx_probation_status ON "probation_record"(status);
CREATE INDEX idx_probation_manager ON "probation_record"(manager_id);
CREATE INDEX idx_probation_mentor ON "probation_record"(mentor_id);

7. 薪酬管理模块

7.1 薪资表 (payroll)

CREATE TABLE "payroll" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id),
    pay_period_start DATE NOT NULL,
    pay_period_end DATE NOT NULL,
    base_salary DECIMAL(10,2) NOT NULL,
    overtime_pay DECIMAL(10,2) DEFAULT 0,
    bonus DECIMAL(10,2) DEFAULT 0,
    allowance DECIMAL(10,2) DEFAULT 0,
    social_insurance DECIMAL(10,2) DEFAULT 0,
    housing_fund DECIMAL(10,2) DEFAULT 0,
    income_tax DECIMAL(10,2) DEFAULT 0,
    other_deductions DECIMAL(10,2) DEFAULT 0,
    gross_salary DECIMAL(10,2) NOT NULL,
    net_salary DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'CNY',
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'paid')),
    paid_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_payroll_employee ON "payroll"(employee_id);
CREATE INDEX idx_payroll_period ON "payroll"(pay_period_start, pay_period_end);
CREATE INDEX idx_payroll_status ON "payroll"(status);

7.2 福利表 (benefit)

CREATE TABLE "benefit" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id),
    benefit_type VARCHAR(50) NOT NULL,
    benefit_name VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'CNY',
    start_date DATE NOT NULL,
    end_date DATE,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'expired')),
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_benefit_employee ON "benefit"(employee_id);
CREATE INDEX idx_benefit_type ON "benefit"(benefit_type);
CREATE INDEX idx_benefit_status ON "benefit"(status);

8. 绩效管理模块

8.1 绩效计划表 (performance_plan)

CREATE TABLE "performance_plan" (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'completed', 'cancelled')),
    created_by INTEGER NOT NULL REFERENCES "employee"(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_performance_plan_period ON "performance_plan"(period_start, period_end);
CREATE INDEX idx_performance_plan_status ON "performance_plan"(status);
CREATE INDEX idx_performance_plan_creator ON "performance_plan"(created_by);

8.2 绩效评估表 (performance_assessment)

CREATE TABLE "performance_assessment" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id),
    plan_id INTEGER NOT NULL REFERENCES "performance_plan"(id),
    assessor_id INTEGER NOT NULL REFERENCES "employee"(id),
    assessment_type VARCHAR(20) NOT NULL CHECK (assessment_type IN ('self', 'manager', 'peer', '360')),
    score INTEGER NOT NULL CHECK (score >= 0 AND score <= 100),
    feedback TEXT,
    strengths TEXT[],
    improvements TEXT[],
    recommendation VARCHAR(20) CHECK (recommendation IN ('excellent', 'good', 'satisfactory', 'needs_improvement')),
    assessed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_assessment_employee ON "performance_assessment"(employee_id);
CREATE INDEX idx_assessment_plan ON "performance_assessment"(plan_id);
CREATE INDEX idx_assessment_assessor ON "performance_assessment"(assessor_id);
CREATE INDEX idx_assessment_type ON "performance_assessment"(assessment_type);

9. 培训管理模块

9.1 培训计划表 (training_plan)

CREATE TABLE "training_plan" (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    training_type VARCHAR(50) NOT NULL,
    target_audience VARCHAR(100),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    duration_hours INTEGER,
    location VARCHAR(200),
    instructor VARCHAR(100),
    max_participants INTEGER,
    status VARCHAR(20) DEFAULT 'planned' CHECK (status IN ('planned', 'ongoing', 'completed', 'cancelled')),
    created_by INTEGER NOT NULL REFERENCES "employee"(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_training_plan_dates ON "training_plan"(start_date, end_date);
CREATE INDEX idx_training_plan_status ON "training_plan"(status);
CREATE INDEX idx_training_plan_type ON "training_plan"(training_type);

9.2 培训记录表 (training_record)

CREATE TABLE "training_record" (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES "employee"(id),
    plan_id INTEGER NOT NULL REFERENCES "training_plan"(id),
    enrollment_date DATE NOT NULL,
    completion_date DATE,
    score INTEGER CHECK (score >= 0 AND score <= 100),
    status VARCHAR(20) DEFAULT 'enrolled' CHECK (status IN ('enrolled', 'in_progress', 'completed', 'dropped')),
    feedback TEXT,
    certificate_url VARCHAR(500),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_training_record_employee ON "training_record"(employee_id);
CREATE INDEX idx_training_record_plan ON "training_record"(plan_id);
CREATE INDEX idx_training_record_status ON "training_record"(status);

10. 系统管理模块

10.1 系统配置表 (system_config)

CREATE TABLE "system_config" (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT NOT NULL,
    config_type VARCHAR(20) DEFAULT 'string' CHECK (config_type IN ('string', 'number', 'boolean', 'json')),
    description TEXT,
    is_system BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

-- 索引
CREATE INDEX idx_config_key ON "system_config"(config_key);
CREATE INDEX idx_config_system ON "system_config"(is_system);

10.2 操作日志表 (audit_log)

CREATE TABLE "audit_log" (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES "user"(id),
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50) NOT NULL,
    resource_id INTEGER,
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_audit_user ON "audit_log"(user_id);
CREATE INDEX idx_audit_action ON "audit_log"(action);
CREATE INDEX idx_audit_resource ON "audit_log"(resource_type, resource_id);
CREATE INDEX idx_audit_created ON "audit_log"(created_at);

数据库关系图

1. 核心实体关系图 (Core Entities)

erDiagram
    User {
        int id PK
        string username
        string email
        string full_name
        string role
        int department_id FK
        int position_id FK
        boolean is_active
    }
    
    Employee {
        int id PK
        string employee_id
        int user_id FK
        string name
        int department_id FK
        int position_id FK
        int manager_id FK
        date hire_date
        string employment_status
        boolean is_active
    }
    
    Department {
        int id PK
        string name
        string code
        int parent_id FK
        int manager_id FK
        int level
        boolean is_active
    }
    
    Position {
        int id PK
        string title
        string code
        int department_id FK
        string level
        string category
        boolean is_active
    }
    
    User ||--o| Employee : "has profile"
    User }o--|| Department : "belongs to"
    User }o--|| Position : "has position"
    Employee }o--|| Department : "belongs to"
    Employee }o--|| Position : "has position"
    Employee ||--o{ Employee : "manages"
    Department ||--o{ Department : "parent-child"
    Department ||--o{ Position : "contains"

2. 考勤管理关系图 (Attendance Management)

erDiagram
    Employee {
        int id PK
        string employee_id
        string name
        int department_id FK
        int position_id FK
        string employment_status
    }
    
    Attendance {
        int id PK
        int employee_id FK
        date date
        time check_in_time
        time check_out_time
        string status
    }
    
    LeaveRequest {
        int id PK
        int employee_id FK
        string leave_type
        date start_date
        date end_date
        string status
        int approved_by FK
    }
    
    OvertimeRequest {
        int id PK
        int employee_id FK
        date date
        time start_time
        time end_time
        string reason
        string status
        int approved_by FK
    }
    
    Employee ||--o{ Attendance : "has records"
    Employee ||--o{ LeaveRequest : "requests"
    Employee ||--o{ LeaveRequest : "approves"
    Employee ||--o{ OvertimeRequest : "requests"
    Employee ||--o{ OvertimeRequest : "approves"

3. 招聘管理关系图 (Recruitment Management)

erDiagram
    Department {
        int id PK
        string name
        string code
    }
    
    Position {
        int id PK
        string title
        string code
        int department_id FK
    }
    
    Employee {
        int id PK
        string name
        int department_id FK
    }
    
    RecruitmentRequirement {
        int id PK
        string requirement_number
        string title
        int department_id FK
        int position_id FK
        int requested_by FK
        string status
    }
    
    Resume {
        int id PK
        int recruitment_requirement_id FK
        string candidate_name
        string candidate_email
        string status
    }
    
    Interview {
        int id PK
        int resume_id FK
        string interview_type
        int interviewer_id FK
        date interview_date
        string status
    }
    
    Department ||--o{ RecruitmentRequirement : "for"
    Position ||--o{ RecruitmentRequirement : "for"
    Employee ||--o{ RecruitmentRequirement : "requests"
    RecruitmentRequirement ||--o{ Resume : "receives"
    Resume ||--o{ Interview : "scheduled"
    Employee ||--o{ Interview : "conducts"

4. 入职离职关系图 (Onboarding & Exit Management)

erDiagram
    Employee {
        int id PK
        string employee_id
        string name
        int department_id FK
        int position_id FK
    }
    
    OnboardingProcess {
        int id PK
        int employee_id FK
        date start_date
        string status
        int assigned_mentor FK
    }
    
    ProbationPeriod {
        int id PK
        int employee_id FK
        date start_date
        date end_date
        string status
        int evaluator_id FK
    }
    
    ExitProcess {
        int id PK
        int employee_id FK
        date exit_date
        string reason
        string status
        int approved_by FK
    }
    
    Employee ||--o| OnboardingProcess : "has"
    Employee ||--o| ProbationPeriod : "has"
    Employee ||--o| ExitProcess : "has"
    Employee ||--o{ OnboardingProcess : "mentors"
    Employee ||--o{ ProbationPeriod : "evaluates"
    Employee ||--o{ ExitProcess : "approves"

5. 薪酬绩效关系图 (Payroll & Performance Management)

erDiagram
    Employee {
        int id PK
        string employee_id
        string name
        int department_id FK
        int position_id FK
    }
    
    Payroll {
        int id PK
        int employee_id FK
        date pay_period_start
        date pay_period_end
        decimal base_salary
        decimal net_salary
        string status
    }
    
    PerformancePlan {
        int id PK
        string title
        date period_start
        date period_end
        string status
        int created_by FK
    }
    
    PerformanceAssessment {
        int id PK
        int employee_id FK
        int plan_id FK
        string assessment_type
        decimal score
        string status
        int assessor_id FK
    }
    
    TrainingPlan {
        int id PK
        string title
        string training_type
        date start_date
        date end_date
        string status
        int created_by FK
    }
    
    Employee ||--o{ Payroll : "receives"
    Employee ||--o{ PerformancePlan : "created by"
    Employee ||--o{ PerformanceAssessment : "assessed"
    Employee ||--o{ TrainingPlan : "created by"
    PerformancePlan ||--o{ PerformanceAssessment : "includes"

6. 完整系统关系图

erDiagram
    User {
        int id PK
        string username
        string email
        string full_name
        string role
        int department_id FK
        int position_id FK
        boolean is_active
    }
    
    Employee {
        int id PK
        string employee_id
        int user_id FK
        string name
        int department_id FK
        int position_id FK
        int manager_id FK
        date hire_date
        string employment_status
        boolean is_active
    }
    
    Department {
        int id PK
        string name
        string code
        int parent_id FK
        int manager_id FK
        int level
        boolean is_active
    }
    
    Position {
        int id PK
        string title
        string code
        int department_id FK
        string level
        string category
        boolean is_active
    }
    
    Attendance {
        int id PK
        int employee_id FK
        date date
        time check_in_time
        time check_out_time
        string status
    }
    
    LeaveRequest {
        int id PK
        int employee_id FK
        string leave_type
        date start_date
        date end_date
        string status
        int approved_by FK
    }
    
    OvertimeRequest {
        int id PK
        int employee_id FK
        date date
        time start_time
        time end_time
        string reason
        string status
        int approved_by FK
    }
    
    RecruitmentRequirement {
        int id PK
        string requirement_number
        string title
        int department_id FK
        int position_id FK
        int requested_by FK
        string status
    }
    
    Resume {
        int id PK
        int recruitment_requirement_id FK
        string candidate_name
        string candidate_email
        string status
    }
    
    Interview {
        int id PK
        int resume_id FK
        string interview_type
        int interviewer_id FK
        date interview_date
        string status
    }
    
    OnboardingProcess {
        int id PK
        int employee_id FK
        date start_date
        string status
        int assigned_mentor FK
    }
    
    ProbationPeriod {
        int id PK
        int employee_id FK
        date start_date
        date end_date
        string status
        int evaluator_id FK
    }
    
    ExitProcess {
        int id PK
        int employee_id FK
        date exit_date
        string reason
        string status
        int approved_by FK
    }
    
    Payroll {
        int id PK
        int employee_id FK
        date pay_period_start
        date pay_period_end
        decimal base_salary
        decimal net_salary
        string status
    }
    
    PerformancePlan {
        int id PK
        string title
        date period_start
        date period_end
        string status
        int created_by FK
    }
    
    PerformanceAssessment {
        int id PK
        int employee_id FK
        int plan_id FK
        string assessment_type
        decimal score
        string status
        int assessor_id FK
    }
    
    TrainingPlan {
        int id PK
        string title
        string training_type
        date start_date
        date end_date
        string status
        int created_by FK
    }
    
    User ||--o| Employee : "has profile"
    User }o--|| Department : "belongs to"
    User }o--|| Position : "has position"
    Employee }o--|| Department : "belongs to"
    Employee }o--|| Position : "has position"
    Employee ||--o{ Employee : "manages"
    Department ||--o{ Department : "parent-child"
    Department ||--o{ Position : "contains"
    Employee ||--o{ Attendance : "has records"
    Employee ||--o{ LeaveRequest : "requests"
    Employee ||--o{ LeaveRequest : "approves"
    Employee ||--o{ OvertimeRequest : "requests"
    Employee ||--o{ OvertimeRequest : "approves"
    Employee ||--o{ RecruitmentRequirement : "requests"
    Department ||--o{ RecruitmentRequirement : "for"
    Position ||--o{ RecruitmentRequirement : "for"
    RecruitmentRequirement ||--o{ Resume : "receives"
    Resume ||--o{ Interview : "scheduled"
    Employee ||--o{ Interview : "conducts"
    Employee ||--o| OnboardingProcess : "has"
    Employee ||--o| ProbationPeriod : "has"
    Employee ||--o| ExitProcess : "has"
    Employee ||--o{ OnboardingProcess : "mentors"
    Employee ||--o{ ProbationPeriod : "evaluates"
    Employee ||--o{ ExitProcess : "approves"
    Employee ||--o{ Payroll : "receives"
    Employee ||--o{ PerformancePlan : "created by"
    Employee ||--o{ PerformanceAssessment : "assessed"
    Employee ||--o{ TrainingPlan : "created by"
    PerformancePlan ||--o{ PerformanceAssessment : "includes"

索引策略

1. 主键索引

  • 所有表的主键自动创建唯一索引
  • 支持快速主键查询和关联查询

2. 外键索引

  • 所有外键字段创建索引
  • 支持快速关联查询和级联操作

3. 业务索引

  • 用户表: username, email, role, department_id, is_active
  • 员工表: employee_id, user_id, department_id, position_id, manager_id, employment_status, hire_date
  • 部门表: code, parent_id, manager_id, level, is_active
  • 考勤表: employee_id, date, status
  • 请假表: employee_id, leave_type, status, start_date, end_date
  • 招聘表: requirement_number, department_id, position_id, status, priority

4. 复合索引

  • 考勤表: (employee_id, date) - 唯一约束
  • 薪资表: (employee_id, pay_period_start, pay_period_end)
  • 绩效表: (employee_id, plan_id, assessment_type)

数据完整性约束

1. 实体完整性

  • 所有表都有主键约束
  • 主键值唯一且非空

2. 参照完整性

  • 外键约束确保数据关联正确性
  • 级联删除和更新策略
  • 防止孤立记录

3. 域完整性

  • 数据类型约束
  • 长度限制约束
  • 取值范围约束
  • 非空约束

4. 业务完整性

  • 检查约束确保业务规则
  • 唯一约束防止重复数据
  • 默认值设置

性能优化策略

1. 查询优化

  • 合理使用索引
  • 避免全表扫描
  • 优化复杂查询
  • 使用查询计划分析

2. 分区策略

  • 大表按时间分区
  • 考勤表按月分区
  • 日志表按年分区

3. 缓存策略

  • 热点数据缓存
  • 查询结果缓存
  • 统计数据缓存

4. 连接池

  • 数据库连接池
  • 连接复用
  • 连接超时设置

安全设计

1. 数据加密

  • 敏感字段加密存储
  • 密码哈希存储
  • 传输加密

2. 访问控制

  • 基于角色的数据访问
  • 行级安全策略
  • 列级权限控制

3. 审计日志

  • 完整的数据操作日志
  • 用户行为跟踪
  • 安全事件记录

4. 备份恢复

  • 定期数据备份
  • 增量备份策略
  • 快速恢复机制

数据迁移策略

1. 版本控制

  • 使用Alembic进行版本控制
  • 增量迁移脚本
  • 回滚机制

2. 数据迁移

  • 数据格式转换
  • 数据清洗和验证
  • 数据完整性检查

3. 测试验证

  • 迁移前数据备份
  • 迁移后数据验证
  • 性能测试

监控和维护

1. 性能监控

  • 查询性能监控
  • 索引使用情况
  • 连接数监控

2. 空间监控

  • 数据库大小监控
  • 表空间使用情况
  • 日志文件大小

3. 维护任务

  • 定期统计信息更新
  • 索引重建
  • 数据清理

总结

本数据库设计遵循第三范式，确保数据完整性、一致性和可扩展性。设计支持完整的HR业务流程，包括用户管理、员工管理、组织架构、考勤管理、招聘管理、薪酬管理等核心功能模块。

数据库设计具有以下特点：

  1. 完整性: 完整的数据模型设计，支持所有业务功能
  2. 性能: 合理的索引策略和查询优化
  3. 安全: 多层次的安全保障机制
  4. 扩展性: 支持功能扩展和性能扩展
  5. 可维护性: 清晰的表结构和关系设计
  6. 标准化: 遵循数据库设计标准和最佳实践

该数据库设计为HR管理系统提供了坚实的数据基础，能够满足企业级应用的各种需求。