MySQL设计

MySQL数据库设计与范式解析

原创已于 2025-07-27 16:48:42 修改 · 900 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2025-07-18 13:06:16 首次发布

MySQL 专栏收录该内容

6 篇文章

订阅专栏

文章目录

一、范式
二、表的设计

一、范式

所谓的范式就是设计数据库表的时候，基本的规范和约定。和前面的数据库操作不同，数据库操作那叫做语法，是硬性的规定。而范式是描述如何建表，是一个比较好的建法，怎么建表是不科学的方法，是一个软性要求（也就是你设计表不遵守也可运行，硬性要求则是不遵守数据库就会报错）。范式主要有三个：

1.1 第一范式

数据库表中的每一列都是不可分割的，而不能是集合、数组、对象等类型。
在关系型数据库的设计中，满足第⼀范式是对关系模式的基本要求。不满足第⼀范式的数据库就不能被称为关系数据库。

例如：一个学生表

在这里插入图片描述

在这里学生表中的信息记录了学生的id，学生的姓名，学生的班级以及家庭。但是家庭填写的话还需要划分家庭的地址和家庭的电话，所以在这里家庭这一列还可以继续分割不满足第一范式的要求。

1.2 第二范式

在满足第一范式的基础上，不存在非关键字段（非主键）对任意候选键（主键：也就是被primary key约束的）的部分函数依赖。所以第二范式的约束，就是希望消灭部分依赖。也就是第二范式是约束表中定义了复合主键的情况。

所谓的候选键就是主键，可以作为一条记录的身份标识，在MySQL增删查改进阶小编曾讲述过，主键可能是一列，也可能是多个列共同构成一个主键，这种叫做联合主键。比如下面

在这里插入图片描述
在这里存在三张表，一个学生表，一个课程表，还有一个分数表。在学生表当中，可以根据学生的id确定学生的姓名，在课程表当中可以根据课程的id确定课程的名字，像学生表和课程表当中的id则被称为主键，而在
分数表就是一个联合主键的关系，一个学生的成绩需要学生的id和课程的id共同确定。把学生id和课程id这两列打包放在一起共同确定，这种就叫做联合主键。

所谓的非关键字段则是不属于候选键的列。

函数依赖

部分函数依赖：不必通过完整的候选键，就可以确定一个值。
完全函数依赖：通过完整的复合主键，确定一个值。

在这里插入图片描述

在上面整个表的候选键是学号+课程名。
但是学生姓名，性别，年龄这三个列，只依赖学号即可（也就是知道学生的学号就可以知道学生的姓名、年龄和性别，并不能决定学分的多少）。学分这一列，只依赖课程名即可（即学分是多少由课程名决定，不用学号进行决定）。上面两个则是部分依赖。像成绩这一列同时依赖学号和课程名这就叫完全依赖。

部分依赖则会造成：

数据的冗余，像张三和李四名字和数据同时出现两次，只是后面的成绩不同。
数据在维护的时候，进行增删改查就会容易出现问题。假设你将张三的年龄修改为20岁，但是你忘了修改另外一个数据的年龄，那么张三的年龄到底是18呢还是20岁呢？

所以针对需求，应该设计三张表：学生表、课程表、成绩表。

在这里插入图片描述

1.3 第三范式

在满足第二范式的基础上，要求非主属性（非关键字段）不能依赖于其他非主属性（也就是不存在非关键字段，对任一候选键的传递依赖）。

所谓的传递依赖如下：假如存在三列

在这里插入图片描述

例如：
在这里插入图片描述

在上面只有一个主键学生id，只要学生id确定了，剩下的像学号、姓名、年龄、性别、学院、学院电话也就确定了。但是学院电话依赖于学院，只需要学院确定了，学院电话就知道了，并不直接依赖学生id。

但是学生id确定了，学院也就确定了，学院确定了，学院的电话也就确定了，构成了一个传递依赖。这种传递依赖会造成数据的冗余，也就是你想再添加一个赵六同学的信息，假如也在通信工程学院，那么就会继续重复填写学院电话，数据少还好，像如果数据几千万几百万条，那么重复就高了。所以正确的做法应该分成两个表：学生表和学院表

在这里插入图片描述

二、表的设计

所谓表的设计，就是根据实际的需求场景，明确当前要创建几个表，每个表啥样子，这些表之间是否存在一定联系。主要分为两个操作：

梳理清楚，需求中的实体。所谓的实体就是类似java中的对象。一般来说，每个实体都需要安排一个表，表的列对应到实体的各个属性。
确定实体之间的关系。不同关系的实体设计表的方式也是不同的，主要存在三种关系：一对一；一对多，多对多。

2.1 一对一表关系

所谓的一对一表关系，就是一个表中的一个数据和另外一个表中的数据相对应。比如下方图：一个学生对应一个学生账号，一个账号也只能被一个学生所拥有。

在这里插入图片描述

例如下面创建表

1. 不推荐的做法

create table student(
id int primary key auto_increment,
name varchar(20) not null,
userName varchar(20) not null,
password varchar(20) not null
);

在上面用一张表表示学生和账号，但是会存在部分依赖，也就是说后面的用户名和密码依赖于账户，这不符合第三范式，非主属性（非关键字段）依赖于非主属性（非关键字段）。

