4.1 设计数据库

原创已于 2025-03-24 11:23:54 修改 · 639 阅读

31 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2025-03-09 18:19:50 首次发布

MySQL 专栏收录该内容

19 篇文章

订阅专栏

1. 数据建模

此过程要为存储在数据库中的数据创建模型

数据模型有三种：概念、逻辑和实体

2. 概念模型

假如要想建立一个销售在线课程网站，让人们可以注册 or 登记一个 or 多个课程，其中一个课程可以有一个 or 多个标签，如前端 or 后端。

为了给该网站建立数据库，首先需要建立一个概念模型，用以表示业务中的实体、事务或概念以及它们之间的关系。通常，这些实体包括人、事件、位置等。

该系统中需要的实体有，学生、课程。此时我们需要一种可视化的方式观察这些实体和它们之间的关系，通常有两种办法：ER、UML（这两种方法都可以视觉直观地展示概念）。

这就是一个概念模型，至此，还未对每个属性类型的细节作出说明，我们既不知道也不关心未来会使用什么数据库管理系统来完成这个模型。这只是一个概念模型，我们用它来和商业参与方交接，所以需要让对方理解我们所表达的意思，这就是概念模型的好处。

3. 逻辑模型

基于概念模型进行改造，为了生成一个数据模型 or 数据结构来存储我们的数据，这个逻辑模型独立于数据库技术，它是一个抽象的数据模型，能清楚显示我们的实体及关系架构，比概念模型更细节。

首先，我们需要指定每个属性的类型，如字符串、整数、浮点型、日期等等。学生和课程是多对多的关系，因为一个学生可以注册多门课程。

如果我们想知道学生是在哪一天选择了某个课程应该怎么办？这个时间数据应该放置在哪里？我们不可以放置于 Student 中，因为一个学生可能会选修好几门课，所以它应该是”注册“的一种属性。假如，每个学生在不同时间购买的课程价格不同，price 则应该移至 entollment 中。

概念模型与逻辑模型的区别：

概念模型不能真正为我们提供存储数据的结构，它只能代表业务实体及其关系，我们用它来帮助理解问题域，以便和域专家交流。

逻辑模型为概念模型增加了更多细节，这样我们就可以了解什么结构 or 什么表需要用来存储数据。

例子中使用到的实体，最终会以表格的形式出现在我们的数据库中。

4. 实体模型

实体模型是逻辑模型通过特定数据库技术的实现，因此，这里使用逻辑数据模型在 MySQL 中创建一个实体模型。

打开 DBeaver，建立数据库 student，创建表 students、courses 和 enrollments。

5. 主键

现在需要基于以上创建的表，为其添加主键。主键是对表里的每一条列进行唯一标识。

针对表 students，如果使用 first_name 或者 last_name 作为主键，可能会出现重复。也可以使用 first_name 和 last_name 的复合主键，但是也可能会重复。email 自然不会重复，但似乎它的字符长度也许会过长。

此外，理想情况下主键不能更改，所以它的值也应该永远不变，但是学生可能会修改其邮箱地址，因此 email 也不是最佳主键。这个时候适合引入“id”列作为主键。

同样的，courses 表也需要创建“id”列作为主键。

6. 外键

在对 enrollments 表与 students 表建立联系时，关系的一端被称为父表 or 主键表另一端为子表 or 外键表。此时，students 表为父表 or 主键表，因为不能在没有学生的情况下进行注册。

此时可以为 enrollments 添加一个主键，有两个方法：

①使用 student_id 与 course_id 组合，唯一识别每一次注册，即复合主键（方便阻止我们意外为一个同学重复注册同一门课，but 未来出现别的表注册表与这张新表如果存在联系，这两个键会需要在新表中作为外键重复出现）；

②引入名为注册“id”列为新列（如果为未来有新表，新列不会出现什么问题）。此时没有新表出现，可以使用复合键，等以后有了新表可以重新修改该表。

创建外键如下所示：

结果如下所示：

7. 外键约束

外键约束本质上可以保护数据不受损坏、

打开 enrollments 表可以看到，有 2 个外键，分别是 student_id（引用了学生表中的 id）和 course_id（引用了课程表中的 id），并且二者的组合也构成了这张表的主键。

打开 enrollments 外键时，可以观察到有“删除时的规则”和“更新时的规则”，例如当学生的 id 从 1 变成 2 时要进行什么样的操作？这里我们需要确定当主键表更新时，外键表也要随之更新。删除操作也同理，假如表里存储的信息时财务信息，我们可能不需要主键删除时子表中的内容同样删除，可能会选择“限制”。

外键的约束情况要根据业务的实际情况去设置。

8. 标准化

在继续生成数据库表格之前，需要确保设计是最优的，不允许存在冗余 or 重复的数据，因为冗余增加了数据库的大小，而且会使插入、更新和删除操作复杂化。

