标记的数据库设计
我们如何设计数据库来支持以下标记功能：
- 项目可以具有大量标签
- 搜索带有给定标签集的所有项目必须快速(这些项目必须具有ALL标记，因此这是"与"搜索，而不是"或者"搜索)
- 创建/写入项目可能较慢，无法快速查找/阅读
理想情况下，应使用单个SQL语句查找(至少)被标记为n个给定标记集的所有项目。由于要搜索的标签数量以及任何项目上的标签数量都是未知的并且可能很高，因此使用JOIN是不切实际的。
有任何想法吗？
到目前为止，感谢我们提供所有答案。
但是，如果我没记错的话，给出的答案将显示如何对标签进行"或者"搜索。 (选择所有具有n个标签中的一个或者多个标签的项目)。我正在寻找有效的AND搜索。 (选择所有带有n个标签，甚至可能更多的标签的项目。)
解决方案
回答
我不认为直接解决方案有问题：项目表，标签表，"标签"交叉表
交叉表上的指标应该足够优化。选择适当的项目将是
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个匹配，那么这仍然很繁重在数据库上。
回答
最简单的方法是创建标签表。Target_Type-如果我们要标记多个表
目标-被标记的记录的键
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]
回答
我们将无法避免加入并且仍会被标准化。
我的方法是拥有一个标签表。
TagId (PK)| TagName (Indexed)
然后，项目表中有一个TagXREFID列。
这个TagXREFID列是第3个表的FK，我将其称为TagXREF：
TagXrefID | ItemID | TagId
因此，要获取某项的所有标签将类似于：
SELECT Tags.TagId,Tags.TagName
FROM Tags,TagXref
WHERE TagXref.TagId = Tags.TagId
AND TagXref.ItemID = @ItemID
为了获得标签的所有项目，我将使用以下方法：
SELECT * FROM Items, TagXref
WHERE TagXref.TagId IN
( SELECT Tags.TagId FROM Tags
WHERE Tags.TagName = @TagName; )
AND Items.ItemId = TagXref.ItemId;
将AND标记串在一起，我们可以对上面的语句稍加修改，以添加AND Tags.TagName = @ TagName1 AND Tags.TagName = @ TagName2等...并动态构建查询。
回答
我们可能想尝试使用非限制性的数据库解决方案(例如Java Content Repository实现(例如Apache Hymanrabbit))，并使用基于Apache Lucene之类的基础之上构建的搜索引擎。
与本地解决方案相比，具有适当缓存机制的此解决方案可能会产生更好的性能。
但是，我真的不认为在中小型应用程序中，我们将需要比以前的文章中提到的规范化数据库更复杂的实现。
编辑：澄清一下，在搜索引擎中使用类似JCR的解决方案似乎更有吸引力。从长远来看，这将大大简化程序。
回答
我想做的是有许多表代表原始数据，因此在这种情况下，我们将拥有
Items (ID pk, Name, <properties>) Tags (ID pk, Name) TagItems (TagID fk, ItemID fk)
这样可以在写入时间上快速运行，并使所有内容正常化，但是我们可能还需要注意，对于每个标签，我们需要为要与的其他每个标签连接两次表，因此读取速度很慢。
一种改善读取的解决方案是通过设置一个存储过程来在命令中创建一个缓存表，该存储过程实质上是创建一个新表来表示扁平化格式的数据...
CachedTagItems(ID, Name, <properties>, tag1, tag2, ... tagN)
然后，我们可以考虑"标记项"表需要保持多长时间更新一次(如果它在每次插入中)，然后在游标插入事件中调用存储过程。如果这是每小时的任务，则设置一个每小时的任务来运行它。
现在，要真正变得聪明起来，就需要创建一个存储过程来从标记中获取数据。而不是在大量的case语句中使用嵌套查询，而是要传递一个包含要从数据库中选择的标签列表的单个参数，然后返回记录集Items。使用按位运算符，最好是二进制格式。
以二进制格式，很容易解释。假设有四个标签分配给一个项目，以二进制形式可以表示
0000
如果将所有四个标签都分配给一个对象，则该对象将如下所示：
1111
如果只是前两个...
1100
这只是在想要的列中找到带有1和0的二进制值的情况。使用SQL Server的按位运算符，我们可以使用非常简单的查询来检查第一列中是否有1.
检查此链接以了解更多信息。
回答
用其他人的话来解释一下：技巧不在模式中，而在查询中。
实体/标签/标签的幼稚模式是正确的方法。但是，正如我们所看到的，目前尚不清楚如何执行带有许多标签的AND查询。
优化该查询的最佳方法将取决于平台，因此，我建议我们使用RDBS重新标记问题，并将标题更改为"在标记数据库上执行AND查询的最佳方法"之类的内容。
对于MS SQL，我有一些建议，但如果我们使用的平台不多，将尽量避免。
回答
我第二个@Zizzencs建议我们可能想要的不是完全以(R)DB为中心的内容
我以某种方式相信使用简单的nvarchar字段通过适当的缓存/索引存储该标签可能会产生更快的结果。但这就是我。
我已经实现了使用3个表表示之前的多对多关系(项目标签ItemTags)的标记系统，但是我想我们将在很多地方处理标签，我可以告诉我们3个表必须始终被同时操纵/查询肯定会使代码更复杂。
我们可能要考虑增加的复杂性是否值得。
回答
关于ANDing：听起来我们正在寻找"关系除法"操作。本文以简洁但可理解的方式介绍了关系划分。
关于性能：基于位图的方法听起来很适合这种情况。但是，我不认为"手动"实现位图索引是个好主意，就像digiguru所建议的：每当添加新标签时，听起来情况就很复杂(？)，但是某些DBMS(包括Oracle)提供了位图索引，这可能会以某种方式之所以有用，是因为内置的索引系统消除了索引维护的潜在复杂性；此外，提供位图索引的DBMS在执行查询计划时应该能够适当考虑它们。
回答
这是一篇有关标记数据库模式的好文章：
http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/
以及性能测试：
http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/
请注意，那里的结论是非常特定于MySQL的，MySQL的结论(至少在撰写本文时是在2005年)具有非常差的全文索引特性。
回答
我只是想强调一下@Jeff Atwood链接到的文章(http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/)非常详尽(它讨论了3种不同模式的优点方法)，并为AND查询提供了一个很好的解决方案，该查询通常会比到目前为止所提到的要好(即，它没有为每个术语使用相关的子查询)。在评论中也有很多好东西。
ps在本文中，每个人都在谈论的方法称为" Toxi"解决方案。
