代码人生

初学后端，如何做好表结构设计？

前言

最近有不少前端和测试转Go的朋友在私信我：如何做好表结构设计？

大家关心的问题阳哥必须整理出来，希望对大家有帮助。

先说结论

这篇文章介绍了设计数据库表结构应该考虑的4个方面，还有优雅设计的6个原则，举了一个例子分享了我的设计思路，为了提高性能我们也要从多方面考虑缓存问题。

收获最大的还是和大家的交流讨论，总结一下：

首先，一定要先搞清楚业务需求。比如我的例子中，如果不需要灵活设置，完全可以写到配置文件中，并不需要单独设计外键。主表中直接保存各种筛选标签名称（注意维护的问题，要考虑到数据一致性）
数据库表结构设计一定考虑数据量和并发量，我的例子中如果数据量小，可以适当做冗余设计，降低业务复杂度。

4个方面

设计数据库表结构需要考虑到以下4个方面：

数据库范式：通常情况下，我们希望表的数据符合某种范式，这可以保证数据的完整性和一致性。例如，第一范式要求表的每个属性都是原子性的，第二范式要求每个非主键属性完全依赖于主键，第三范式要求每个非主键属性不依赖于其他非主键属性。
实体关系模型（ER模型）：我们需要先根据实际情况画出实体关系模型，然后再将其转化为数据库表结构。实体关系模型通常包括实体、属性、关系等要素，我们需要将它们转化为表的形式。
数据库性能：我们需要考虑到数据库的性能问题，包括表的大小、索引的使用、查询语句的优化等。
数据库安全：我们需要考虑到数据库的安全问题，包括表的权限、用户角色的设置等。

设计原则

在设计数据库表结构时，可以参考以下几个优雅的设计原则：

简单明了：表结构应该简单明了，避免过度复杂化。
一致性：表结构应该保持一致性，例如命名规范、数据类型等。
规范化：尽可能将表规范化，避免数据冗余和不一致性。
性能：表结构应该考虑到性能问题，例如使用适当的索引、避免全表扫描等。
安全：表结构应该考虑到安全问题，例如合理设置权限、避免SQL注入等。
扩展性：表结构应该具有一定的扩展性，例如预留字段、可扩展的关系等。

最后，需要提醒的是，优雅的数据库表结构需要在实践中不断迭代和优化，不断满足实际需求和新的挑战。

下面举个示例让大家更好的理解如何设计表结构，如何引入内存，有哪些优化思路：

问题描述

如上图所示，红框中的视频筛选标签，应该怎么设计数据库表结构？除了前台筛选，还想支持在管理后台灵活配置这些筛选标签。

这是一个很好的应用场景，大家可以先自己想一下。不要着急看我的方案。

需求分析

可以根据红框的标签筛选视频
其中综合标签比较特殊，和类型、地区、年份、演员等不一样

综合是根据业务逻辑取值，并不需要入库
类型、地区、年份、演员等需要入库

设计表结构时要考虑到：

方便获取标签信息，方便把标签信息缓存处理
方便根据标签筛选视频，方便我们写后续的业务逻辑

设计思路

综合标签可以写到配置文件中（或者写在前端），这些信息不需要灵活配置，所以不需要保存到数据库中
类型、地区、年份、演员都设计单独的表
视频表中设计标签表的外键，方便视频列表筛选取值
标签信息写入缓存，提高接口响应速度
类型、地区、年份、演员表也要支持对数据排序，方便后期管理维护

表结构设计

视频表

字段	注释
id	视频主键id
type_id	类型表外键id
area_id	地区表外键id
year_id	年份外键id
actor_id	演员外键id

其他和视频直接相关的字段（比如名称）我就省略不写了

类型表

字段	注释
id	类型主键id
name	类型名称
sort	排序字段

地区表

字段	注释
id	类型主键id
name	类型名称
sort	排序字段

年份表

字段	注释
id	类型主键id
name	类型名称
sort	排序字段

原以为年份字段不需要排序，要么是年份正序排列，要么是年份倒序排列，所以不需要sort字段。

仔细看了看需求，还有“10年代”还是需要灵活配置的呀~

