数据库设计——Mysql项目实践

拾戒

已于 2023-06-28 11:07:58 修改

阅读量2.8k

点赞数 4

文章标签：数据库

于 2023-06-28 11:04:39 首次发布

本文链接：https://blog.csdn.net/weixin_61870990/article/details/131422357

版权

敏捷开发过程中，迭代需求人员每交付一次原型图、需求规格说明时，都需要有专门人员进行后续的数据库设计与开发工作。数据库的设计质量将直接影响到系统的整体性能，为此数据库设计人员应遵守数据库设计原则，保证所设计数据库保证系统功能需求的同时，提高系统部分性能。下面，让我们理论与实践相结合，习得数据库设计的原则后，笔者将通过对一个项目——培训机构管理系统进行Mysql数据库设计实践，希望能够给有需要的小伙伴一些帮助。

一、数据库设计原则

两个目的

——尽量避免数据的冗余

——方便对数据进行维护

三个范式

——第一范式：每个属性都可再分的原子项

——第二范式：满足第一范式，且不存在部分依赖，非主属性必须完全依赖于主属性

——第三范式：满足第二范式，且不存在传递依赖，非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖于主属性。

四个经验

——一张表只能描述一个实体

——实体与实体之间是一对一的关系，通过外键来维护实体之间的关系，外键可以放在任何一方

——实体与实体之间是一对多的关系，通过外键来维护实体之间的关系，外键放在多的一方

——实体与实体之间是多对多的关系，借助中间表通过外键来维护实体之间的关系，外键放在中间表一方

二、数据库设计准备

在数据库设计开始之前，我们手上需要得到一些需求资料，例如需求人员提供的原型图+规格说明书或者是原型图+业务说明书又或者单单是某种需求文档，无论手中掌握哪种需求资料，这一步是必须有的，它是后续数据库设计过程依仗的根本。以培训机构管理系统为例，由于笔者在本项目中担任需求兼开发的工作，项目开始之初便投入于系统需求的探讨，完成了各阶段原型图的设计工作以及规格说明书和业务说明书的编写，对于系统需求有着很好的理解，手上掌握着多种需求资料，因此准备工作会相对轻松一些，想要跟着我们项目进行数据库设计的小伙伴，可以查看笔者的上一篇文章原型设计——摹客RP项目实践，文内有详细的系统原型图设计提供。

获取需求资料后，数据库设计就剩下了毫无感情的ER设计、表设计和SQL语句编写啦！

三、ER设计

ER设计的工作主要分为三步：确定实体、定义属性、产生关系。

确定实体：根据原型图以及业务逻辑，本培训机构管理系统可分为九个实体，分别为用户、教师每日时间、科目、课程、作业问题图片、作业答案图片、作业、教师课程留言和消息。

定义属性：详情见下图。

产生关系：

①一个教务员可以管理多个教师，一个教师只由一个教务员管理；

②一个教务员可以管理多个学生，一个学生只由一个教务员管理；

③一个用户可以接收n条消息，一条消息只属于一个用户；

④一个教师匹配多张教师每日时间表，一张教师每日时间表只属于一个教师；

⑤一个教务员可以创建多个科目，一个科目只能由一个教务员创建；

⑥一个教师只能教授一个科目，一个科目可以由多个教师教授；

⑦一个学生可以选择多个科目，一个科目可以被多个学生选择；

⑧一个教师可以有多条课程记录，一条课程记录只能对应一个教师；

⑨一个科目可以有多条课程记录，一条课程记录只能对应一个科目；

⑩一个学生可以有多条课程记录，一条课程记录只能对应一个学生；

⑪一个教师可以发布多个作业，一个作业只能由一个教师发布；

⑫一个学生可以接收多个作业，一个作业只能由一个学生接收；

⑬一个教师拥有多条留言，一条留言只属于一个教师；

⑭一个作业可以匹配多张作业问题图片，一张作业问题图片只属于一份作业；

⑮一个作业可以匹配多张作业答案图片，一张作业答案图片只属于一份作业；