例如，如果某个人的名字在许多不同的地方重复，然后他们决定改名，那么就需要同步更新好几个地方的名字，否则数据会不一致，造成无效报告，因此需要标准化。

标准化是审查我们的设计，并确保它遵循一些防止数据重复的预定义规则的过程，有七条规则，但是一般只需要用到前三条。

8.1第一范式

一行中的每个单元格都应该有单一值，且不能出现重复列。

观察 courses 表，可以发现不遵循该范式。该表中的 tags 列会存储多个标记，并且使用逗号进行分割，会导致该单元格 or 属性中存在多个值，如果设置成标签 1、标签 2 这样的好几个列也不行，因为我们并不知道每门课会有多少个标签，不可能穷举，如果后续继续加 tag，则需要重新过来修改表，过于麻烦。

为了解决该问题，可以把 tags 列从表中拎出来，作为 tags 表建模，然后为 tags 和 courses 表增加多对多的关系。在设置 tags_id 列时，需要注意是管理员去设置，还是终端用户，如果是终端用户则会迅速增加，这里假设是由管理员去创建，并且最多有 50～60 个标签，因此设计成微型整数（TINYINT）。

8.2 接表

现在需要在 courses 表和 tags 表中增加“多对多”关系，但是在 MySQL 中没有该关系，只有“一对一”和“一对多”关系，所以为了实现这两张表之间的“多对多”关系，需要引入新的表，称为“链接表”，和“注册表”完全一样。设置好后可以删除 tags 列。

8.3 第二范式

首先必须满足第一范式

并且它不能有任何取决于这组关系任何候选键的任何真子集的非主属性

简而言之，第二范式要求每一张表都应该有一个单一目的，也就是说，它应该只能代表一种且仅有一种实体类型，而且表中的每一列都应该用来描述这个实体。

在这个例子中，courses 表包括 title、course_id 等，目的是单一的——用以存储课程记录，此表中每一列都是课程的一种属性。

如果有一列描述了其他东西，就应该拿掉它，单独设计成表。

如下所示是一个订单表，order_id 和 date 都是订单的属性，但是 customer_name 列描述了一名顾客，而不是订单。

如果订单越来越多，则会导致他的名字重复出现，最后会造成两个问题：

①浪费空间

②更新会很痛苦（想要更新一个客户名，得在更多地方同时更新）

如果这样去修改，只需要在 costomers 表中修改，所有地方将同时更新

例子中的 courses 表，instructor 违反了第二范式，它不应该属于该表，如果一个讲师授课多门课，他的名字会重复出现，所以需要单独设计一个 instructors 表。

8.4 第三范式

实体 or 表应该符合第二范式

表中的所有属性只能由那组关系的候选键决定，而不能是任何非主属性

如图所示，表中的 balance=invoice_total-payment_total，这个时候会发生一件事情，就是 balance 得手动同步更新，如果忘记更新就会造成麻烦，所以说表中的列不应该由其他列决定。

包括下面的表也是同样的问题，full_name 是 first_name 和 last_name 组合而成。

所以出现这样的问题，需要删除这一列。

8.5 实用建议

1. 应该从逻辑 or 概念模型设计开始，不能跳过直接创建表。

实际工作时只需要专注于消除冗余即可，不需要对每张表和每一列逐个应用标准化程序。

只需要记得，当出现下面的这种情况时，意味设计没有标准化，至于违反哪条范式不重要。

如下面的例子，一个顾客可能会有多个收货地点，如果它每次增加地点，则会有很多 id 和 name 的重复，如果这张表里有更多信息（email、电话）就会造成大量重复。

对于这种 1 对多的情况，可以考虑设计成两个表。

所以，应该从逻辑 or 概念模型设计开始，不能跳过直接创建表。

2. 不要什么都建模

要考虑项目的范围，不要试图把所有大大小小的事情都建模，无视项目背景，无视业务需求。只需要针对现下问题制定最佳解决方案即可。

9. 视频租赁应用项目

需求说明

我们将构建一个名为 Vidly 的桌面应用程序，该应用程序将在一家视频租赁店使用。不同级别的用户将具有不同的权限。

店长需要能够添加/更新/删除电影列表。他们负责为每部电影设置库存数量以及每日租金价格。

收银员 只能查看电影列表，无法修改。他们可以 管理顾客信息 以及顾客租赁的电影。

结账流程

顾客携带一部或多部电影到前台结账。

收银员根据 顾客的电话号码 查找顾客信息。

如果是 首次租赁 的顾客，收银员需要登记其 姓名、电子邮件和电话号码，然后将其注册到系统中。

之后，收银员扫描电影封面上的 10 位条形码，记录租赁的电影。

电影归还

