(1) Demand

There are three roles: student and teacher, super administrator. Teachers can edit student information, competition results and so on. Ordinary students can only query scores and modify their own information when they log in. Super administrators can operate everything when they log in, and there is only one, which can not be deleted.
Note that the database design of this system does not consider the index, strictly speaking, the query field needs to be indexed.
Five tables need to be created first.

• The student list contains some basic information about the students.
• The score sheet contains the results of every student's every sport.
• The teacher's table contains some information about the teacher.
• The basic data table contains some data types, such as what sports events, which user types, which states, and so on.
• Log table, record system operation log, log-in and log-out, registration audit, information editing, score editing and so on.

The registration of students requires the approval of teachers before they can log in.
Teachers can manage student information, edit student scores, basic data sheets, while students can only view results and information.

(2) Database Modeling

Student list

drop table if exists student;

/*==============================================================*/
/* Table: student                                               */
/*==============================================================*/
create table student
(
   id                   bigint(40) not null auto_increment comment 'id',
   name                 varchar(40) default NULL comment 'Full name',
   password             varchar(40) default NULL comment 'Password md5 encryption',
   code                 varchar(40) default NULL comment 'Student ID',
   birthday             date default NULL comment 'Date of birth',
   sex                  tinyint(2) default 1 comment 'Gender (1: male; 2: female)',
   nation               varchar(40) default NULL comment 'Nation',
   college              varchar(40) default NULL comment 'college',
   major                varchar(40) default NULL comment 'major',
   nationality          varchar(40) default NULL comment 'nationality',
   entrance_date        date default NULL comment 'Date of enrollment',
   student_type         tinyint(2) default 2 comment 'Student type (corresponding to basic data table) type=2)',
   identity             varchar(18) default NULL comment 'ID',
   phone                varchar(20) default NULL comment 'Telephone',
   native_place         varchar(40) default NULL comment 'Native place',
   status               tinyint(4) default 1 comment 'Status (corresponding to the underlying data table) type=3)',
   primary key (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Score sheet

drop table if exists score;

/*==============================================================*/
/* Table: score                                                 */
/*==============================================================*/
create table score
(
   id                   bigint(40) not null auto_increment comment 'id',
   student_id           bigint(40) not null comment 'Student id',
   project              tinyint(4) default NULL comment 'Competition events (corresponding basic data sheet) type=1)',
   score                decimal(5) default NULL comment 'Fraction',
   ranking              smallint(5) default NULL comment 'ranking',
   unit                 varchar(40) default NULL comment 'Company',
   teacher_id           bigint(40) not null comment 'Teacher id',
   update_time          datetime default NULL comment 'Update time',
   remark               varchar(40) default NULL comment 'Remarks',
   primary key (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Teacher list

drop table if exists teacher;

/*==============================================================*/
/* Table: teacher                                               */
/*==============================================================*/
create table teacher
(
   id                   bigint(40) not null auto_increment comment 'id',
   name                 varchar(40) default NULL comment 'Full name',
   password             varchar(40) default NULL comment 'Password md5 encryption',
   code                 varchar(20) default NULL comment 'Teacher number',
   birthday             date default NULL comment 'Date of birth',
   sex                  tinyint(4) default 1 comment 'Gender (1: male; 2: female)',
   phone                varchar(20) default NULL comment 'Telephone',
   identity             varchar(17) default NULL comment 'ID',
   nation               varchar(20) default NULL comment 'Nation',
   nationality          varchar(20) default NULL comment 'nationality',
   native_place         varchar(20) default NULL comment 'Native place',
   major                varchar(20) default NULL comment 'Teacher subject',
   entrance_date        date default NULL comment 'Arrival time',
   remark               varchar(40) default NULL comment 'Remarks',
   primary key (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Basic Data Table

drop table if exists basic_data;

/*==============================================================*/
/* Table: basic_data                                            */
/*==============================================================*/
create table basic_data
(
   type                 smallint(10) not null comment 'Basic data types',
   name                 varchar(50) default NULL comment 'Base type name',
   basic_code           varchar(50) not null comment 'Basic data No.',
   basic_name           varchar(50) default NULL comment 'Basic Number Name',
   primary key (type, basic_code)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Initialization data
Log table

drop table if exists log;

/*==============================================================*/
/* Table: log                                                   */
/*==============================================================*/
create table log
(
   id                   bigint(40) not null auto_increment comment 'id',
   content              varchar(40) default NULL comment 'Log content',
   type                 tinyint(2) default NULL comment 'Log type (corresponding to underlying data table) type=4)',
   person_id            bigint(40) comment 'Corresponding personnel ID',
   person_name          varchar(40) default NULL comment 'Corresponding Personnel Name',
   log_time             datetime default NULL comment 'Log time',
   remark               varchar(40) default NULL comment 'Remarks',
   primary key (id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Finally, export sql script and import it into Navicat. The sql script will be submitted to the git backend db directory.

Previous article: Spring Boot Vue Element Initial Practical Warfare (11) Backend Spring Boot Service Architecture
Next article: Spring Boot Vue Element Initial Practice (13) Spring Boot+Mybatis+Redis+Swagger
Click here to return to the directory

Source code download

Github: 
https://github.com/Iamoldwang/spring-boot-vue.git
Gitee: 
https://gitee.com/Iamoldwang/spring-boot-vue.git