blocks|key|865551|text|有各种有效的模式，随着标记项的数量增加，每个模式都会对您需要的常见查询产生各自的性能影响：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|865552|http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/|unordered-list-item|offset|length|865553|http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/|865554|就我个人而言，我喜欢有一个标记表和一个链接表，它将标记与项关联起来，因为它是非规范化的(没有标记名称的重复)，并且我可以在必要时将附加信息存储在链接表中(例如，当项目被标记时)。|865555|您还可以添加一些去角色化的数据，如果您想要简单的选择，而代价是在标记表中存储使用计数所需的额外数据维护，或者存储标签名，这些标记名用于避免访问每个项的链接表和标签表，这对于显示多个项目及其所有标记和简单的标记版本非常有用。如果你对这种事感兴趣;)|865556|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|1S|0|0|0|1Z|1|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@$E|W|F|X|1|Y]]|A|$]]|$1|G|3|H|5|D|7|Z|8|@]|9|@$E|10|F|11|1|12]]|A|$]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|C]]|T|$5|P|Q|R|A|$S|H]]]]

There are various schemas which are effective, each with their own performance implications for the common queries you'll need as the number of tagged items grows:

<ul>
<li><a href="http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/" rel="noreferrer">http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/</a></li>
<li><a href="http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/" rel="noreferrer">http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/</a></li>
</ul>

Personally, I like having a tag table and a link table which associates tags with items, as it's denormalized (no duplication of tag names) and I can store additional information in the link table (such as when the item was tagged) when necessary.

You can also add some denormalised data if you're feeling frisky and want simple selects at the cost of the additional data maintenance required by storing usage counts in the tag table, or storing tag names which were used in the item table itself to avoid hitting the link table and tag table for each item, which is useful for displaying multiple items with all their tags and for simple tag versioning... if you're into that sort of thing ;)

blocks|key|1205065|text|像这样的事情浮现在我的脑海中:加上这两张桌子|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1205066|标签|offset|length|style|BOLD|1205067|TagID|unordered-list-item|1205068|TagName|1205069|TagDescription|1205070|PhotoTags|1205071|PhotoID|1205072|1205073|您也可以将其扩展到相册，在相册和标记之间有一个相交表。|1205074|entityMap^0|0|0|2|0|0|0|0|0|9|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@$D|Z|E|10|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|J|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|J|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@$D|15|E|16|F|G]]|9|@]|A|$]]|$1|Q|3|R|5|J|7|17|8|@]|9|@]|A|$]]|$1|S|3|I|5|J|7|18|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|19|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|W|$]]

Something like this comes to my mind: add those two tables

Tags

<ul>
<li>TagID </li>
<li>TagName </li>
<li>TagDescription</li>
</ul>

PhotoTags

<ul>
<li>PhotoID</li>
<li>TagID</li>
</ul>

You can extend this to albums too, having an intersection table between Photo Albums and Tags.

blocks|key|865600|text|我建议看看成熟的开源软件是如何做到这一点的。例如，画廊和您一样将其元数据存储在数据库中，并且非常丰富。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|865601|不过，我不认为您会找到“标准”模式。我能想到的最接近的是EXIF元数据格式，它嵌入到图像文件中(通过摄像机等)。|865602|entityMap|0|LINK|mutability|MUTABLE|url|http://gallery.menalto.com/^0|P|2|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

I suggest looking to see how established open-source software does it. For example, <a href="http://gallery.menalto.com/" rel="nofollow noreferrer">Gallery</a> stores its meta-data in a database like you do, and is pretty rich.

I don't think you'll find a "standard" schema, though. The closest thing I can think of is the EXIF meta-data format, which is embedded within image files themselves (by cameras, etc).

blocks|key|1205157|text|如果您想要拥有数百万条记录的真实性能，您可以在一个字段中存储标记，用一个完整的索引/搜索守护进程(如sphinxsearch+)分隔逗号，然后再提取记录。您只需添加一个表，列出所有带有count值的标记，以了解它们附加到项目的频率。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1205158|我知道这不是通常的方法，也比纯数据库解决方案要复杂一些，但是搜索标记相关项的速度真的很快。|1205159|您也可以使用数据库引擎的全文搜索方位性，但是当有大量记录时，大多数引擎往往速度较慢。|1205160|如果是为一个小项目，你可以走你的路，缝好和适当的方式去做。但我想和你分享另一个解决方案。你觉得呢？|1205161|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

if you want real performance with millions of records, you could store tags in one field, comma separated and retreive records with a full-text index/search daemon like sphinxsearch. All you have to add, is a table listing all tags with a count value to know how often they are attached to an item.

I know it's not the usual way and a little more complicated than a pure database solution, but it's really really fast to search tag related items.

You could use full-text search fonctionnality of your database engine too, but when there's lots of records, most engines tend to be slow.

If it's for a small project, you can go your way, seams good and proper way to do. But I would just share with you this other solution. What do you think of ?