演员表

字段	注释
id	类型主键id
name	类型名称
sort	排序字段

表结构设计完了，别忘了缓存

缓存策略

首先这些不会频繁更新的筛选条件建议使用缓存：

比较常用的就是redis缓存
再进阶一点，如果你使用docker，可以把这些配置信息写入docker容器所在物理机的内存中，而不用请求其他节点的redis，进一步降低网络传输带来的耗时损耗
筛选条件这类配置信息，客户端和服务端可以约定一个更新缓存的机制，客户端直接缓存配置信息，进一步提高性能

列表数据自动缓存

目前很多框架都是支持自动缓存处理的，比如goframe和go-zero

goframe

可以使用ORM链式操作-查询缓存

示例代码：

package main


import (

	"time"


	"github.com/gogf/gf/v2/database/gdb"

	"github.com/gogf/gf/v2/frame/g"

	"github.com/gogf/gf/v2/os/gctx"

)


func main() {

	var (

		db  = g.DB()

		ctx = gctx.New()

	)


	// 开启调试模式，以便于记录所有执行的SQL

	db.SetDebug(true)


	// 写入测试数据

	_, err := g.Model("user").Ctx(ctx).Data(g.Map{

		"name": "xxx",

		"site": "https://xxx.org",

	}).Insert()


	// 执行2次查询并将查询结果缓存1小时，并可执行缓存名称(可选)

	for i := 0; i < 2; i++ {

		r, _ := g.Model("user").Ctx(ctx).Cache(gdb.CacheOption{

			Duration: time.Hour,

			Name:     "vip-user",

			Force:    false,

		}).Where("uid", 1).One()

		g.Log().Debug(ctx, r.Map())

	}


	// 执行更新操作，并清理指定名称的查询缓存

	_, err = g.Model("user").Ctx(ctx).Cache(gdb.CacheOption{

		Duration: -1,

		Name:     "vip-user",

		Force:    false,

	}).Data(gdb.Map{"name": "smith"}).Where("uid", 1).Update()

	if err != nil {

		g.Log().Fatal(ctx, err)

	}


	// 再次执行查询，启用查询缓存特性

	r, _ := g.Model("user").Ctx(ctx).Cache(gdb.CacheOption{

		Duration: time.Hour,

		Name:     "vip-user",

		Force:    false,

	}).Where("uid", 1).One()

	g.Log().Debug(ctx, r.Map())

}

复制代码

go-zero

DB缓存机制

go-zero缓存设计之持久层缓存

官方都做了详细的介绍，不作为本文的重点。

讨论

我的方案也在我的技术交流群里引起了大家的讨论，也和大家分享一下：

Q1 冗余设计和一致性问题

提问：一个表里做了这么多外键，如果我要查各自的名称，势必要关联4张表，对于这种存在多外键关联的这种表，要不要做冗余呢(直接在主表里冗余各自的名称字段)？要是保证一致性的话，就势必会影响性能，如果做冗余的话，又无法保证一致性

回答：

你看文章的上下文应该知道，文章想解决的是视频列表筛选问题。

你提到的这个场景是在视频详情信息中，如果要展示这些外键的名称怎么设计更好。

我的建议是这样的：

根据需求可以做适当冗余，比如你的主表信息量不大，配置信息修改后同步修改冗余字段的成本并不高。
或者像我文章中写的不做冗余设计，但是会把外键信息缓存，业务查询从缓存中取值。
或者将视频详情的查询结果整体进行缓存

还是看具体需求，如果这些筛选信息不变化或者不需要手工管理，甚至不需要设计表，直接写死在代码的配置文件中也可以。进一步降低DB压力，提高性能。

Q2 why设计外键？

提问：为什么要设计外键关联？直接写到视频表中不就行了？这么设计的意义在哪里？

回答：

关键问题是想解决管理后台灵活配置
如果没有这个需求，我们可以直接把筛选条件以配置文件的方式写死在程序中，降低复杂度。
站在我的角度：这个功能的筛选条件变化并不会很大，所以很懂你的意思。也建议像我2.中的方案去做，去和产品经理拉扯喽~

总结