2.建议的做法

分成两张表：一张学生表，一张账号表。

drop table if exists student;
create table student(
  id int primary key auto_increment,
  name varchar(20) not null,
  accountId int
);
create table accountId(
  accountId int primary key ;
  userName varchar(20) not null,
  password varchar(20) not null
)

2.2 一对多表关系

该关系就像一个学生只能在一个班级当中，就跟你小学，初中一样，你在某某某班，但是该班还存在其他的学生。

在这里插入图片描述

2.3 习题巩固

习题一
在这里插入图片描述

解析：在上面设计一个考勤系统，包括两张表，一张是员工表，一张是考勤记录表。首先分析这两张表的关联，一个员工可以进行很多次打开，所以员工表和考勤记录表是一对多的关系。需要注意的是，使用外键约束的时候，父表（员工表）被关联的表需要被unique修饰或者为主键。

答案：

create database if not exists attendance_system;
use attendance_system;
show tables;
# 创建员工表
create table user(
  id int primary key,
  name varchar(20) not null
);
# 创建考勤系统
create table info(
  # 每一个考勤记录都有id
  info_id int primary key,
  # 考勤时间
  date datetime,
  # 员工
  user_id int,
  # 外键约束
  foreign key(user_id) references user(id)
);

习题二
在这里插入图片描述

解析：
设计学校管理系统，包含三个表：宿舍信息表，学生信息表，每日的宿舍查房记录表。首先需要找到各个表之间的关联性。首先一个宿舍会有多名学生，而一名学生不可能存在于多个宿舍中，所以学生和宿舍的关系是一对多的关系；再看到宿舍查房记录表，宿舍查房肯定是每个宿舍进行进行查，一个宿舍可以被查多次。所以宿舍查房记录表和宿舍关系是多对一的关系。

答案：

create database school_system;
use school_system;
# 创建宿舍表
create table dorm(
  id int primary key,
  number varchar(20)
);
  
# 创建学生表
create table student(
    id int primary key,
    name varchar(20) not null,
    # 哪个宿舍
    dorm_id int,
    foreign key(dorm_id) references dorm(id)
  );
    
# 创建查房记录表
create table info(
      # 每次查房的编号
      id int primary key,
      dormitory_id int,
      # 查房的时候状态，是否缺少人，不缺少写0，缺少写个数
      num int,
      # 查房的时间
      date datetime,
      # 外键约束
      foreign key(dormitory_id) references dorm(id)
  );

习题三

在这里插入图片描述

解析：
首先用户可以拥有多辆车，关系为1:m，题目已经说明违章信息包括用户和车辆，说明违章信息表中要记录用户和车辆，一个用户可以有多次违章记录，用户与违章记录关系为1:m，一辆车也可以有多次违章记录，车辆与违章记录关系也为1:m。

答案：

# 创建用户表
create table user(
  
  id int primary key,

  name varchar(20)
);

# 创建车辆表
create table cars(

  id int primary key,

  name varchar(20),

  user_id int,

  foreign key (user_id) references user(id)
);

# 创建违规信息表
create table info(
  # 违规单号
  id int primary key,
  # 违规用户
  user_id int,
  # 违规用户的车辆
  cars_id int,
  # 外键约束
  foreign key (user_id) references user(id),
  # 外键约束
  foreign key (cars_id) references cars(id)
);

习题四

在这里插入图片描述

解析：
看到这道题目，设计学校食堂管理系统，分别存在三个表：食堂表，食堂仓口表，仓口收费表。分析三个表之间的关系，首先食堂会存在很多个仓口，所以食堂和仓口的关系是一对多的关系；然后每个仓口可以进行多次收费，所以仓口和仓口收费表之间的关系也是一对多的关系。

答案：

# 创建食堂
create table hall(

  id int primary key,

  name varchar(20)
);
# 创建仓口
create table hall_opening(

  id int primary key,

  name varchar(20),

  hall_id int,

  foreign key (hall_id) references hall(id)
);
# 创建仓口收费表
create table info(
  # 收费id
  id int primary key,
  # 价格
  price int,
  # 收费时间
  info_date timestamp,
  # 收费的仓口号
  hall_opening_id int,
  # 外键约束
  foreign key (hall_opening_id) references hall_opening(id)
);

2.4 多对多表关系

一个学生可以选择多门课程，一门课程也可以被多个学生选择，但是多对多关系需要借助一个关联表来表示。

在这里插入图片描述
这里为什么不能直接关联，而需要借助上述像学生课程表这样一个关联表呢？

数据库限制：关系型数据库无法表示多对多关系。即不能在学生表中存储多个课程ID，也不能在课程表中存储多个学生ID。如果存储多个的话就比如张三，所选课程1，2，3，这样用逗号隔开，其中课程这一列可以划分为多个课，不符合第一范式的规范。
数据冗余：如果尝试直接关联会导致：学生表中重复存储同一课程的多个记录，课程表中重复存储同一学生的多个记录。

上述表的关系还可以通过ER图画出来。ER图（Entity-Relationship Diagram：实体关系图）是数据库设计中的核心工具，这里小编不加赘述。