PostgreSQL構建通用標籤系統

lanzhiheng發表於2019-01-11
SQL

對資源打標籤在建站過程中是很常見的需求，有些時候我們需要給文章打標籤，有些時候我們需要給使用者打標籤。實現一個標籤系統其實並不難，其本質就是一個多對多的關係-我可以對同一篇部落格打多個標籤，同時也可以把一個標籤打到不同的部落格身上。這篇文章主要通過分析標籤系統的原理，並用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原始檔的形式匯出。本文的資料表配圖均由用該軟體製作。

相關文章