数据库 PostgreSQL 构建通用标签系统

lanzhiheng · 2019年01月11日 · 最后由 emmausa 回复于 2023年11月18日 · 10505 次阅读
本帖已被管理员设置为精华贴

PostgreSQL

对资源打标签在建站过程中是很常见的需求,有些时候我们需要给文章打标签,有些时候我们需要给用户打标签。实现一个标签系统其实并不难,其本质就是一个多对多的关系 - 我可以对同一篇博客打多个标签,同时也可以把一个标签打到不同的博客身上。这篇文章主要通过分析标签系统的原理,并用 PostgreSQL 来实现一个能够为多种资源打标签的标签系统。

1. 单一资源标签系统

先从单一资源开始,所谓单一资源便是,我们只给一种数据资源打标签。假设我们需要给博客文章打标签,那么我们需要构建以下几个表:

  1. 文章表posts,用于存储文章的基本信息。
  2. 标签表tags,用于存储标签的基本信息。
  3. 标签 - 文章表tags_posts,存储双方的 id 并形成多对多的关系。

表设计图大概是

Model Design for Simple Tag System

先进入数据库引擎并创建对应的数据库

postgres=# create database blog;
CREATE DATABASE

postgres=# \c blog;
blog=#

通过 SQL 语句创建上面所提到的数据表

CREATE TABLE posts (
    id              SERIAL,
    body            text,
    title           varchar(80)
);

CREATE TABLE tags (
    id              SERIAL,
    name            varchar(80)
);

CREATE TABLE tags_posts (
    id              SERIAL,
    tag_id          integer,
    post_id         integer
);

每个表都只是包含了该资源最基础的字段,到这一步为止其实已经构建好了一个最简单的标签系统了。接下来则是填充数据,我的策略是添加两篇文章,五个标签,给标题为Ruby的文章打上language标签,给标题为Docker的文章打上container的标签,两篇文章都要打上tech标签

-- 填充文章数据
INSERT INTO posts (body, title) VALUES ('Hello Ruby', 'Ruby');
INSERT INTO posts (body, title) VALUES ('Hello Docker', 'Docker');

-- 填充标签数据
INSERT INTO tags (name) VALUES ('language');
INSERT INTO tags (name) VALUES ('container');
INSERT INTO tags (name) VALUES ('tech');

-- 为相关资源打上标签
INSERT INTO tags_posts (tag_id, post_id) VALUES ((SELECT id FROM tags WHERE name = 'container'), (SELECT id FROM posts WHERE title = 'Docker'));
INSERT INTO tags_posts (tag_id, post_id) VALUES ((SELECT id FROM tags WHERE name = 'tech'), (SELECT id FROM posts WHERE title = 'Docker'));
INSERT INTO tags_posts (tag_id, post_id) VALUES ((SELECT id FROM tags WHERE name = 'tech'), (SELECT id FROM posts WHERE title = 'Ruby'));
INSERT INTO tags_posts (tag_id, post_id) VALUES ((SELECT id FROM tags WHERE name = 'language'), (SELECT id FROM posts WHERE title = 'Ruby'));

然后分别查询两篇文章都被打上了什么标签。

blog=# SELECT tags.name FROM tags, posts, tags_posts WHERE tags.id = tags_posts.tag_id AND posts.id = tags_posts.post_id AND posts.title = 'Ruby';
   name
----------
 language
 tech
(2 rows)

blog=# SELECT tags.name FROM tags, posts, tags_posts WHERE tags.id = tags_posts.tag_id AND posts.id = tags_posts.post_id AND posts.title = 'Docker';
   name
-----------
 container
 tech
(2 rows)

两篇文章都被打上期望的标签了,相关的语句有点长,一般生产线上不会这样直接操作数据库。各种编程语言的社区一般都对这种数据库操作进行了封装,这为编写业务代码带来了不少的便利性。

2. 为多种资源打标签

如果只需要对一个数据表打标签的话,依照上面的逻辑来设计表已经足够了。但是现实世界往往没那么简单,假设除了要给博客文章打标签之外,还需要给用户表打标签呢?我们需要把表设计得更灵活一些。如果继续用tags表来存标签数据,为了给用户打标签还得另外建一个名为tags_users的表来存储标签与用户数据之间的关系。

但更好的做法应该是采用名为多态的设计。创建关联表taggings,这个关联表除了会存储关联的两个 id 之外,还会存储被打上标签的资源类型,我们根据类型来区分被打标签的到底是哪种资源,这会在每条记录上多存了类型数据,不过好处就是可以少建表,所有的标签关系都通过一个表来存储。

Ruby 比较流行的标签系统ActsAsTaggableOn 就沿用了这个设计,不过它的类型字段直接存的是对应资源的类名,或许是为了更方便编程吧,数据大概如下:

naive_development=# select id, tag_id, taggable_type, taggable_id from taggings;
 id | tag_id |    taggable_type     | taggable_id
----+--------+----------------------+-------------
  1 |      1 | Refinery::Blog::Post |           1
  2 |      2 | Refinery::Blog::Post |           1
  3 |      3 | Refinery::Blog::Post |           1

先通过taggable_type获取类名,然后再利用taggable_id的数据就能准确获取相关的资源了。

a. 修改原表

表设计图大概如下

Model Design for multi

这里我不重新建表了,而直接修改原有的表,并进行数据迁移

  1. 增加type字段用于存储资源类型。
  2. 把原来的数据表改名为更通用的名字taggings
  3. 把原来的post_id字段改成更通用的名字taggable_id
  4. 给原有的资源填充数据,type字段统一填数据post
