讲到数据库设计，就需要先说一下数据库设计的三大范式（Normal Form）。
三大范式
第一范式(1NF)：列属性的原子性
- 确保表中列属性的原子性；
- 数据库表中的每一列，都不可被继续拆分为其他列；
- 最基本范式，也是绝大多数数情况下会遵从的范式；
反示例：
| 会员编号 | 会员名称 | 手机号 | 邮箱 | 地址 | 创建时间 |
|---|---|---|---|---|---|
| C00001 | 张三 | 13800000001 | zhangsan@test.com | 上海市浦东区博云路2号 | 2020-01-20 |
| C00002 | 李四 | 13800000002 | lisi@test.com | 上海市浦东区博云路1号 | 2020-01-22 |
正示例：
| 会员编号 | 会员名称 | 手机号 | 邮箱 | 省 | 市 | 区 | 街道 | 创建时间 |
|---|---|---|---|---|---|---|---|---|
| C00001 | 张三 | 13800000001 | zhangsan@test.com | 上海 | 上海市 | 浦东区 | 博云路2号 | 2020-01-20 |
| C00002 | 李四 | 13800000002 | lisi@test.com | 浙江 | 杭州市 | 西湖区 | 余杭塘路866号 | 2020-01-22 |
第二范式（2NF）：行记录的唯一性
- 确保表中的每一行都具有实体的唯一性
- 联合主键情况下，每一列都和主键相关，而不是和主键的某一部分相关；
- 不可以把多种数据保存在同一张数据表中；
反示例：会员编号+账户编号作为联合主键
| 会员编号 | 会员名称 | 手机号 | 邮箱 | 账户编号 | 账户余额 | 创建时间 |
|---|---|---|---|---|---|---|
| C00001 | 张三 | 13800000001 | zhangsan@test.com | ACC001 | 200.00 | 2020-01-20 |
| C00001 | 张三 | 13800000001 | zhangsan@test.com | ACC002 | 300.00 | 2020-01-20 |
| C00002 | 李四 | 13800000002 | lisi@test.com | ACC003 | 100.00 | 2020-01-22 |
正示例：拆分为会员表和账户表，会员编号作为账户表外键进行关联
| 会员编号 | 会员名称 | 手机号 | 邮箱 | 创建时间 |
|---|---|---|---|---|
| C00001 | 张三 | 13800000001 | zhangsan@test.com | 2020-01-20 |
| C00002 | 李四 | 13800000002 | lisi@test.com | 2020-01-22 |
| 账户编号 | 会员编号 | 账户类型 | 账户余额 | 创建时间 |
|---|---|---|---|---|
| ACC001 | C00001 | 现金 | 200.00 | 2020-01-20 |
| ACC002 | C00001 | 红包 | 300.00 | 2020-01-20 |
| ACC003 | C00002 | 现金 | 100.00 | 2020-01-22 |
第三范式(3NF)：字段的冗余性
- 确保表中的每一列都与主键直接关联，而不是间接关联；
- 即不存在传递依赖情况发生；
反示例：存在依赖传递关系 会员编号 - 所属团队 - 团队名称 - 团队主管，更改团队名称或主管时，所有记录都要更新
| 会员编号 | 会员名称 | 手机号 | 邮箱 | 所属团队 | 团队名称 | 团队主管 | 创建时间 |
|---|---|---|---|---|---|---|---|
| C00001 | 张三 | 13800000001 | zhangsan@test.com | T01 | 云腾 | 小红 | 2020-01-20 |
| C00002 | 李四 | 13800000002 | lisi@test.com | T02 | 天翼 | 小明 | 2020-01-22 |
| C00003 | 王五 | 13800000003 | wangwu@test.com | T02 | 天翼 | 小明 | 2020-01-24 |
| C00004 | 赵六 | 13800000004 | zhaoliu@test.com | T01 | 云腾 | 小红 | 2020-01-24 |
正示例：
| 会员编号 | 会员名称 | 手机号 | 邮箱 | 所属团队 | 创建时间 |
|---|---|---|---|---|---|
| C00001 | 张三 | 13800000001 | zhangsan@test.com | T01 | 2020-01-20 |
| C00002 | 李四 | 13800000002 | lisi@test.com | T02 | 2020-01-22 |
| C00003 | 王五 | 13800000003 | wangwu@test.com | T02 | 2020-01-24 |
| C00004 | 赵六 | 13800000004 | zhaoliu@test.com | T01 | 2020-01-24 |
| 团队编号 | 团队名称 | 团队主管 | 创建时间 |
|---|---|---|---|
| T01 | 云腾 | 小红 | 2020-01-20 |
| T02 | 天翼 | 小明 | 2020-01-22 |
遵从范式的优点
- 重复数据量少，无冗余数据；
- 更新操作更快；
- 范式化后表体积更小；
遵从范式的缺点
- 查询时经常需要进行多表关联查询，导致性能降低；
- 更难进行索引的优化；
数据库设计的一些建议
1. 合理使用冗余
- 对于更新频率不高，但查询频率过高的字段，做冗余数据处理；
- 有效的减少关联查询所带来的性能问题；
| 订单编号 | 订单金额 | 订单状态 | 会员编号 | 会员名称 | 创建时间 |
|---|---|---|---|---|---|
| ORD00001 | 300.00 | 成功 | C00001 | 张三 | 2020-01-20 |
| ORD00002 | 500.00 | 成功 | C00002 | 李四 | 2020-01-22 |
2. 超长字段拆分
- 存储超长字段时，将字段从主表中拆出，避免因超长字段导致的查询效率问题；
- 拆出的字段，通过ID与主表内容进行关联，通过纵向逻辑关系进行关联查询；
- 纵向逻辑关系，可提升数据的易读性和维护性；
3. 不使用外键与级联（阿里巴巴开发手册）
- 一切外键概念，通过应用层处理，不依赖外键关系；
- 使用外键可能导致业务变动时，增加额外的修改数据库成本；
- 外键对插入和更新有性能影响；
4. 合适的数据类型(MySQL)
- 数据量相同时，使用更小的数据类型所占用的空间更少；
- 检索同样的数据所产生的IO消耗越低；
- 运算和排序，越简单的数据类型操作性能越高；
详细内容点击：MySQL数据类型
| 类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
|---|---|---|---|---|
| TINYINT | 1 bytes | (-128，127) | (0，255) | 小整数值 |
| INT或INTEGER | 4 bytes | (-2^31-1 ，2^31-1) | (0，2^32-1) | 大整数值 |
| BIGINT | 8 bytes | (-2^63 ，2^63) | (0，2^64 - 1) | 极大整数值 |
| DECIMAL | 对DECIMAL(M,D) ， 如果M>D，为M+2否则为D+2 |
依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
| 类型 | 大小 | 范围(有符号) | 格式 | 用途 |
|---|---|---|---|---|
| DATETIME | 8 bytes | 1000-01-01 00:00:00 / 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 bytes | 1970-01-01 00:00:00/2038 ~ 结束 北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 |
YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
5. 表中应建立唯一标识
- 区分数据库主键和业务主键；
- 合理使用ID作为自增长主键，有利于更新、删除、查找时的准确性；
- 主键数据类型根据业务需要，选用最小数据类型，有利于提高性能；
6. 表中不应有重复意义的字段
- 迭代过程中，不应重复建立相同意义字段；
- 详尽的阅读数据库设计，是每个开发人员对系统了解的基础；
7. 命名规范（阿里巴巴开发手册）
- 表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（ 1表示是，0表示否）；
- 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字；
- 表名不使用复数名词，应该仅仅表示表里面的实体内容；
- 禁用保留字，如desc、range、match、delayed等，请参考MySQL官方保留字；
- 主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名；
- 小数类型为decimal，禁止使用float和double；超过decimal的范围，拆成整数和小数分开存储；
- 表必备三字段：id、gmt_create(date_time)、 gmt_modified(date_time)；
