如何实现标签系统 [英] How to implement tag system
问题描述
我在想，最好的办法是实行标签制度，像这样使用一个。我想这一点，但我不能拿出一个很好的可扩展解决方案。
I was wondering what the best way is to implement a tag system, like the one used on SO. I was thinking of this but I can't come up with a good scalable solution.
我在想有一个基本的三表解决方案：具有标签表，文章表和一个 tag_to_articles 表。
I was thinking of having a basic 3 table solution: having a tags table, an articles tables and a tag_to_articles table.
这是对这个问题的最佳解决方案，还是有选择吗？使用这种方法的表会得到非常大的时候，和搜索，这是不是太有效率我以为。在另一方面，这并不重要，查询执行速度快。
Is this the best solution to this problem, or are there alternatives? Using this method the table would get extremely large in time, and for searching this is not too efficient I assume. On the other hand it is not that important that the query executes fast.
推荐答案
我相信你们会感兴趣的这篇博客文章：的标签：数据库架构
I believe you'll find interesting this blog post: Tags: Database schemas
问题：你想拥有一个数据库模式，你可以标记一个 书签（或博客帖子或其他）与只要你想尽可能多的标签。 后来那么，你想运行的查询约束书签到 工会或标签交集。您还希望排除（例如：减） 从搜索结果中一些标记。
The Problem: You want to have a database schema where you can tag a bookmark (or a blog post or whatever) with as many tags as you want. Later then, you want to run queries to constrain the bookmarks to a union or intersection of tags. You also want to exclude (say: minus) some tags from the search result.
在此溶液中，模式得到了只有一个表，它被规格化。这种类型被称为MySQLicious溶液，因为MySQLicious出口del.icio.us数据到一个表具有这种结构
"MySQLicious" solution
In this solution, the schema has got just one table, it is denormalized. This type is called "MySQLicious solution" because MySQLicious imports del.icio.us data into a table with this structure.
交集（AND） 查询的搜索+ Web服务+ semweb：
Intersection (AND) Query for "search+webservice+semweb":
SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
AND tags LIKE "%webservice%"
AND tags LIKE "%semweb%"
联盟（OR） 查询查询| Web服务| semweb：
Union (OR) Query for "search|webservice|semweb":
SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
OR tags LIKE "%webservice%"
OR tags LIKE "%semweb%"
减 查询的搜索+ Web服务，semweb
Minus Query for "search+webservice-semweb"
SELECT *
FROM `delicious`
WHERE tags LIKE "%search%"
AND tags LIKE "%webservice%"
AND tags NOT LIKE "%semweb%"
天窗的解决方案
天窗组织其数据在两个表。该表scCategories是标签 - 表，并得到了一个外键书签 - 表。
"Scuttle" solution
Scuttle organizes its data in two tables. That table "scCategories" is the "tag"-table and has got a foreign key to the "bookmark"-table.
交集（AND） 查询为书签+ Web服务+ semweb：
Intersection (AND) Query for "bookmark+webservice+semweb":
SELECT b.*
FROM scBookmarks b, scCategories c
WHERE c.bId = b.bId
AND (c.category IN ('bookmark', 'webservice', 'semweb'))
GROUP BY b.bId
HAVING COUNT( b.bId )=3
首先，所有书签标签组合进行搜索，其中，标签是书签，Web服务或semweb（c.category IN（'书签'，'Web服务'，'semweb'）），然后就已经得到了搜索的所有三个标签的书签会考虑（有COUNT（b.bId）= 3）。
First, all bookmark-tag combinations are searched, where the tag is "bookmark", "webservice" or "semweb" (c.category IN ('bookmark', 'webservice', 'semweb')), then just the bookmarks that have got all three tags searched for are taken into account (HAVING COUNT(b.bId)=3).
联盟（OR） 查询为书签| Web服务| semweb： 刚刚离开了HAVING子句，你有工会的：
Union (OR) Query for "bookmark|webservice|semweb": Just leave out the HAVING clause and you have union:
SELECT b.*
FROM scBookmarks b, scCategories c
WHERE c.bId = b.bId
AND (c.category IN ('bookmark', 'webservice', 'semweb'))
GROUP BY b.bId
减号（豁免） 查询为书签+ Web服务 - semweb，即：书签和互联网服务和不semweb
Minus (Exclusion) Query for "bookmark+webservice-semweb", that is: bookmark AND webservice AND NOT semweb.
SELECT b. *
FROM scBookmarks b, scCategories c
WHERE b.bId = c.bId
AND (c.category IN ('bookmark', 'webservice'))
AND b.bId NOT
IN (SELECT b.bId FROM scBookmarks b, scCategories c WHERE b.bId = c.bId AND c.category = 'semweb')
GROUP BY b.bId
HAVING COUNT( b.bId ) =2
离开了HAVING COUNT导致查询了。书签| Web服务，semweb
TOXI 想出了一个三表结构。通过表tagmap的书签和标签是n至米相关。每个标签可以与不同的书签，反之亦然使用。该DB-模式也被字preSS。 该查询是完全一样的天窗的解决方案。
Toxi came up with a three-table structure. Via the table "tagmap" the bookmarks and the tags are n-to-m related. Each tag can be used together with different bookmarks and vice versa. This DB-schema is also used by wordpress. The queries are quite the same as in the "scuttle" solution.
交集（AND） 查询为书签+ Web服务+ semweb
Intersection (AND) Query for "bookmark+webservice+semweb"
SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND b.id = bt.bookmark_id
GROUP BY b.id
HAVING COUNT( b.id )=3
联盟（OR） 查询为书签| Web服务| semweb
Union (OR) Query for "bookmark|webservice|semweb"
SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND b.id = bt.bookmark_id
GROUP BY b.id
减号（豁免） 查询为书签+ Web服务 - semweb，即：书签和互联网服务和不semweb
Minus (Exclusion) Query for "bookmark+webservice-semweb", that is: bookmark AND webservice AND NOT semweb.
SELECT b. *
FROM bookmark b, tagmap bt, tag t
WHERE b.id = bt.bookmark_id
AND bt.tag_id = t.tag_id
AND (t.name IN ('Programming', 'Algorithms'))
AND b.id NOT IN (SELECT b.id FROM bookmark b, tagmap bt, tag t WHERE b.id = bt.bookmark_id AND bt.tag_id = t.tag_id AND t.name = 'Python')
GROUP BY b.id
HAVING COUNT( b.id ) =2
离开了HAVING COUNT导致查询了。书签| Web服务，semweb
这篇关于如何实现标签系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
