如何做好表结构设计？

前言

最近有不少前端和测试转Go的朋友在​​交流群​​里聊：如何做表结构设计？

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

4个方面

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

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

设计原则

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

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

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

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

问题描述

如上图所示，红框中的视频筛选标签，应该怎么设计数据库表结构？

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

需求分析

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

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

3. 设计表结构时要考虑到：

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

设计思路

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

表结构设计

视频表

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

类型表

地区表

年份表

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

演员表

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

缓存策略

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

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

列表数据自动缓存

目前很多框架都是支持自动缓存处理的，比如goframe和go-zero，官方文档都做了详细的介绍，不作为本文的重点。

goframe

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

官方示例：

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缓存机制[2]

go-zero缓存设计之持久层缓存[3]

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

总结

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

本文抛砖引玉，欢迎大家留言交流。

相关资料

[1]ORM链式操作-查询缓存: https://goframe.org/pages/viewpage.action?pageId=1114346

[2]DB缓存机制: https://go-zero.dev/cn/docs/blog/cache/cache

[3]go-zero缓存设计之持久层缓存: https://go-zero.dev/cn/docs/blog/cache/redis-cache

本文转载自微信公众号「 程序员升级打怪之旅」，作者「王中阳Go」