⑯一个课程可以拥有多个作业，一个作业只能属于一个课程。

四、表设计

用户信息表user

字段名	数据类型	长度	非空	主键	描述
id	int	/	是	是	用户id，自增
phone	bigint	/	是	/	手机号，唯一
password	varchar	30	是	/	用户密码，默认值为8888
type	tinyint	/	是	/	用户类型：0-教务员、 1-教师、2-家长
realname	varchar	20	是	/	教务员、教师和学生对应的真实姓名
subject_id	int	/	否	/	科目id
gender	tinyint	/	是	/	性别：0-男、1-女
title	tinyint	/	否	/	职称：0-正高级、1-高级、2-一级、3-二级、4-三级
age	tingint	/	否	/	年龄

教师每日时间表teacher_daily_schedule

字段名	数据类型	长度	非空	主键	描述
id	int	/	是	是	教师每日时间表id，自增
teacher_id	int	/	是	/	教师id
weekday	tinyint	/	是	/	星期几，1-星期一、星期二、3-星期三、星期四、5-星期五、 6-星期六、7-星期日
am_1_busy	tinyint	/	是	/	状态：0-false、1-true 早上第一节课，8:00-9:30
am_2_busy	tinyint	/	是	/	状态：0-false、1-true 早上第二节课，10:00-11:30
pm_1_busy	tinyint	/	是	/	状态：0-false、1-true 下午第一节课，14:00-15:30
pm_2_busy	tinyint	/	是	/	状态：0-false、1-true 下午第二节课，16:00-17:30

科目信息subject

字段名	数据类型	长度	非空	主键	描述
id	int	/	是	是	科目id，自增
name	varchar	30	是	/	科目名称

课程表course

字段名	数据类型	长度	非空	主键	描述
id	int	/	是	是	课程id，自增
subject_id	int	/	是	/	科目id
teacher_id	int	/	是	/	教师id
student_id	int	/	是	/	学生id
class_weekday	tinyint	/	是	/	周几上课，选择项:1~7
classes	char	3	是	/	上课场次选择项：am1、am2、 pm1、pm2
class_ending	tinyint	/	是	/	是否结课：0-否、1-是默认是0

作业表homework

字段名	数据类型	长度	非空	主键	描述
id	int	/	是	是	作业id，自增
title	varchar	30	是	/	作业标题
subject_id	int	/	是	/	作业所属科目的id
teacher_id	int	/	是	/	教师id，发布者id
student_id	int	/	是	/	学生id，接收者id
start_date	datetime	/	否	/	起始日期
end_date	datetime	/	否	/	截止日期
question_text	nvarchar	1000	否	/	作业问题文本
answer_text	nvarchar	1000	否	/	作业答案文本
score	int	/	否	/	作业分数
teacher_comment	varchar	100	否	/	教师评语
state	tinyint	/	是	/	作业的状态：0-未提交、1-待批阅、2-已完成,初始为0
is_excellent	tinyint	/	是	/	是否为优秀作业：0-否，1-是，初始为0
student_name	varchar	20	是	/	冗余字段，作业接收者的名字，一次写入方便查表。
student_phone	bigint	/	是	/	冗余字段，作业接收者的电话，一次写入方便查表。

教师科目留言表 teacher_subject_remark

字段名	数据类型	长度	非空	主键	描述
id	int	/	是	是	留言id，自增
teacher_id	int	/	是	/	教师id，回复留言的人留言拥有者
student_id	int	/	是	/	学生id，留言的人
spoke_content	varchar	50	是	/	留言内容
spoke_time	datetime	/	否	/	留言时间
reply_content	varchar	50	否	/	回复内容
reply_time	datetime	/	否	/	回复时间

作业问题图片 homework_question_picture

字段名	数据类型	长度	非空	主键	描述
id	int	/	是	是	图片id，自增
homework_id	int	/	是	/	作业id
picture_url	varchar	100	是	/	问题图片url

