问题

在很多业务系统中，都有对对象的分类和标签设计。在数据库层面如何设计相应的表，以及如何做查询优化，是一个比较普遍的问题。

分类和标签的设计

信息离不开搜索和分类，虽然机器学习能做到自动分类，但人工分类系统也是不可少的。
在一个系统中，有多种类型的对象，不同类型的对象都可以做分类。
为了统一处理，我把所有的分类都放到一棵树上。
不同类型的对象的分类对应于一棵子树。

例如，在我的系统中，有(博客，文摘，链接，图片，电子书)几个基本对象类型。
在分类树上，每个分类节点都可以服务于不同类型。

知名开源系统的设计

jive论坛

只有tag，没有分类，tag和帖子(thread)多对多的设计：

CREATE TABLE tag (tagID BIGINT NOT NULL,title varchar(50) default '',assonum int(20) NOT NULL default '0',PRIMARY KEY (tagID)
);
CREATE TABLE threadTag (threadTagID BIGINT NOT NULL,threadID BIGINT NOT NULL,tagID BIGINT NOT NULL,PRIMARY KEY (threadTagID),INDEX jiveThread_tagID_idx (tagID),INDEX tagID_jiveThread_idx (threadID)
);

Solo博客系统的设计

在 Solo 中一共有 3 张关联表：

• archivedate_article：存档-文章关联
• category_tag：分类-标签关联
• tag_article：标签-文章关联

wordpress的数据库设计

WordPress数据库的11个数据表，没有单独的tag：

• wp_commentmeta: 文章评论额外信息表
• wp_comments: 文章评论信息表
• wp_links :链接信息表
• wp_options ：基本配置信息表，通常通过get_option来操作，该表通常作为插件存储数据的一个地方。
• wp_postmeta： 文章额外数据表，例如文章浏览次数，文章的自定义字段等都存储在这里
• wp_posts ：文章信息表，包括了日志、附件、页面等等信息。是WordPress最重要的一个数据表。
• wp_terms ：词汇表，也就是系统中各类对象。如文章、链接、标签的信息表。
• wp_term_relationships ：对象和分类的对应关系，如分类与文章信息表（wp_posts）、链接表(wp_links)的关联表。
• wp_term_taxonomy： 分类信息表，区分wp_terms信息的分类类型，有category、link_category和tag三种分类类型。
• wp_usermeta: 用户额外信息表 wp_users 用户基本信息表。存放系统所有用户基本信息。

​
三张表结构如下：

CREATE TABLE wp_terms (term_id bigint(20) unsigned NOT NULL auto_increment,name varchar(200) NOT NULL default '',slug varchar(200) NOT NULL default '',term_group bigint(10) NOT NULL default 0,PRIMARY KEY  (term_id),KEY slug (slug($max_index_length)),KEY name (name($max_index_length))
) CREATE TABLE wp_term_taxonomy (term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment,term_id bigint(20) unsigned NOT NULL default 0,taxonomy varchar(32) NOT NULL default '',description longtext NOT NULL,parent bigint(20) unsigned NOT NULL default 0,count bigint(20) NOT NULL default 0,PRIMARY KEY  (term_taxonomy_id),UNIQUE KEY term_id_taxonomy (term_id,taxonomy),KEY taxonomy (taxonomy)
) CREATE TABLE wp_term_relationships (object_id bigint(20) unsigned NOT NULL default 0,term_taxonomy_id bigint(20) unsigned NOT NULL default 0,term_order int(11) NOT NULL default 0,PRIMARY KEY  (object_id,term_taxonomy_id),KEY term_taxonomy_id (term_taxonomy_id)
) 

参考链接

• https://www.mezgy.com/201.html