MySQL数据库设计和规范化

目录

1 数据库设计

1.1 函数依赖

1.2 异常

1.3 范式

1.4 范式优化流程示例

1.4.1 优化到满足1NF

1.4.2 优化到满足2NF

1.4.3 优化到满足3NF

2 数据库规范

2.1 数据库命名规范

2.2 数据库设计规范

2.3 SQL 语句书写规范

1 数据库设计

1.1 函数依赖

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

如果 {A1，A2，... ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为候选键。可以从候选键中随机选出一个作为主键。

对于 A->B，如果能找到 A 的真子集 A'，使得 A'-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。

对于 A->B，B->C，则 A->C 是一个传递函数依赖。

1.2 异常

一个简单的学生选课表如下：

不符合范式的关系，会产生很多异常，主要有以下四种异常：

1.冗余异常（Redundancy Anomaly）

在这个例子中，张三的名字多次出现，造成了数据冗余。

2.更新异常（Update Anomaly）

假设张三教授的名字改了（比如改名为张山）。在上面的表中，你需要更新所有与张三相关的行。如果忘记更新某一行，将导致数据不一致。

3.插入异常（Insert Anomaly）

假设一个新学生注册但还未选课。由于学生ID、课程ID和教师名称都是该表的组成部分，如果没有课程和教师信息，你将无法插入这个新学生的记录。

4.删除异常（Delete Anomaly）

如果一个课程被取消，你可能会删除所有选这门课的学生的记录。但是，如果这样做，与这些学生有关的其他课程信息也会被删除，导致大量信息丢失。

1.3 范式

在关系型数据库设计中，范式是确保数据结构化的逻辑规则。遵循范式的目标是减少数据冗余、避免数据异常，同时提高数据完整性。下面是常见的范式和它们的主要规则：

1.第一范式 (1NF):

• 每个列都必须有唯一的值。
• 每个列的值必须是原子的，不可再分。
• 每个记录必须能由一个主键唯一标识。

2.第二范式 (2NF):

• 完全满足1NF。
• 去除部分依赖，即确保每个非主键字段都完全函数依赖于主键。如果是复合主键，则要确保没有字段只依赖于复合主键的部分。

3.第三范式 (3NF):

• 完全满足2NF。
• 没有传递依赖，即非主键字段之间不应该存在依赖关系。 

1.4 范式优化流程示例

假设我们有一个表，其中包含以下数据：

学生表

此表违反了以下范式：

• 1NF：因为课程、教师和教师邮箱的列包含了多个值。
• 2NF：因为教师和他们的邮箱是根据课程决定的，而不是直接依赖于学生名。
• 3NF：因为教师的邮箱是依赖于教师的，而不是直接依赖于学生或课程。

1.4.1 优化到满足1NF

将每个多值属性拆分为单独的行：

学生课程表

1.4.2 优化到满足2NF

将与课程和教师相关的信息从学生表中分离：

学生表

课程教师表

学生课程关联表

1.4.3 优化到满足3NF

将教师和他们的电子邮件分开，因为电子邮件是直接依赖于教师的：

教师表

课程表

2 数据库规范

2.1 数据库命名规范

1. 可读性和一致性: 命名应明确、简单且一致。避免使用不明确的缩写或命名。
2. 大小写: 通常，数据库对象（如表名、列名等）应该使用小写，并用下划线分隔单词（例如，student_info）。
3. 前缀和后缀: 使用前缀或后缀来区分数据库对象的类型（例如，tbl_students 表示学生表，tbl_students_20220707_bak表示学生表在20220707的备份，tbl_students_tmp表示存储学生表的一些临时记录，uk_tbl_students表是这是学生表的唯一索引）。
4. 复数与单数: 表名通常使用单数（例如，student），除非单个记录表示多个实体（例如，orders）。
5. 限制长度：通常应该对库名、表名、字段名限制长度，防止数据库迁移出现问题。

2.2 数据库设计规范

1. 主键和外键: 每个表应有一个明确定义的主键，建议该主键没有业务含义。外键可有可无，如果在小数据量且不涉及到分布式的情况下可以有，如果在分布式数据库或者数据量非常大的情况下，使用外键可能不是好的选择，此时可以在应用层维护数据的完整性）。
2. 范式: 设计应遵循数据库范式，至少达到第三范式，以减少数据冗余和维护问题。
3. 索引: 根据查询需求适当创建索引，但不应过度使用，以免影响更新操作的性能。详细的索引设计参考上一篇文档。
4. 数据类型: 选择最适合数据的数据类型。例如，使用 INT 存储整数，使用 VARCHAR 存储可变长度的字符串。在多种数据类型都可以使用时，选择字节数最少的数据类型，比如日期可以存储为varchar/char , 也可以存储为date，但date所占的字节数更少，性能更好。
5. 注释：表和字段必须有 comments，注释信息必须保证完整、明确和准确；表和字段含义发生变更时，comments必须做同步修改。
6. 除临时表以外，其他表必须有 CREATED_DATE（记录创建日期，中文注释是“创建时间”）、MODIFIED_DATE（记录修改日期，中文注释是“修改时间”）。

• CREATED_DATE 与 MODIFIED_DATE 字段类型均为 DATETIME。
• 在插入数据时，必须将 CREATED_DATE 与 MODIFIED_DATE 字段写入为CURRENT_TIMESTAMP。
• 在更新数据时，必须将 MODIFIED_DATE 字段修改为 CURRENT_TIMESTAMP。

7.字符集： 若无特殊需求，字符集使用 utf8mb4 ，排序规则使用utf8mb4_bin。

8.存储引擎：若无特殊需求，使用 InnoDB 存储引擎。

2.3 SQL 语句书写规范

1. 大小写: SQL 关键字通常大写（例如，SELECT、FROM、WHERE）以增加可读性。
2. 缩进和对齐: 使用缩进和新行来使复杂的 SQL 语句更易读。例如：

  SELECT student_name,
          course_name
   FROM   students
   JOIN   courses ON students.course_id = courses.id
   WHERE  student_name LIKE 'A%'

3.别名: 使用别名来简化复杂的表或列名，但别名应简洁并具有描述性。

4.注释: 使用注释来解释复杂的查询逻辑或特殊的操作。

5.子查询与连接: 在可行的情况下，优先使用连接（JOIN）而非子查询。

6.防止大事务: UPDATE、DELETE 语句一定要有明确的 WHERE 条件，对于大表，该条件上一定要有索引。

7.显式的指明字段名称: 查询指定字段名可以提高性能，插入指定字段名可以防止出错。