作业答案图片表 homework_answer_picture

字段名	数据类型	长度	非空	主键	描述
id	int	/	是	是	图片id，自增
homework_id	int	/	是	/	作业id
picture_url	varchar	100	是	/	答案图片url

消息表 message

字段名	数据类型	长度	非空	主键	描述
id	int	/	是	是	消息id，自增
sender_id	int	/	是	/	消息发布者id
recipient_id	int	/	是	/	消息接收者id
type	tinyint	/	否	/	消息类型：0-排课成功、 1-课程修改、2-上课提醒、3-结课提醒。
relevant_person	varchar	20	否	/	相关人员名字
phone	bigint	/	否	/	相关人员手机号
subject_id	int	/	否	/	相关科目id
class_weekday	tinyint	/	否	/	课程在周几，选择项:1~7
classes	char	3	否	/	上课场次选择项：am1、am2、 pm1、pm2
modify_class_weekday	tinyint	/	否	/	修改后-课程在周几，选择项:1~7
modify_classes	char	3	否	/	修改后-上课场次选择项：am1、am2、 pm1、pm2
handle	tinyint	/	是	/	消息是否已处理：否、1-是，默认为0 消息是否已读：否、1-是，默认为0

五、SQL语句编写

CREATE DATABASE `edu2`;
USE `edu2`;

DROP TABLE IF EXISTS `message`;
DROP TABLE IF EXISTS `homework_answer_picture`;
DROP TABLE IF EXISTS `homework_question_picture`;
DROP TABLE IF EXISTS `teacher_subject_remark`;
DROP TABLE IF EXISTS `homework`;
DROP TABLE IF EXISTS `course`;
DROP TABLE IF EXISTS `teacher_daily_schedule`;
DROP TABLE IF EXISTS `user`;
DROP TABLE IF EXISTS `subject`;

