请假系统数据库设计

（NiZi）

于 2024-06-14 11:45:45 发布

阅读量912

点赞数 26

文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/m0_51414431/article/details/139677832

版权

创建请销假系统

1.1 数据库表设计

1.1.1 设计学生表

主要字段有id,student_id，admission_year，college_code，gender，student_name ，phone， class， password

校验输入phone是否合法

alter table students
    add phone CHAR(11) NOT NULL CHECK (phone REGEXP '^[0-9]{11}$');

创建一个触发器，在插入新记录后自动填充student_id字段。这个触发器会根据新插入的记录中的入学年份、学院编号、性别以及自增的id来生成student_id。

DELIMITER //
/*
创建一个触发器，用于在学生表中插入新记录后自动设置学生的唯一标识符student_id。
该触发器在插入操作完成后触发，对每个新插入的行执行。
触发器通过组合入学年份、学院代码、性别和学生编号生成一个新的student_id。
*/

CREATE TRIGGER set_student_id
    AFTER INSERT
    ON students
    FOR EACH ROW
BEGIN
    /*
    组合学生的唯一标识符。
    使用CONCAT将入学年份、学院代码（补齐至两位）、性别和学生编号（补齐至四位）连接起来。
    确保student_id的唯一性和一定的结构化，方便后续查询和管理。
    */
    SET @new_student_id = CONCAT(
            NEW.admission_year,
            LPAD(NEW.college_code, 2, '0'),
            NEW.gender,
            LPAD(NEW.id, 4, '0'));

    /*
    更新学生表，将新生成的student_id赋值给对应的新插入行。
    这里使用WHERE id = NEW.id来确保只更新刚刚插入的那行数据。
    */
    UPDATE students
    Set student_id = @new_student_id
    WHERE id = NEW.id;
end;
DELIMITER //

ALTER TABLE students
ADD COLUMN password_hash CHAR(64) NOT NULL DEFAULT SHA2('111111', 256);

Error1：学号自动生成出错

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

zn_holiday> INSERT INTO zn_holiday.students (student_id, admission_year, college_code, gender, student_name) VALUES (null, 2024, '10', '01', '张三')
[2024-06-07 11:27:22] [23000][1048] Column 'student_id' cannot be null

Step1:

更改student_id字段允许默认为null值

zn_holiday> INSERT INTO zn_holiday.students (student_id, admission_year, college_code, gender, student_name) VALUES (null, 2024, '12', '01', '张三')
[2024-06-07 13:29:48] [HY000][1442] Can't update table 'students' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Step2:

触发器试图更新正在被当前操作影响的表students，而这种操作在Mysql中是不允许的，以避免循环或意外的副作用

触发器set_student_id在检测到新纪录插入后尝试更新students表中的student_id字段。由于触发器是由对students表的INSERT操作触发的，所以它不能反过来再更新同一张表，这会导致一种“直接或间接地引发自身的循环"。

为了使在插入时自动生成student_id，可能的解决方案：

使用BEFORE INSERT触发器代替: 改变触发器为BEFORE INSERT类型，并直接在触发器中设置NEW.student_id的值，而不是执行UPDATE。这样就避免了在触发器内部对正在插入数据的表进行更新操作的问题

CREATE TRIGGER set_student_id
BEFORE INSERT
ON students
FOR EACH ROW
BEGIN
    SET NEW.student_id = CONCAT(
        NEW.admission_year,
        LPAD(NEW.college_code, 2, '0'),
        NEW.gender,
        LPAD(NEW.id, 4, '0'));
END

Step3:

INSERT INTO zn_holiday.students (student_id, admission_year, college_code, gender, student_name) VALUES (null, 2024, '12', '01', '张三')
[2024-06-07 13:47:48] [22001][1406] Data truncation: Data too long for column 'student_id' at row 1

更改student_id varchar(10)为varchar(15);

Error2:向students表中插入哈希密码出错

向students表中插入哈希密码字段

zn_holiday> ALTER TABLE students
            ADD COLUMN password_hash CHAR(64) NOT NULL DEFAULT SHA2('111111',256) comment '密码哈希值'
[2024-06-11 09:32:56] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHA2('111111',256) comment '密码哈希值'' at line 2

step1

MySQL并不直接支持在 DEFAULT 子句中使用函数来计算非静态默认值。不能直接在 ALTER TABLE 语句中使用 SHA2() 或其他函数来为每个新记录动态生成默认的哈希密码。

先执行 ALTER TABLE 添加字段，然后通过更新语句设置默认的哈希密码

step2

后续在后端来设置哈希密码

1.1.2 设计请假类型表

