支持全年每日自定义的非固定工时休假管理系统数据库结构咨询

Hey there! Let's tackle this problem step by step—your need for a leave management system that handles both base scheduling templates and custom daily rules is super common, and the key is balancing flexibility with database efficiency.

Core Approach: Template + Exception Rules #

Instead of storing every single date of the year (which is redundant and hard to maintain), we'll use a base scheduling template for recurring weekly patterns, plus an exception table for one-off or custom date overrides. This way, you only store what's different from the default template, keeping your database lean and easy to manage.

Recommended Database Tables #

1. companies (Enterprise Basic Info) #

Stores core details about each company using the system:

CREATE TABLE companies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. employees (Employee Details) #

Links employees to their company and default weekly schedule template:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL,
    base_schedule_template_id INT NOT NULL, -- Maps to the employee's default weekly schedule
    FOREIGN KEY (company_id) REFERENCES companies(id),
    FOREIGN KEY (base_schedule_template_id) REFERENCES schedule_templates(id)
);

3. schedule_templates (Base Weekly Schedules) #

Defines reusable weekly work patterns (e.g., "Mon-Fri + Sat work, Sun off" for a retail team):

CREATE TABLE schedule_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    template_name VARCHAR(50) NOT NULL, -- e.g., "Weekend Shift Team"
    FOREIGN KEY (company_id) REFERENCES companies(id)
);

3.1 schedule_template_details (Granular Daily Rules for Templates)

Breaks down each weekly template into individual days for precise control:

CREATE TABLE schedule_template_details (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_id INT NOT NULL,
    week_day INT NOT NULL, -- 1=Monday, 2=Tuesday...7=Sunday
    is_workday BOOLEAN NOT NULL, -- True = work day, False = rest day
    work_start_time TIME, -- Optional: only needed if is_workday is True
    work_end_time TIME, -- Optional: only needed if is_workday is True
    FOREIGN KEY (template_id) REFERENCES schedule_templates(id)
);

This lets you easily mark Saturday (week_day=6) as a workday for employees on non-standard shifts.

4. schedule_exceptions (Custom Date Overrides) #

Stores any date that deviates from the base template (e.g., company holidays, individual employee temporary schedule changes, national holidays):

CREATE TABLE schedule_exceptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    employee_id INT NULL, -- Null = applies to all company employees; non-null = only this employee
    exception_date DATE NOT NULL,
    is_workday BOOLEAN NOT NULL,
    work_start_time TIME,
    work_end_time TIME,
    reason VARCHAR(200), -- e.g., "National Holiday", "Temporary Remote Work"
    FOREIGN KEY (company_id) REFERENCES companies(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

Why This Beats Storing Every Date #

Let's weigh the two options clearly:

• Storing all dates: Pros are straightforward queries (just look up the date directly). But cons are massive redundancy (365 rows per company per year), tedious annual setup, and wasted storage space.
• Template + Exceptions: Pros are minimal data storage (only templates and exceptions), easy updates (change a template once instead of hundreds of dates), and full flexibility for custom rules. The only minor con is that you'll need a bit of logic to calculate a day's status (check exceptions first, fall back to the template if no exception exists).

Example Query Logic for a Day's Work Status #

To check if an employee works on 2024-05-01:

1. First, query schedule_exceptions for any entry where exception_date = '2024-05-01' and (employee_id = [target_employee_id] or employee_id IS NULL). If found, use that entry's is_workday value.
2. If no exception exists, get the employee's base_schedule_template_id, calculate the week_day of 2024-05-01 (e.g., Wednesday = 3), then look up schedule_template_details for that template and week_day to get the default is_workday status.

Bonus: Supporting Leave Balance Calculations #

To track remaining leave days, add a leave_applications table to log each employee's requests and approvals:

CREATE TABLE leave_applications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    leave_type VARCHAR(50) NOT NULL, -- e.g., "Annual Leave", "Sick Leave"
    status VARCHAR(20) NOT NULL, -- e.g., "Pending", "Approved", "Rejected"
    approved_days DECIMAL(4,1), -- Calculated based on workdays in the leave period
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

When calculating approved_days, use the same workday logic above to count only days that would normally be workdays (since rest days might not count against leave balances, depending on company policy).

内容的提问来源于stack exchange，提问作者Maradilly