mysql考勤系统表

-- 创建员工信息表
CREATE TABLE employee (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL
);

-- 创建考勤记录表
CREATE TABLE attendance (
    attendance_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    date DATE NOT NULL,
    type ENUM('normal', 'late', 'early_leave', 'absent') NOT NULL,
    check_in_time TIME,
    check_out_time TIME,
    FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
);

-- 创建请假记录表
CREATE TABLE leave (
    leave_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    date DATE NOT NULL,
    type ENUM('personal_leave', 'sick_leave') NOT NULL,
    duration INT NOT NULL, -- 请假时长（单位：小时）
    FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
);

-- 创建加班记录表
CREATE TABLE overtime (
    overtime_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    date DATE NOT NULL,
    duration INT NOT NULL, -- 加班时长（单位：小时）
    FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
);