CREATE  table laave_types(
    leave_type_id INT AUTO_INCREMENT PRIMARY KEY ,
    leave_name VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

在这个SQL语句中：

leave_type_id 是主键字段，使用INT类型并设置为自动递增（AUTO_INCREMENT），确保每条记录都有唯一的ID。
leave_name 字段用于存储请假类型的名称，如“病假”、“事假”等，类型为VARCHAR(50)，并标记为NOT NULL
description 字段可选，用于存储请假类型的详细描述，类型为TEXT，可以存储大量文本。
created_at 和 updated_at 字段分别记录请假类型创建和最后更新的时间，均使用TIMESTAMP类型，并设置了默认值：CURRENT_TIMESTAMP表示插入时的时间，以及ON UPDATE CURRENT_TIMESTAMP表示每次更新记录时自动更新为当前

1.1.3设计请假申请表

CREATE TABLE leave_applications (
    application_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    leave_type_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    reason TEXT NOT NULL,
    status ENUM('申请中', '批准', '拒绝', '已销假') DEFAULT '申请中',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (leave_type_id) REFERENCES leave_types(leave_type_id)
);

application_id 是主键字段，自动递增。
student_id 和 leave_type_id 分别为外键，引用students表的student_id和leave_types表的leave_type_id，用于关联学生和请假类型，且这两个字段都被标记为NOT NULL。
start_date 和 end_date 定义请假的起始和结束日期，类型为DATE，并要求非空。
reason 字段用于记录请假理由，类型为TEXT，也是必填项。
status 字段使用ENUM类型来限定请假申请的状态，包括“申请中”、“批准”、“拒绝”和“已销假”，并设置了默认值为“申请中”。
created_at 和 updated_at 字段记录记录创建和最后更新的时间

Error1：status在遍历状态，无法创建该表

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

Step1:

查询mysql版本，是否为5.7及以上

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

不是mysql版本低的原因

Step2:

尝试移除DEFAULT ’申请中‘

[2024-06-07 15:01:04] [42000][1067] Invalid default value for 'status'
zn_holiday> CREATE TABLE leave_applications
            (
                application_id INT AUTO_INCREMENT PRIMARY KEY,
                student_id     INT  NOT NULL,
                leave_type_id  INT  NOT NULL,
                start_date     DATE NOT NULL,
                end_date       DATE NOT NULL,
                reason         TEXT NOT NULL,
                status         ENUM ('申请中','已批准','已拒绝','已销假'),
                created_at     TIMESTAMP                                  DEFAULT CURRENT_TIMESTAMP,
                updated_at     TIMESTAMP                                  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (student_id) references students (student_id),
                FOREIGN KEY (leave_type_id) references laave_types (leave_type_id)
            )
[2024-06-07 15:02:27] [HY000][1291] Column 'status' has duplicated value '???' in ENUM

Step3：

问题所在：未设置数据库引擎和字符编码格式

CREATE TABLE leave_applications
(
    application_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id     INT  NOT NULL,
    leave_type_id  INT  NOT NULL,
    start_date     DATE NOT NULL,
    end_date       DATE NOT NULL,
    reason         TEXT NOT NULL,
    status ENUM('申请中', '已批准', '已拒绝', '已销假')default '申请中',
    created_at     TIMESTAMP                                  DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP                                  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) references students (student_id),
    FOREIGN KEY (leave_type_id) references leave_types (leave_type_id)
)engine = innodb default charset = utf8mb4;

Error2：请假类型表无法与students表建立外键

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

Step1:

外键类型不匹配：检查leave_applications表中外键列的数据类型是否与它们所引用的表中相应列的数据类型完全一致。例如，student_id和leave_type_id的类型都应该是INT，并且长度、无符号属性等也要相同。

student_id     VARCHAR(15)  NOT NULL,

依然无法建立外键

Step2:

如果students表中的student_id没有建立索引，仍然无法建立外键

查看索引状态

SHOW INDEXES FROM students;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

student_id存在索引。

step3：

将两个表中的student_id 都建立唯一索引，字符集编码都为utf8mb4_bin,成功

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

1.1.4 设计职员表

