表结构设计

在数据库使用过程中，开发者会创建各种表，来进行数据库操作，本文旨在帮助开发者规范数据库中表的结构设计。

三大范式

在推荐表结构设计之前，先来了解一个数据库概念，数据库设计三大范式。为了建立出冗余更小、结构更合理的数据库，在进行数据库创建的时候要遵循一定的原则，在关系型数据库中这种规范被称为范式。下面简单介绍下三大范式。

第一范式（字段原子值）

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该表满足了第一范式。

示例：学生表，具体字段的定义如下。

从表中明显可以看出，联系方式值有邮箱和手机号码，因此不是原子值，不满足第一范式。这里将联系方式拆分为邮箱和手机号码，则每一列都是原子值，从而满足了第一范式。修改后的学生表的字段定义如下。

第二范式（无部分依赖）

第二范式在第一范式的基础之上对字段的定义要求更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

示例：学生教师表，具体字段定义如下：

这张表中的主键为（sno，tno），很明显 每个字段都是原子值不可再分，满足第一范式，但 sname 依赖于 sno，tname 依赖于 tno，不满足第二范式，因此需要对表进行拆分。将 1 张表拆分为 3 张表具体拆分后的字段如下。

学生表：

教师表：

学生教师关系表：

上面3张表字段皆为原子值，且不存在部分依赖，满足第二范式。

第三范式（无传递依赖）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

示例：学生教室表，具体字段定义如下

从表中明显看出表中各字段皆为原子值，且不存在部分依赖，满足第一、第二范式，但 cname 依赖于 cno，cno 依赖于 tno，存在传递依赖，不满足第三范式。将这张表进行拆分为 2 张表，具体如下：

班级表：

学生班级关系表：

经过拆分后，表中不存在传递依赖，满足了第三范式。

普通表结构设计规范

• 表结构设计不应该简单遵循三大范式，应该以业务性能为指导，适当进行数据冗余存储，以减少表的关联从而提升业务性能。冗余字段应遵循：

  • 不是频繁修改的字段。

  • 不是 varchar 超长字段。

• 建表时应该设定主键。

  • 建议使用业务字段做主键或做联合主键，不建议使用自增列做主键。

  • OceanBase 数据库的表存储模型为索引聚集表模型(IOT)，如果用户未指定主键，系统会自动生成一个隐藏主键。

• 表必备两字段：gmt_create，gmt_modified。

  说明

  gmt_create，gmt_modified 的类型选择 DATE （精确到秒）或 TIMESTAMP WITH TIME ZONE（精确到微秒，且带当前时区信息），可以使用 sysdate 或 systimestamp 函数。

• 表、字段需要有 COMMENT 属性。

• 表中所有字段推荐是 NOT NULL 属性，业务可以根据需要定义 DEFAULT 值。

• 多表中的相同列，必须保证列定义一致。

• 进行 join 的关联字段，数据类型保证一致，避免隐式转换。

• 不推荐使用复杂的数据类型，如 blob 或者 json。

• 表达是与否概念的字段，推荐数据类型是 unsigned tinyint（1 表示是，0 表示否），值的内容要统一。 示例：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。

• 如果修改字段含义或对字段表示的状态追加时，建议及时更新字段注释。