ALTER TABLE tags_posts ADD COLUMN type varchar(80);
ALTER TABLE tags_posts RENAME TO taggings;
ALTER TABLE taggings RENAME COLUMN post_id TO taggable_id;
UPDATE taggings SET type='post';

b. 添加用户

在给用户打标签之前先创建用户表,并填充数据

-- 创建简单的用户表
CREATE TABLE users (
    id              SERIAL,
    username        varchar(80),
    age             integer
);


-- 添加一个名为lan的用户,并添加两个相关的标签

INSERT INTO users (username, age) values ('lan', 26);

INSERT INTO tags (name) VALUES ('student');
INSERT INTO tags (name) VALUES ('programmer');

c. 给用户打标签

接下来需要给用户lan打上标签,对原有的 SQL 语句做一些调整,并在打标签的时候把type字段填充为user

INSERT INTO taggings (tag_id, taggable_id, type) VALUES ((SELECT id FROM tags WHERE name = 'student'), (SELECT id FROM users WHERE username = 'lan'), 'user');

INSERT INTO taggings (tag_id, taggable_id, type) VALUES ((SELECT id FROM tags WHERE name = 'programmer'), (SELECT id FROM users WHERE username = 'lan'), 'user');

上述的 SQL 语句为用户打上了student以及programmer两个标签。

d. 查看标签情况

为了完成这个任务我们依然要联合三张表进行查询,同时还要约束type的类型

  • 用户名为lan的用户被打上的所有标签
blog=# SELECT tags.name FROM tags, users, taggings WHERE tags.id = taggings.tag_id AND users.id = taggings.taggable_id AND taggings.type = 'user' AND users.username = 'lan';

    name
------------
 student
 programmer
(2 rows)
  • 标题为Ruby的文章被打上的所有标签
blog=# SELECT tags.name FROM tags, posts, taggings WHERE tags.id = taggings.tag_id AND posts.id = taggings.taggable_id AND taggings.type = 'post' AND posts.title = 'Ruby';

   name
----------
 language
 tech

OK,都跟预期一样,现在的标签系统就比较通用了。

总结

本文通过 PostgreSQL 的基础语句来构建了一个标签系统。实现了一个标签系统其实并不难,各个语言的社区应该都有相关的集成。本人也就是想抛开编程语言,从数据库层面来剖析一个标签系统的基本原理。

PS: 另外推荐一个比较好用的 Model Design 工具dbdiagram,可以用文本的方式对数据表进行设计,边设计边预览。最后还能以 PNG,PDF 甚至 SQL 源文件的形式导出。本文的数据表配图均由用该软件制作。

有些标签是用户和文章公用的,例如:技术,ruby,但有些标签仅可以用于文章,如:通俗易懂等等,可能 tag 表还需要加一个分类...

这个文章是十年前的吗?现在还有人打标签,而不是靠全文检索?多态关联表就是反模式。

nouse 回复

不是,这篇文章是我刚写的。刚好在自己的项目中有用到 ActsAsTaggableOn。

luoyou 回复

taggings上面做分类就行了吧。

nouse 回复

不过全文搜索的东西我还没接触过,感谢提醒,我稍后找时间了解一下。

nouse 回复

搜索替代不了标签吧,多肽确实反模式,但 Rails 里方便…

是否采用多态关联表还是要看具体业务。标签系统用来做什么,产生的数据输出的目的是什么,是否有后续的处理需求。 是用多态或者单表其实不重要。

打标签和全文搜索没有冲突,或者说两者的目标其实不是一回事。

nouse 回复

请问给用户张三打上教师的标签这种功能,用全文搜索如何实现?

hooopo 回复

问一下,为啥多态是反模式呢?

leiz_me 回复

了解。

zouyu 回复

这个就要写长文批判了

hooopo 回复

求长文。

zouyu 回复

姓名:张三丰,个人说明:武当山道家学院教师

不就搜出来了吗?“教师”和“老师”也可以傻傻分不清楚。否则就会出现同义的重复标签。

leiz_me 回复

不用看具体业务,你节省了几张表,带来的问题更多。每次多 join 一张表,上千条就受不了。

nouse 回复

这点不太理解,你的意思是不用上述的标签系统查询的时候就不用 join 表了?而一般的业务场景下拿数据应该都会分批拿,比如会做分页等的,不会一次搞上千条数据出来,你所说的那种场景是否能详细描述一下?

16 楼 已删除
17 楼 已删除

为啥你们觉得 join 会慢,pgsql join 不慢啊?

文章写的好详细,赞。

如果担心大数据量的性能问题,还有一个选择是用 PostgreSQL 的 array,可以给它设置 GIN 类型的索引,本质上是一个全文索引的字段,Rails 也有相关的 gem : https://github.com/tmiyamon/acts-as-taggable-array-on

jasl 将本帖设为了精华贴。 01月12日 19:28
quakewang 回复

多谢提醒,PG 的 Array 我稍后会看一下。

nouse 回复

这个问题也不错,让我反思一下。 这是为什么 Ruby Chain 没有标签系统吗 ? 可是 StackExchange 仍然坚持标签,感觉上标签仍然有一定用处。

liprais 回复

嗯嗯其实 lateral join 很快的

lanzhiheng 回复

反模式是为了解决数据库应用开发中常用遇到的一些问题产生的,具体解决哪些问题可以看这里

SQL 反模式:SQL 建模与使用指南 - 张友东的文章 - 知乎 https://zhuanlan.zhihu.com/p/36831350

读《SQL 反模式》 - 方跃明的文章 - 知乎 https://zhuanlan.zhihu.com/p/37534634

cly 回复

是好书的感觉,不过似乎只有二手了。👀

需要 登录 后方可回复, 如果你还没有账号请 注册新账号