CREATE TABLE staffs
(
    id                INT                           NOT NULL AUTO_INCREMENT PRIMARY KEY comment '员工id',
    staff_id          VARCHAR(10)                   NOT NULL UNIQUE comment '员工工号',
    staff_name        VARCHAR(50)                   NOT NULL comment '员工姓名',
    staff_level       ENUM ('辅导员','主任','院长') NOT NULL comment '员工级别',
    superior_staff_id VARCHAR(10)                    comment '上级员工工号', -- 上级员工工号,院长级别最高
    password          VARCHAR(50)      DEFAULT '111111'         NOT NULL comment '密码',
    college_code      CHAR(2)                   NOT NULL comment '学院编号',
    FOREIGN KEY (superior_staff_id) REFERENCES staffs(staff_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4;

staff_id是主键，自动递增，确保每位职员有唯一的ID。
staff_level使用ENUM类型定义了职员的三个等级：辅导员、主任、院长。
staff_name存储职员的姓名，并通过UNIQUE约束保证姓名的唯一性，避免重名。
superior_staff_id是一个自引用的外键，它可以指向staffs表中的另一个职员，表示该职员的直接上级。对于最高级别的职员（如院长），这个字段可以设置为其自己的staff_id，或者对于没有直接上级的情况设置为NULL。

将职员id按照规则自动生成

CREATE TRIGGER set_staff_id
    BEFORE INSERT
    ON staffs
    FOR EACH ROW
    BEGIN
        SET NEW.staff_id = CONCAT(
                           LPAD(NEW.id, 4, '0'),
                           LPAD(collegge_code, 2, '0')
                           );
    end;

Error：插入职员数据时出错

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

step1:

职员id自动生成错误更改为如下成功

CREATE TRIGGER set_staff_id
    BEFORE INSERT
    ON staffs
    FOR EACH ROW
    BEGIN
        SET NEW.staff_id = CONCAT(
                           LPAD(NEW.id, 4, '0'),
                           LPAD(NEW.college_code, 2, '0')  -- 此处为NEW.college_code
                           );
    end;

Error2: 插入多条数据出错

如果id字段是自增的，那么在BEFORE INSERT触发器中，NEW.id的值可能还没有被数据库自动赋值。在这种情况下，需要使用LAST_INSERT_ID()函数来获取最近一次插入操作的自增ID值。更改触发器如下

create definer = root@localhost trigger set_staff_id
    before insert
    on staffs
    for each row
BEGIN
  -- 假设id是自增主键，这里使用LAST_INSERT_ID()获取上一条记录的ID
  -- 注意：这里可能需要根据实际情况调整逻辑
  SET NEW.staff_id = CONCAT(
    LPAD(LAST_INSERT_ID(), 4, '0'),
    LPAD(NEW.college_code, 4, '0')
  );
END;

1.1.5设计审批表单

CREATE TABLE approvals  (
    approval_id INT AUTO_INCREMENT PRIMARY KEY comment '审批id',
    application_id INT NOT NULL comment '请假申请id',
    approver_id VARCHAR(10) NOT NULL comment '审批人id',
    approval_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '审批时间',
    approval_status ENUM ('待审批', '已通过', '已拒绝') DEFAULT '待审批'comment '审批状态',
    comment TEXT comment '审批意见',
    FOREIGN KEY (application_id) REFERENCES leave_applications(application_id),
    FOREIGN KEY (approver_id) REFERENCES staffs(staff_id),
    INDEX idx_approver_status (approver_id, approval_status)

)ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

定义了表名为approvals，并设置了各个字段及数据类型。
使用AUTO_INCREMENT为approval_id字段设置自动增长的主键。
对application_id和approver_id添加了外键约束，分别引用leave_applications表的application_id和假设的staffs表的staff_id。
approval_time默认值设为CURRENT_TIMESTAMP，记录审批的时间。
approval_status使用ENUM类型定义了审批的几种可能状态，并设置了默认值为待审批。
添加了comments字段来存储审批意见。
NE = INNODB DEFAULT CHARSET = utf8mb4;


- 定义了表名为`approvals`，并设置了各个字段及数据类型。
- 使用`AUTO_INCREMENT`为`approval_id`字段设置自动增长的主键。
- 对`application_id`和`approver_id`添加了外键约束，分别引用`leave_applications`表的`application_id`和假设的`staffs`表的`staff_id`。
- `approval_time`默认值设为`CURRENT_TIMESTAMP`，记录审批的时间。
- `approval_status`使用`ENUM`类型定义了审批的几种可能状态，并设置了默认值为`待审批`。
- 添加了`comments`字段来存储审批意见。
- 创建了一个索引`idx_approver_status`，如果在查询时经常需要根据审批人和审批状态过滤记录，这个索引可能会提升查询性能。

（NiZi）

关注

26
点赞
踩
17

收藏

觉得还不错? 一键收藏
1
评论
请假系统数据库设计

主要字段有idstudent_idgenderphoneclasspassword 校验输入phone是否合法创建一个触发器，在插入新记录后自动填充student_id字段。这个触发器会根据新插入的记录中的入学年份、学院编号、性别以及自增的id来生成student_id。
复制链接

扫一扫