12.26

CREATE TABLE attendance_record (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '序号',
attendance_time DATETIME NOT NULL COMMENT '考勤时间',
job_id VARCHAR(10) NOT NULL COMMENT '工号',
name VARCHAR(50) NOT NULL COMMENT '姓名',
sex ENUM('男', '女') NOT NULL COMMENT '性别',
birthday VARCHAR(10) NOT NULL COMMENT '出生日期',
department_id VARCHAR(2) NOT NULL COMMENT '部门编码',
attendance_type TINYINT NOT NULL COMMENT '考勤类型：0上班，1下班，2事假，3病假',
notes TEXT COMMENT '请假事由',
approved_type TINYINT DEFAULT 0 COMMENT '是否审批：0未审批，1已通过',
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (job_id) REFERENCES employee(job_id),
FOREIGN KEY (department_id) REFERENCES department(department_id)
) COMMENT '考勤记录表';

-- 4. 初始化部门数据
INSERT INTO department (department_id, department_name) VALUES
('01', '办公室'),
('02', '财务部门'),
('03', '销售部门'),
('04', '生产一部'),
('05', '生产二部'),
('06', '生产三部');

-- 5. 创建索引
CREATE INDEX idx_attendance_jobid ON attendance_record(job_id);
CREATE INDEX idx_attendance_time ON attendance_record(attendance_time);