数据库表结构设计一点心得和经验

数据库表结构设计一点心得和经验
花呗借呗前端团队 @ 蚂蚁集团

一个好的表结构设计能减少不小开发量,也能提升部分扩展性,只梳理下自己日常设计表结构的时候一点点心得,经验

1.0 基本要求

  • 命名规范,命名可读,同一业务模块用相同前缀
  • 做逻辑删除,不做物理删除
  • 不搞主外键关联,在程序业务逻辑中维护

2.0 不需要严格遵守 3NF,通过业务字段冗余来减少表关联

通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,就必须通俗地理解三个范式(通俗地理解是够用的理解,并不是最科学最准确的理解):

  • 第一范式:1NF 是对属性的原子性约束,要求属性具有原子性,不可再分解
  • 第二范式:2NF 是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性
  • 第三范式:3NF 是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余

没有冗余的数据库设计可以做到。但是,没有冗余的设计未必是最好的设计

基本表及其字段之间的关系, 应尽量满足第三范式。但是,满足第三范式的数据库设计,往往不是最好的设计。为了提高数据库的运行效率,常常需要降低范式标准:适当增加冗余,达到以空间换时间的目的

比如上面这张存放商品的基本表。“金额”这个字段的存在,表明该表的设计不满足第三范式,因为“金额”可以由“单价”乘以“数量”得到,说明“金额”是冗余字段。但是,增加“金额”这个冗余字段,可以提高查询统计的速度,这就是以空间换时间的作法

3.0 通用公共字段

  • deleted_at 默认是 null(大部分orm 默认查询 null)
  • sort 非必需 默认 是 1,页面展示经常用到
  • version 非必需,默认是 1,乐观锁需要
  • created_at 创建时间
  • updated_at 更新时间
  • created_by 创建人
  • updated_by 更新人
  • remark 非必需 备注

4.0 一张表表达多维度基础信息

以省市区这种树结构的级联信息为例,我们可以设计 3 张表,然后分别关联,但是我们也可以设计一张表,会更加简洁,3 张表,设计如下:

// 省表
CREATE TABLE `province` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(6) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;

// 市表
CREATE TABLE `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(6) NOT NULL,
  `name` varchar(20) NOT NULL,
  `provincecode` varchar(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=343 DEFAULT CHARSET=utf8;
// 县,区表
CREATE TABLE `area` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(6) NOT NULL,
  `name` varchar(20) NOT NULL,
  `citycode` varchar(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3145 DEFAULT CHARSET=utf8;
复制代码

这样是没问题的,换成一张表设计,如下:

CREATE TABLE `region` (
  `region_id` varchar(10) NOT NULL COMMENT '地区主键编号',
  `region_name` varchar(50) NOT NULL COMMENT '地区名称',
  `region_short_name` varchar(10) DEFAULT NULL COMMENT '地区缩写',
  `region_code` varchar(20) DEFAULT NULL COMMENT '行政地区编号',
  `region_parent_id` varchar(10) DEFAULT NULL COMMENT '地区父id',
  `region_level` int(2) DEFAULT NULL COMMENT '地区级别 1-省、自治区、直辖市 2-地级市、地区、自治州、盟 3-市辖区、县级市、县',
  PRIMARY KEY (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='地区表';
复制代码

通过 region_level这个字段来增加了数据的一个维度信息,就表达一条数据的信息,然后通过 region_parent_id 来表达关联关系,这样一张表就能表达出来了,这样做查询的时候,一张表就把所有信息查出来了,方便不少

这种设计用在基础数据设计上是非常方便的,这种表结构变化比较少,新增,更改不频繁的表结构上面,比如,分类信息,部门信息

不能滥用,比如学生,班级,就不适合,比如洛可场景,楼层,展位,操作频繁,而且也不符合三范式

5.0 1:n 设计变通

1对多关系,日常用的也多,例如: 班级和学生,部门和员工,客户和订单,分类和商品,一般建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

网上找的图

但是,有时候我们会遇到复杂一点的一对多关系,我们已营销领域的常用的实验为例,假设我们需要对页面,不同版本组件,做实验,按照上面的设计,表结构设计如下:

表结构设计如下: image.png

这样我们需要在页面表,组件表新增 实验id字段,然后在对应 pageService,componentService 新增对应配置方法,随着时间的推移,大家发现做实验非常好,干啥都做个实验,比如内容,投放计划,按照上面设计,我们就需要在内容表,投放计划表都加上实验id字段,写对应的配置方法,非常繁琐变,也不灵活

如果我们新增一张实验配置表,把 1:n 改造成 1: 1,改造成下面这样:

image.png

这里我们新增一张实验配置表,通过 targer_id,target_type,就把实验配置信息独立出来了,不用改动原始的页面表和组件表,同时在领域设计上来说,我们是把实验配置信息单独在自己的领域里面,这样在代码层面,也值需要关注实验相关的service,接口了

这里也需要注意,并不是任何时候都需要把1:n, 拆成中间表 1:1,首先需要预判下,你当前做的事情是否和多张表有关联,有改动是否是很多地方可能用到的,是否归属在一个业务模型下面,比如页面和组件,是个 1:n 的关系,在一个业务模型下,那就没必要搞个页面组件中间表,直接在组件表加一个页面 id 字段就好

6.0 如果 2 张表之间存在 n:n 的关系,应改消除这种关系

消除的办法是,在两者之间增加第三张表。这样,原来 n:n 的关系,现在变为两个 1:n 的关系。要将 原来两个表的的属性合理地分配 到第 3 张表。一般来讲,数据库设计工具不能识别多对多的关系,但能处理多对多的关系

比如在“图书馆信息系统”中,“图书”对应一张表,“读者”也对应一张表。这两张表之间的关系,是一个典型的多对多关系,一本图书在不同时间可以被多个读者借阅,一个读者又可以借多本图书,为此,要在二者之间增加第三个实体,该实体取名为“借还书”,它的属性为:借还时间、借还标志(0 表示借书,1 表示还书),另外,它还应该有两个外键(“图书”的主键,“读者”的主键),使它能与“图书”和“读者”连接

7.0 n:n 消除后的中间表适当冗于字段

原来两个表的的属性合理地分配 上这段加粗 大部分时候 大家都会把 n:n 拆开,但是并不是拆出来的中间表就 3 个字段,以上面图书,读者为例,假设中间表就 3个字段

CREATE TABLE `book_reader` (
  `id` varchar(10) NOT NULL COMMENT '主键编号',
  `book_id` varchar(50) NOT NULL COMMENT 'book 表 主键',
  `reader_id` varchar(10) DEFAULT NULL COMMENT '读者表主键'
  PRIMARY KEY (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='图书,读者中间表';
复制代码

这张表特别符合三范式的,但是你做查询的时候每次都得关联 3 张表查询,其实可以适当冗于部分信息,比如书名,书的 isbn 编号,这样的好处是,查中间表就能获取部分信息,但是需要注意的是合理分配, 不要把变动频繁的字段往中间表放,更新数据的时候,记得更新中间表

8.0 最后

我们前端大部分时候不会遇到很复杂的数据库操作和表结构设计,基本上理解了 RBAC 用户权限管理数据库设计,就能满足日常的各类设计,RBAPC 模型包含 用户,角色,权限,菜单,包含 1:n,n:n 关系,非常值得好好学习下

参考资料:

分类:
前端
标签:
收藏成功!
已添加到「」, 点击更改