用于标记的数据库设计 [英] Database Design for Tagging
问题描述
您将如何设计数据库以支持以下标记功能:
How would you design a database to support the following tagging features:
- 项目可以有大量标签
- 必须快速搜索使用给定标签集标记的所有项目(项目必须具有所有标签，因此它是 AND 搜索，而不是 OR 搜索)
- 创建/写入项目可能会更慢以实现快速查找/读取
理想情况下，应使用单个 SQL 语句来查找(至少)带有一组 n 个给定标签的所有项目.由于要搜索的标签数量以及任何项目上的标签数量都是未知的并且可能很高，因此使用 JOIN 是不切实际的.
Ideally, the lookup of all items that are tagged with (at least) a set of n given tags should be done using a single SQL statement. Since the number of tags to search for as well as the number of tags on any item are unknown and may be high, using JOINs is impractical.
有什么想法吗?
感谢到目前为止所有的答案.
Thanks for all the answers so far.
不过，如果我没记错的话，给出的答案显示了如何对标签进行 OR 搜索.(选择具有一个或多个 n 标记的所有项目).我正在寻找有效的 AND 搜索.(选择所有具有 ALL n 标签的项目 - 可能还有更多.)
If I'm not mistaken, however, the given answers show how to do an OR-search on tags. (Select all items that have one or more of n tags). I am looking for an efficient AND-search. (Select all items that have ALL n tags - and possibly more.)
推荐答案
关于ANDing:听起来你在找关系除法"运算.这篇文章以简明易懂的方式介绍了关系划分.
About ANDing: It sounds like you are looking for the "relational division" operation. This article covers relational division in concise and yet comprehendible way.
关于性能:基于位图的方法直观地听起来很适合这种情况.但是，我不相信手动"实现位图索引是一个好主意，就像 digiguru 建议的那样:每当添加新标签时，这听起来像是一个复杂的情况(?)但是一些 DBMS(包括 Oracle)提供位图索引，这可能会以某种方式很有用，因为内置的索引系统消除了索引维护的潜在复杂性；此外，提供位图索引的 DBMS 在执行查询计划时应该能够适当地考虑它们.
About performance: A bitmap-based approach intuitively sounds like it will suit the situation well. However, I'm not convinced it's a good idea to implement bitmap indexing "manually", like digiguru suggests: It sounds like a complicated situation whenever new tags are added(?) But some DBMSes (including Oracle) offer bitmap indexes which may somehow be of use, because a built-in indexing system does away with the potential complexity of index maintenance; additionally, a DBMS offering bitmap indexes should be able to consider them in a proper when when performing the query plan.
这篇关于用于标记的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