CREATE TABLE `subject`(
	`id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(30) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user`(
	`id` int NOT NULL AUTO_INCREMENT,
	`phone` bigint NOT NULL UNIQUE,
    `password` varchar(30) NOT NULL DEFAULT '8888',
    `type` tinyint NOT NULL COMMENT '0:教务员 1:教师 2:学生',
    `realname` varchar(20) NOT NULL,
    `subject_id` int,
    `gender` tinyint NOT NULL COMMENT '0:男 1:女',
    `title` tinyint COMMENT '0:正高级 1:高级 2:一级 3:二级 4:三级',
    `age` tinyint,
	PRIMARY KEY (`id`),
    CONSTRAINT `fk_subject_id1` FOREIGN KEY (`subject_id`) REFERENCES  `subject`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `teacher_daily_schedule`(
	`id` int NOT NULL AUTO_INCREMENT,
    `teacher_id` int NOT NULL,
    `weekday` tinyint NOT NULL COMMENT '1:星期一 2:星期二 3:星期三 4:星期四 5:星期五 6:星期六 7:星期日',
	`am_1_busy` tinyint NOT NULL COMMENT '0:false 1:true',
    `am_2_busy` tinyint NOT NULL COMMENT '0:false 1:true',
    `pm_1_busy` tinyint NOT NULL COMMENT '0:false 1:true',
    `pm_2_busy` tinyint NOT NULL COMMENT '0:false 1:true',
	PRIMARY KEY (`id`),
    CONSTRAINT `fk_teacher_id1` FOREIGN KEY (`teacher_id`) REFERENCES  `user`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `course`(
	`id` int NOT NULL AUTO_INCREMENT,
    `subject_id` int NOT NULL,
    `teacher_id` int NOT NULL,
	`student_id` int NOT NULL,
    `class_weekday` tinyint NOT NULL COMMENT '1:星期一 2:星期二 3:星期三 4:星期四 5:星期五 6:星期六 7:星期日',
    `calsses` char(3) NOT NULL COMMENT 'am1:8:00-9:30 am2:10:00-11:30 pm1:14:00-15:30 pm2:16:00-17:30',
	`class_ending` tinyint NOT NULL DEFAULT 0 COMMENT '0:否 1：是',
	PRIMARY KEY (`id`),
    CONSTRAINT `fk_subject_id2` FOREIGN KEY (`subject_id`) REFERENCES  `subject`(`id`),
    CONSTRAINT `fk_teacher_id2` FOREIGN KEY (`teacher_id`) REFERENCES  `user`(`id`),
    CONSTRAINT `fk_student_id` FOREIGN KEY (`student_id`) REFERENCES  `user`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `homework`(
	`id` int NOT NULL AUTO_INCREMENT,
    `title` varchar(30) NOT NULL,
    `subject_id` int NOT NULL,
    `teacher_id` int NOT NULL,
    `student_id` int NOT NULL,
	`start_date` datetime,
    `end_date` datetime,
    `question_text` nvarchar(1000),
    `answer_text` nvarchar(1000),
    `score` int,
    `teacher_comment` varchar(100),
    `state` tinyint DEFAULT 0 NOT NULL COMMENT '作业的状态：0-未提交、1-待批阅、2-已完成',
    `is_excellent` tinyint DEFAULT 0 NOT NULL COMMENT '是否为优秀作业：0-否，1-是',
    `student_name` varchar(20) NOT NULL,
    `student_phone` bigint NOT NULL,
	PRIMARY KEY (`id`),
    CONSTRAINT `fk_teacher_id3` FOREIGN KEY (`teacher_id`) REFERENCES  `user`(`id`),
    CONSTRAINT `fk_student_id2` FOREIGN KEY (`student_id`) REFERENCES  `user`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `teacher_subject_remark`(
	`id` int NOT NULL AUTO_INCREMENT,
    `teacher_id` int NOT NULL,
	`student_id` int NOT NULL,
    `spoke_content` varchar(50) NOT NULL,
    `spoke_time` datetime,
    `reply_content` varchar(50),
    `reply_time` datetime,
	PRIMARY KEY (`id`),
    CONSTRAINT `fk_teacher_id4` FOREIGN KEY (`teacher_id`) REFERENCES  `user`(`id`),
    CONSTRAINT `fk_student_id3` FOREIGN KEY (`student_id`) REFERENCES  `user`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `homework_question_picture`(
	`id` int NOT NULL AUTO_INCREMENT,
    `homework_id` int NOT NULL,
    `picture_url` varchar(100) NOT NULL,
	PRIMARY KEY (`id`),
    CONSTRAINT `fk_homework_id1` FOREIGN KEY (`homework_id`) REFERENCES  `homework`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `homework_answer_picture`(
	`id` int NOT NULL AUTO_INCREMENT,
    `homework_id` int NOT NULL,
    `picture_url` varchar(100) NOT NULL,
	PRIMARY KEY (`id`),
    CONSTRAINT `fk_homework_id2` FOREIGN KEY (`homework_id`) REFERENCES  `homework`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
CREATE TABLE `message`(
	`id` int NOT NULL AUTO_INCREMENT,
    `sender_id` int NOT NULL,
	`recipient_id` int NOT NULL,
    `type` tinyint COMMENT '0-排课成功、1-课程修改、2-上课提醒、3-结课提醒',
    `relevant_person` varchar(20) NOT NULL,
    `phone` bigint,
    `subject_id` int,
    `class_weekday` tinyint,
    `classes` char(3),
    `modify_class_weekday` tinyint,
    `modify_classes` char(3),
    `handle` tinyint DEFAULT 0 NOT NULL COMMENT '0:未处理/未读   1:已处理/已读',
	PRIMARY KEY (`id`),
    CONSTRAINT `fk_sender_id` FOREIGN KEY (`sender_id`) REFERENCES  `user`(`id`),
    CONSTRAINT `fk_recipient_id` FOREIGN KEY (`recipient_id`) REFERENCES  `user`(`id`),
    CONSTRAINT `fk_subject_id4` FOREIGN KEY (`subject_id`) REFERENCES  `subject`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;