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

目录

        一、数据库设计原则

                两个目的

                三个范式

                四个经验

        二、数据库设计准备

        三、ER设计

        四、表设计

        五、SQL语句编写

---

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

一、数据库设计原则

• 两个目的

        ——尽量避免数据的冗余

        ——方便对数据进行维护

• 三个范式

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

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

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

• 四个经验

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

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

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

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

二、数据库设计准备

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

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

三、ER设计

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

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

        定义属性：详情见下图。

        产生关系：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 四、表设计

用户信息表user

教师每日时间表teacher_daily_schedule

科目信息subject

课程表course

作业表homework

教师科目留言表 teacher_subject_remark

作业问题图片 homework_question_picture

作业答案图片表 homework_answer_picture

消息表 message

五、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;