当顾客归还电影时，系统将计算应付租金：

正常归还：按照租赁天数和每日租金费率计算费用。

丢失电影：顾客需要支付 5 倍的每日租金，并且库存数量减少（无需记录具体丢失的电影，只需更新库存和费用）。

优惠券

商店会不定期发放 折扣优惠券，顾客在归还电影时可使用优惠券享受折扣。

顾客可能会在 多次归还 电影，即并非一次性归还所有已租赁的电影。

系统功能

系统需要能够跟踪以下数据：

最受欢迎的电影

最常租赁的顾客

每日、每月、每年的收入情况

操作过程

1. 概念模型

2. 逻辑建模

用户和权限的关系选择“多对多”，因为一个用户可以拥有多个权限，而同样的权限课可以给到不同的用户；所以，需要考虑到：

我们要特意将不同权限分配给不同用户，也就是说，收银员 or 店长无法拥有相同的权限。并且，需求中说明了不需要为每一个用户设置不同的权限，所以此处需要引入新实体——“身份”。可以将用户分到“店长”or“收银员”的身份组中，然后将权限直接分给“身份”组，这样下一次用户注册账户时，直接把用户添加成“店长”组 or“收银员”组，他自然而言就有了对应身份的权限。

但是，在对比需求时可以发现，店长和收银员的区别只有，店长拥有一切权限，收银员没有管理电影列表这一项权限，因此可以在设计 app 的时候加一个 if 条件语句进行筛选，而无需专门设计一个“权限表”。

10. 创建数据库

创建数据库 sql_store2

create database sql_store2;

删除数据库 sql_store2

drop database if exists sql_store2;

11. 创建表

在数据库 sql_store2 中创建一个 customers 表，其中的列包括 customer_id、first_name、points 和 email。

create database sql_store2;

use sql_store2;

drop table if exists customers; -- 如果表customers存在则删除

create table if not exists customers -- 如果表customers不存在则创建表
        (
        customer_id int primary key auto_increment, -- 整数int、primary key主键、 auto_increment自动递增
        first_name varchar(50) not null , -- varchar(50)字符串、not null非空
        points int not null default 0, -- default 0默认值为0
        enmail varchar(250) not null unique -- unique唯一属性
        );

12. 修改表

假如创建表后发现忘记创建列 last_name，或者某列设置了错误的数据类型，则：

use sql_store2;

alter table customers
        add last_name varchar(50) not null after first_name,
        add city      varchar(50) not null ,
        modify column first_name varchar(55) default '', -- 修改first_name列数据类型为varchar(50)，添加默认值为
        drop points -- 删除列points
        ;

13. 创建关系

创建一个表 orders，并设置表 customers 中的 customer_id 作为 orders 的外键。

drop table if exists orders;
create table orders
        (
                order_id       int primary key,
                customer_id int not null,
                foreign key fk_orders_customers  (customer_id)   -- 让customer_id作为外键，连接customers表中对应列
                        references customers (customer_id) -- 连接customers表
                        on update cascade -- 更新时联级
                        on delete no action  -- 删除时无操作
        );

14. 更改主键/外键

alter table orders
        add primary key (order_id),  -- 添加主键
        drop primary key ,
        drop foreign key orders_ibfk_1, -- 删除外键
        add foreign key fk_orders_customers (customer_id) -- 添加外键
                references customers (customer_id)
                on update cascade
                on delete no action;

15. 字符集和排序规则

什么是字符集？

当存储一个字符串，比如“ABC”时，MySQL 会使用字符集将每个字符转换为它的数值表示，因此字符集是将每个字符映射到数字的表。

sql
show charset

可以看到当前版本 MySQL 支持的所有字符集，当我们创建数据库的时候，数据库支持国际语言。其中 default collation 是默认排序规则，决定了某种语言的字符如何排序。maxlen 是最大长度列，即 MySQL 存储每个字符的时候最多保留的字节。

utf8 最长字节为 3，假如有一张表，其中有一列的类型为 char（10），表示固定长度的字符串，代表不管实际使用了 1 个字符还是 10 个字符，MySQL 还是机会保留 10 个字符串的空间，如果使用的 utf8 字符集，MySQL 则会为每个字符串预留最大字节数 3，这个列则会为每个单元格预留 10×3 的空间，也就是 30 个字节，如果这张表有 100 万记录，MySQL 则会为这张表配置 3000 万个字节的空间。这个时候，如果我们改变字符集，换成最长字节 1 节，则可以减少 MySQL 占用磁盘的字节数，可以减少 1000 万个字节。

修改字符集的方法如下所示：

方法一：

方法二：

create database db_name
        character set latin1;

alter database db_name
        character set latin1;

alter table table1
        character set latin1;
       
alter table customers
    add last_name varchar(50) character set  latin1  not null after first_name