blocks|key|1205200|text|我在一个没有很多用户的小型系统中这样做过，但我以前想知道是否有一种“接受”的方法来管理标记。在阅读了insin发布的链接和大量关于标签的其他博客文章之后，似乎公认的方法是在数据集太大的情况下将其完全规范化并缓存某些内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1205201|因为这是一个多-多关系(每个标签可以属于任意数量的照片-每一张照片可以有多个标签)，关系数据库理论让你创建一个照片表，一个标签表和一个交叉参考表来连接它们。|1205202|photos
++photoid
++caption
++filename
++date

tags
++tagid
++tagname

phototags
++photoid
++tagid|code-block|syntax|javascript|1205203|这会导致从非常大的数据集中选择数据时出现缩放问题，但所有标准化程度较低的模式也是如此(例如，按文本字段进行排序和筛选可能总是比使用整数慢)。如果您增长到美味的大小，甚至是StackOverflow，您可能需要对标记集进行一些缓存。|1205204|您必须面对的另一个问题是标记规范化问题。这与数据库规范化无关--它只是确保(例如)+"StackOverflow“、”堆栈溢出“和”堆栈溢出“标记是相同的。许多地方不允许空白，或者自动删除它。有时你会看到标点符号的相同之处--使"StackOverflow“与”堆栈溢出“相同。自动降低是相当标准的。您甚至会看到特例规范化--类似于使"c#“与"csharp”相同。|1205205|标记愉快！|1205206|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

I've done this in a small system without very many users, but I've wondered before if there was an "accepted" way to manage tags. After reading through the links posted by insin and plenty of other blog posts on tagging, it seems that the accepted way is to store it fully normalized and cache certain things if your data set gets too big.

Since it's a many-many relationship (each tag can belong to any number of photos - each photo can have many tags), relational database theory has you create a photo table, a tag table and a cross-reference table to link them.

<pre><code>photos
 photoid
 caption
 filename
 date

tags
 tagid
 tagname

phototags
 photoid
 tagid
</code></pre>

This has scaling problems selecting from really large datasets, but so do all the less-normalized schemas (sorting and filtering by a text field will probably always be slower than using an integer, for example). If you grow as large as delicious or maybe even StackOverflow you'll probably have to do some caching of your tag sets.

Another issue you'll have to face is the issue of tag normalization. This doesn't have anything to do with database normalization - it's just making sure that (for example) the "StackOverflow", "stackoverflow" and "stack overflow" tags are the same. Lots of places disallow whitespace or automatically strip it out. Sometimes you'll see the same thing for punctuation - making "StackOverflow" the same as "Stack-Overflow". Auto-lowercasing is pretty standard. You'll even see special case normalization - like making "c#" the same as "csharp".

Happy tagging!

blocks|key|1205250|text|在我的应用程序BugTracker.NET中，我假设不会有太多的bug。也许数以万计，但不是数千万。这个假设允许我缓存它们引用的项的标记和ids。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1205251|在数据库中，标签是在输入标签时与bug一起存储在逗号分隔的文本字段中的。|1205252|添加或更改标记字段时，启动选择所有bugids及其标记的后台线程，解析文本，构建一个映射，其中键是标记，值是具有该标记的所有ids的列表。然后，我在Asp.Net应用程序对象中缓存该映射。|1205253|下面是我刚才描述的代码。|1205254|可以对代码进行优化，这样它就可以不去检查所有的but，而是增量地修改缓存的映射，但即使是未优化的，它也可以正常工作。|1205255|当有人使用标记进行搜索时，我在地图中查找值，获取id列表，然后使用SQL获取那些bug，并使用"where+id+in+(1，2，3.)“条款。|1205256|++++public+static+void+threadproc_tags(object+obj)
++++{
++++++++System.Web.HttpApplicationState+app+=+(System.Web.HttpApplicationState)obj;

++++++++SortedDictionary<string,List<int>>+tags+=+new+SortedDictionary<string,List<int>>();

++++++++//+update+the+cache
++++++++DbUtil+dbutil+=+new+DbUtil();
++++++++DataSet+ds+=+dbutil.get_dataset("select+bg_id,+bg_tags+from+bugs+where+isnull(bg_tags,'')+<>+''");

++++++++foreach+(DataRow+dr+in+ds.Tables[0].Rows)
++++++++{
++++++++++++string[]+labels+=+btnet.Util.split_string_using_commas((string)+dr[1]);

++++++++++++//+for+each+tag+label,+build+a+list+of+bugids+that+have+that+label
++++++++++++for+(int+i+=+0;+i+<+labels.Length;+i%2B%2B)
++++++++++++{

++++++++++++++++string+label+=+normalize_tag(labels[i]);

++++++++++++++++if+(label+!=+"")
++++++++++++++++{
++++++++++++++++++++if+(!tags.ContainsKey(label))
++++++++++++++++++++{
++++++++++++++++++++++++tags[label]+=+new+List<int>();
++++++++++++++++++++}

++++++++++++++++++++tags[label].Add((int)dr[0]);
++++++++++++++++}
++++++++++++}
++++++++}

++++++++app["tags"]+=+tags;

++++}|code-block|syntax|javascript|1205257|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|V|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|W|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|X|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|Y|8|@]|9|@]|A|$O|P]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

