数据库设计笔记

数据库设计

一、ER图

ER图（Entity-Relationship Diagram）是一种用于描述实体间关系的图形化表示方法，主要用于数据库设计，可以清晰地展示实体、属性和实体间的联系。

1. ER图三要素

三要素：

实体（Entity）：表示现实世界中的一个对象、事物或概念，可以具有属性和标识符。
属性（Attribute）：描述实体的特征或属性。每个实体可以有多个属性，如姓名、年龄、性别等。
联系（Relationship）：表示实体之间的连接或联系。关系可以是一对一、一对多或多对多的关系。

2. ER图关系

上述符号通常是两个成对使用，其分别表示最小值和最大值。

例如上述ER图中的班级和学生之间的连线，班级一侧的符号表示一（最小值和最大值都是一），学生一侧的符号表示多（最小值是一，最大值是多），其表达的含义就是班级和学生之间的关系为一对多，一个学生只对应一个班级，而一个班级会对应多个学生（且至少对应一个学生）。

二、数据库设计

0. 实体间关系

1-1：无论在哪个表建立关联字段即可。
1-N：在N表中维护1的唯一字段。
N-N：需要中间表，在中间表中维护字段关系。

1. 概念模型

概念模型是一个粗略的初步设计，其只关注实体和关系，不体现最终建表所需的各种细节信息（例如实体的属性）。下图便是一个典型的简易选课系统数据库的概念模型。

2. 逻辑模型

相较于概念模型，逻辑模型会包含更多的细节信息，例如实体的属性、用于关联两个实体的字段等等。需要注意的是，逻辑模型并不关注具体的数据库实现（例如MySQL或者Oracle）。下图是上述选课系统数据库的逻辑模型。

3. 物理模型

相较于逻辑模型，物理模型会包含更多的与所选数据库相关的具体信息，例如存储引擎、字段类型、索引等信息。一般而言，物理模型会包含最终建表所需的所有信息，下图是上述选课系统数据库的物理模型。

4. 数据库表设计最佳实践

设计规范：

明确需求，迭代设计；没有一次性就设计完成的系统
统一命名规范；全部使用小写字母，多单词用 _ 连接，避免使用保留字
合理选择数据类型、大小、NULL值：
1. 主键：bigint、无符号型、自增。
2. 状态类字段：用tinyint、无符号型，最大标识256种状态
3. 字符串字段：
  1. 约束好大小，varchar(100)与varchar(255)在磁盘存储是一样的，但是查询加载到内存中是不一样的。所以依然推荐约束大小，能用小则不用大，提升性能。
  2. 参与的检索字段用char（定长）的性能远高于用varchar（变长）。
  3. 超大文本，建议单独表存储。选用text类型
4. 业务上非null字段，数据库也非null约束，节省空间，提升性能
5. 日期字段区别：datetime、time、timestamp
不做外键：由业务代码保证数据一致性。提升性能
合理选择冗余存储：经常联表查询的某几个字段，可以进行冗余存储，避免联表，提高性能
最好不要用触发器这些隐藏机制；适当使用存储过程

三、RBAC权限模型

1. ACL

ACL：Access Control List，访问权限列表。用户和权限是N-N关系，权限大量重复冗余严重。

例如，需要执行如下sql：

select u.*, p.*
from user u
left join user_perm up
	on up.user_id = u.id
left join perm p
	on up.perm_id = p.id
where u.username='zhangsan'

对应的ER图：

若想要知道不同的用户有哪些权限，在UserDTO中权限大量冗余重复存储，因此引出了RBAC权限模型。

2. RBAC

RBAC：Role Based Access Control，基于角色的访问控制。

例如需要执行如下sql：

select u.*, p.*
from user u
left join user_role ur
	on ur.user_id = u.id
left join role_perm rp
	on rp.role_id = ur.role_id
left join perm p
	on p.id = rp.perm_id
where u.username = zhangsan