标签数据库设计 - 开卷题库

小开

我不认为一个简单的解决方案有什么问题: 项目的表，标签的表，“标签”的可交叉性

交叉表上的指数应该足够优化。选择适当的项目将是

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——以防您要标记多个表
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]

小开

您将无法避免连接，并且仍然保持某种程度的正常化。

我的方法是使用标记表。

 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 标记。TagName =@TagName1 AND Tags.TagName =@TagName2 etc. ..并动态构建查询。

小开

您可能想要尝试一个非严格的数据库解决方案，比如 Java 内容存储库实现(例如阿帕奇长耳兔) ，并使用构建在阿帕奇・卢辛之上的搜索引擎。

使用适当的缓存机制的这种解决方案可能比自行开发的解决方案产生更好的性能。

但是，我并不认为在中小型应用程序中需要比前面文章中提到的规范化数据库更复杂的实现。

编辑: 随着您的澄清，似乎更有说服力的使用类似 JCR 的解决方案与搜索引擎。从长远来看，这将极大地简化您的程序。

小开

我喜欢用一些表来表示原始数据，所以在这种情况下

Items (ID pk, Name, <properties>)
Tags (ID pk, Name)
TagItems (TagID fk, ItemID fk)

这对于写入时间来说工作得很快，并且保持一切正常化，但是您可能还会注意到，对于每个标记，对于每个想要 AND 的进一步标记，都需要两次联接表，因此读取速度很慢。

提高读取能力的一个解决方案是通过设置存储过程创建一个按命令创建的缓存表，该存储过程实际上创建了一个以扁平格式表示数据的新表..。

CachedTagItems(ID, Name, <properties>, tag1, tag2, ... tagN)

然后，您可以考虑需要更新标记项表的频率，如果每次插入都需要更新标记项表，那么在光标插入事件中调用存储过程。如果是每小时一次的任务，那么设置一个每小时一次的任务来运行它。

现在，为了在数据检索方面变得非常聪明，您需要创建一个存储过程来从标记中获取数据。与在大规模 case 语句中使用嵌套查询不同，您希望传入一个包含要从数据库中选择的标记列表的参数，并返回 Items 的记录集。这将是最好的二进制格式，使用位运算符。

在二进制格式中，它很容易解释。假设有四个标签被分配给一个项目，我们可以用二进制表示它

如果所有四个标记都分配给一个对象，那么该对象应该是这样的..。

如果只有前两个..。

然后就只需要在所需的列中查找带有1和0的二进制值。使用 SQLServer 的按位运算符，可以使用非常简单的查询检查第一列中是否有1。

检查这个链接找出更多。

小开

套用其他人的话: 诀窍不在于模式，而在于疑问。

实体/标签/标签的幼稚模式是正确的方法。但是正如您所看到的，目前还不清楚如何使用大量标记执行 AND 查询。

优化该查询的最佳方法是依赖于平台的，因此我建议使用 RDBS 重新标记您的问题，并将标题改为“在标记数据库上执行 AND 查询的最佳方法”。

我有一些关于 MS SQL 的建议，但是如果这不是你正在使用的平台，我会尽量避免。

小开

我支持@Zizzencs 的建议，即您可能需要一些不完全(R)以 DB 为中心的东西

不知为何，我认为使用普通 nvarchar 字段存储带有适当缓存/索引的标记可能会产生更快的结果。但那只是我。

我曾经用3个表来表示多对多的关系(Item Tags ItemTags) ，但是我想你会在很多地方处理标签，我可以告诉你，3个表必须同时被操作/查询，这肯定会使你的代码更加复杂。

您可能需要考虑增加的复杂性是否值得。

小开

最佳答案

关于安定: 听起来你正在寻找“关系部门”的运作。这篇文章以简明易懂的方式涵盖了关系划分。

关于性能: 基于位图的方法听起来很适合这种情况。然而，我并不认为像 digiguru 建议的那样“手动”实现位图索引是个好主意: 每当添加新标签时，这听起来就像是一个复杂的情况(?) 但是一些数据库管理系统(包括 Oracle)提供位图索引，这些索引可能在某种程度上是有用的，因为内置的索引系统消除了索引维护的潜在复杂性; 另外，提供位图索引的数据库管理系统在执行查询计划时应该能够正确地考虑它们。

小开

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

Http://howto.philippkeller.com/2005/04/24/tags-database-schemas/

还有性能测试:

Http://howto.philippkeller.com/2005/06/19/tagsystems-performance-tests/

请注意，这里的结论非常特定于 MySQL，它(至少在编写时是在2005年)具有非常差的全文索引特性。

小开

我只想强调@Jeff Atwood 链接到的那篇文章(http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/)非常全面(它讨论了3种不同模式方法的优点) ，并且对 AND 查询有一个很好的解决方案，通常比这里提到的要好(即它没有对每个术语使用相关的子查询)。评论里也有很多好东西。

Ps-每个人在这里讨论的方法在文章中称为“ Toxi”解决方案。

小开

上述答案的一个变体是使用标记 id，对它们进行排序，组合为 ^ 分隔的字符串，并对它们进行散列。然后简单地将散列关联到项。每个标记组合都生成一个新键。要执行 AND 搜索，只需使用给定的标记 ID 重新创建散列并进行搜索。更改项上的标记将导致重新创建散列。具有相同标记集的项共享相同的散列键。