school-in-out/sql/school_in_out_db.sql

CREATE TABLE tb_school
(
    id             BIGINT UNSIGNED NOT NULL auto_increment COMMENT 'id',
    school_name    VARCHAR(255)    NOT NULL COMMENT '学校名称',
    school_address VARCHAR(255)    NOT NULL COMMENT '学校地址',
    link_name      VARCHAR(60)     NOT NULL COMMENT '联系人',
    link_phone     VARCHAR(60)     NOT NULL COMMENT '联系人电话',
    del_flag       CHAR(1)     DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
    create_by      VARCHAR(64) DEFAULT '' COMMENT '创建者',
    create_time    datetime COMMENT '创建时间',
    update_by      VARCHAR(64) DEFAULT '' COMMENT '更新者',
    update_time    datetime COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE = INNODB
  auto_increment = 1 COMMENT = '学校';

CREATE TABLE tb_school_teacher
(
    id           BIGINT UNSIGNED NOT NULL auto_increment COMMENT 'id',
    school_id    BIGINT UNSIGNED NOT NULL COMMENT '学校id',
    teacher_name VARCHAR(255)    NOT NULL COMMENT '老师姓名',
    gender       CHAR(1)         NOT NULL DEFAULT '0' COMMENT '性别；0男   1女',
    phone        VARCHAR(60)     NOT NULL COMMENT '联系电话',
    del_flag     CHAR(1)                  DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
    create_by    VARCHAR(64)              DEFAULT '' COMMENT '创建者',
    create_time  datetime COMMENT '创建时间',
    update_by    VARCHAR(64)              DEFAULT '' COMMENT '更新者',
    update_time  datetime COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE = INNODB
  auto_increment = 1 COMMENT = '教师';

CREATE TABLE tb_school_class
(
    id          BIGINT UNSIGNED NOT NULL auto_increment COMMENT 'id',
    school_id   BIGINT UNSIGNED NOT NULL COMMENT '学校id',
    class_name  VARCHAR(255)    NOT NULL COMMENT '班级名称',
    class_count INT             NOT NULL DEFAULT 0 COMMENT '班级人数',
    phone       VARCHAR(60) COMMENT '班级电话',
    del_flag    CHAR(1)                  DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
    create_by   VARCHAR(64)              DEFAULT '' COMMENT '创建者',
    create_time datetime COMMENT '创建时间',
    update_by   VARCHAR(64)              DEFAULT '' COMMENT '更新者',
    update_time datetime COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE = INNODB
  auto_increment = 1 COMMENT = '班级';

CREATE TABLE tb_school_teacher_class_relation
(
    id           BIGINT UNSIGNED NOT NULL auto_increment COMMENT 'id',
    school_id    BIGINT UNSIGNED NOT NULL COMMENT '学校id',
    teacher_id   BIGINT UNSIGNED NOT NULL COMMENT '教师id',
    class_id     BIGINT UNSIGNED NOT NULL COMMENT '班级id',
    teacher_main CHAR(1)     DEFAULT 'N' COMMENT '是否为班主任',
    create_by    VARCHAR(64) DEFAULT '' COMMENT '创建者',
    create_time  datetime COMMENT '创建时间',
    update_by    VARCHAR(64) DEFAULT '' COMMENT '更新者',
    update_time  datetime COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE = INNODB
  auto_increment = 1 COMMENT = '老师-班级关系表';

CREATE TABLE tb_school_student
(
    id             BIGINT UNSIGNED NOT NULL auto_increment COMMENT 'id',
    school_id      BIGINT UNSIGNED NOT NULL COMMENT '学校id',
    class_id       BIGINT UNSIGNED NOT NULL COMMENT '班级id',
    student_name   VARCHAR(60)     NOT NULL COMMENT '学生名称',
    student_pic    VARCHAR(60)     NOT NULL COMMENT '学生照片',
    student_gender CHAR(1)     DEFAULT '0' COMMENT '学生性别',
    student_num    VARCHAR(255) COMMENT '学号',
    del_flag       CHAR(1)     DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
    create_by      VARCHAR(64) DEFAULT '' COMMENT '创建者',
    create_time    datetime COMMENT '创建时间',
    update_by      VARCHAR(64) DEFAULT '' COMMENT '更新者',
    update_time    datetime COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE = INNODB
  auto_increment = 1 COMMENT = '学生表';

CREATE TABLE tb_school_parent
(
    id           BIGINT UNSIGNED NOT NULL auto_increment COMMENT 'id',
    student_id   BIGINT UNSIGNED NOT NULL COMMENT '学生id',
    parent_name  VARCHAR(60)     NOT NULL COMMENT '家长姓名',
    parent_phone VARCHAR(60)     NOT NULL COMMENT '家长电话',
    relation     CHAR(3)         NOT NULL DEFAULT '99' COMMENT '与学生的关系',
    del_flag     CHAR(1)                  DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
    create_by    VARCHAR(64)              DEFAULT '' COMMENT '创建者',
    create_time  datetime COMMENT '创建时间',
    update_by    VARCHAR(64)              DEFAULT '' COMMENT '更新者',
    update_time  datetime COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE = INNODB
  auto_increment = 1 COMMENT = '家长表';

CREATE TABLE tb_school_access_log
(
    id          BIGINT UNSIGNED NOT NULL auto_increment COMMENT 'id',
    student_id  BIGINT UNSIGNED NOT NULL COMMENT '学生id',
    in_time     datetime COMMENT '到校时间',
    out_time    datetime COMMENT '离校时间',
    del_flag    CHAR(1)     DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
    create_by   VARCHAR(64) DEFAULT '' COMMENT '创建者',
    create_time datetime COMMENT '创建时间',
    update_by   VARCHAR(64) DEFAULT '' COMMENT '更新者',
    update_time datetime COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE = INNODB
  auto_increment = 1 COMMENT = '进出学校记录表';