标记数据库设计

您将如何设计数据库以支持以下标记功能:

项目可以包含大量标签

搜索标记有给定标记集的所有项目必须快速(项目必须包含所有标记,因此它是AND搜索,而不是OR搜索)

创建/写入项目可能较慢以启用快速查找/读取

理想情况下,使用单个SQL语句查找使用(至少)一组n个给定标记标记的所有项目.由于要搜索的标签数量以及任何项目上的标签数量未知且可能很高,因此使用JOIN是不切实际的.

有任何想法吗？

---

谢谢你到目前为止的所有答案.

但是,如果我没有弄错的话,给出的答案显示了如何对标签进行OR搜索.(选择包含一个或多个n标签的所有项目).我正在寻找一个有效的AND搜索.(选择所有包含所有n个标签的项目 - 可能还有更多.)

1> Jeff Atwood..：

---

这是一篇关于标记数据库模式的好文章:

http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/

以及性能测试:

http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/

请注意,MySQL的结论非常具体,(至少在2005年编写的时候)具有非常差的全文索引特性.

---

你介意分享你是如何与SO一起去的吗？

---

尽管是由@Jeff编写的,但这仍然只是一个链接答案.

---

关于Meta的这个问题有关于SO模式的一些信息:http://meta.stackexchange.com/questions/1863/so-database-schema

2> Troels Arvin..：

---

关于ANDing:听起来你正在寻找"关系师"操作.本文以简洁而又易于理解的方式介绍了关系划分.

关于性能:基于位图的方法直观地听起来很适合这种情况.但是,我不相信"手动"实现位图索引是个好主意,就像digiguru建议的那样:每当添加新标签时听起来都是一个复杂的情况(？)但是有些DBMS(包括Oracle)提供的位图索引可能会以某种方式因为内置的索引系统可以消除索引维护的潜在复杂性; 另外,提供位图索引的DBMS应该能够在执行查询计划时正确考虑它们.

---

我不得不说答案有点短视,因为使用数据库的位字段类型会将您限制为特定的位数.这并不意味着每个项目仅限于一定数量的标签,而是整个系统中只能有一定数量的唯一标签(通常最多为32或64).

---

Mark:这个看起来不错:http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/这可能是一个重新发布的版本我所提到的那个.

3> Slartibartfa..：

---

我没有看到直接解决方案的问题:项目表,标签表,"标记"的交叉表

交叉表上的指数应该足够优化.选择合适的项目即可

SELECT * FROM items WHERE id IN  
    (SELECT DISTINCT item_id FROM item_tag WHERE  
    tag_id = tag1 OR tag_id = tag2 OR ...)  

和标记将是

SELECT * FROM items WHERE  
    EXISTS (SELECT 1 FROM item_tag WHERE id = item_id AND tag_id = tag1)  
    AND EXISTS (SELECT 1 FROM item_tag WHERE id = item_id AND tag_id = tag2)  
    AND ...

诚然,对于大量的比较标签来说效率不高.如果要在内存中维护标记计数,可以使用不常用的标记进行查询,这样可以更快地评估AND序列.根据预期匹配的标签数量和匹配任何一个标签的预期,这可能是好的解决方案,如果你要匹配20个标签,并期望一些随机项目将匹配其中的15个,那么这仍然会很重在数据库上.

4> Winston Fass..：

---

我只想强调一下@Jeff Atwood链接到的文章(http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/)是非常彻底的(它讨论了3种不同模式的优点方法)并且对于AND查询有一个很好的解决方案,它通常比目前为止提到的更好(即它不会为每个术语使用相关的子查询).评论中还有很多好东西.

ps - 每个人在这里谈论的方法在本文中被称为"Toxi"解决方案.

---

链接已经死了:<

---

我记得读过这篇很棒的文章,但不幸的是现在链接已经死了.:(任何人都知道它的镜子？

5> Zizzencs..：

---

您可能希望尝试使用非严格数据库解决方案,例如Java Content Repository实现(例如Apache Jackrabbit),并使用基于Apache Lucene构建的搜索引擎.

具有适当缓存机制的该解决方案可能比本地解决方案产生更好的性能.

但是,我并不认为在小型或中型应用程序中,您需要比先前帖子中提到的规范化数据库更复杂的实现.

编辑:通过您的澄清,使用类似JCR的搜索引擎解决方案似乎更具吸引力.从长远来看,这将极大地简化您的程序.

6> Brad Bruce..：

---

最简单的方法是创建标签表。
Target_Type-如果要标记多个表
Target-被标记的记录的键-标记
Tag的文本

查询数据将类似于：

Select distinct target from tags   
where tag in ([your list of tags to search for here])  
and target_type = [the table you're searching]

更新
根据您对AND条件的要求，上面的查询将变成这样

select target
from (
  select target, count(*) cnt 
  from tags   
  where tag in ([your list of tags to search for here])
    and target_type = [the table you're searching]
)
where cnt = [number of tags being searched]