数据库设计标签 [英] Database Design for Tagging
问题描述
如何设计数据库以支持以下标记功能：
- 项目可以包含大量标签
- 搜索所有标有标签的项目必须快速（项目必须有所有标签，所以它是一个AND搜索，而不是OR搜索）
- 创建/写入项目可能会更慢，以便快速查找/阅读
理想情况下，查找所有使用（至少）标记的n个给定标签的集合应该使用单个SQL语句来完成。由于要搜索的标签数量以及任何项目上的标签数量是未知的，并且可能很高，因此使用JOINs是不切实际的。
任何想法？感谢所有的答案。
b
$ b
没有错，但是，给定的答案显示了如何对标签进行OR搜索。 （选择所有具有一个或多个n个标签的项目）。我正在寻找一个高效的搜索。 （选择所有包含所有n个标签的项目 - 可能更多）
关于ANDing：听起来你正在寻找关系分裂运作。 这篇文章以简洁易懂的方式介绍了关系部门。
关于性能：基于位图的方法直观地听起来像是适合这种情况。然而，我不相信，手动实现位图索引是一个好主意，如digiguru所示：每当添加新标签时，这听起来像一个复杂的情况（？）但是一些DBMS（包括Oracle）提供位图索引，可能会以某种方式因为内置的索引系统可以避免索引维护的潜在复杂性;此外，提供位图索引的DBMS应该能够在执行查询计划时适当考虑它们。
How would you design a database to support the following tagging features:
- items can have a large number of tags
- searches for all items that are tagged with a given set of tags must be quick (the items must have ALL tags, so it's an AND-search, not an OR-search)
- creating/writing items may be slower to enable quick lookup/reading
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.
Any ideas?
Thanks for all the answers so far.
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.)
About ANDing: It sounds like you are looking for the "relational division" operation. This article covers relational division in concise and yet comprehendible way.
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屋！