In my app BugTracker.NET, I make an assumption that there won't be TOO many bugs. Maybe tens of thousands, but not tens of millions. That assumption allows me to cache the tags and the ids of the items they reference.

In the database, the tags are stored as they are entered, with the bugs, in a comma delimited text field.

When a tag field is added or changed, that kicks off a background thread that selects all bugids and their tags, parses the text, building a map where the key is the tag and the value is a list of all the ids that have that tag. I then cache that map in the Asp.Net Application object.

Below is the code I've just described.

The code could be optimized so that instead of going through all the bugs it just incrementally modified the cached map, but even unoptimized, it works fine.

When somebody does a search using a tag, I look up the value in the map, get the list of ids, and then fetch those bugs using SQL with "where id in (1, 2, 3...)" clause. 

<pre><code> public static void threadproc_tags(object obj)
 {
 System.Web.HttpApplicationState app = (System.Web.HttpApplicationState)obj;

 SortedDictionary&lt;string,List&lt;int&gt;&gt; tags = new SortedDictionary&lt;string,List&lt;int&gt;&gt;();

 // update the cache
 DbUtil dbutil = new DbUtil();
 DataSet ds = dbutil.get_dataset("select bg_id, bg_tags from bugs where isnull(bg_tags,'') &lt;&gt; ''");

 foreach (DataRow dr in ds.Tables[0].Rows)
 {
 string[] labels = btnet.Util.split_string_using_commas((string) dr[1]);

 // for each tag label, build a list of bugids that have that label
 for (int i = 0; i &lt; labels.Length; i++)
 {

 string label = normalize_tag(labels[i]);

 if (label != "")
 {
 if (!tags.ContainsKey(label))
 {
 tags[label] = new List&lt;int&gt;();
 }

 tags[label].Add((int)dr[0]);
 }
 }
 }

 app["tags"] = tags;

 }
</code></pre>

blocks|key|1205310|text|关于如何处理标记的快速说明：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1205311|标记系统可以是非常严格定义的标记，创建新标记需要显式的额外工作(比如gmail标签)，也可以是非常松散的系统，鼓励尽可能多地添加标记(比如flickr，或者标记音频内容，其中转录可以直接作为标记应用)。|1205312|一般来说，一种易于索引的媒体(文本！)应该有一个更严格的系统，因为内容已经是可搜索的。更多的附加标签只用于分类，只有当不同的用户将事物大致分配到相同的类别时，分类才会有帮助。如果你有原始的文本，它应该几乎需要一个上帝的行为来创建一个新的标签。|1205313|另一方面，更难索引的媒体(图像、视频、音频)应该有一个灵活的系统，鼓励许多标签，因为它们和其他元数据是搜索时的唯一希望。|1205314|这一点很重要，因为您想要的数据库架构可能会在一定程度上改变，这取决于您所发现的范围的哪一端。|1205315|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

A quick note on how to handle tags:
Tagging systems can vary from very rigidly defined tags, where creating new ones require explicit extra work (think gmail labels) to very loose systems where adding as many tags as possible is encouraged (think flickr, or tagging audio content where a transcription may be applied directly as tags).
In general, an easily-indexable media (text!) should have a more rigid system, since the content is already searchable. Additional tags exist more for categorization only, and categorization is only helpful when different users are broadly assigning things into the same categories. If you have raw text, it should take nearly an act of God to create a new tag.
On the other hand, media that's more difficult to index (images, video, audio) should have a flexible system that encourages many tags, since they and other metadata are your only hope when searching.
This is important because the database schema you want could change somewhat depending on which end of that spectrum you find yourself.

I have a photo website and i want to support tags as my original category bucketing is starting to fail (some pictures are family and vacations, or school and friends). Is there an agreed tagging db schema? 

I still want to support having photos as part of an album.

Right now i have a few tables:

Photos

<ul>
<li>PhotoID</li>
<li>PhotoAlbumID</li>
<li>Caption</li>
<li>Date</li>
</ul>

Photo Album

<ul>
<li>AlbumID</li>
<li>AlbumName</li>
<li>AlbumDate</li>
</ul>

Is there an agreed ideal schema for tagging

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个照片网站，我想支持标签，因为我原来的类别，水桶正开始失败(一些照片是家人和假期，或学校和朋友)。是否有一致的标记db架构？我仍然支持把照片作为相册的一部分。现在我有几张桌子：照片PhotoIDPhotoAlbumID标题日期相册AlbumIDAlbumNameAlbumDate

问是否有一致同意的理想标记模式？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否有一致同意的理想标记模式？EN