使用“标签"的应用程序的数据库设计； [英] Database design for apps using "hashtags"

问题描述

这里的数据库设计问题.

database design question here.

假设我们有一个webapp或使用#标签的20-40个单词注释的东西.存储用户主题标签的最佳方法是什么.

Say we had a webapp or something that uses hashtags for 20-40 word notes. What is the best way to store a user's hashtags.

例如，如果用户输入. "I like to have #lunch at #sizzler"我们将句子存储为文本，将标签存储为JSON，逗号分隔列表或其他某种机制.

For instance, if a user entered. "I like to have #lunch at #sizzler" we would store the sentence as text and we could store the hashtags as JSON, a comma separated list or some other mechanism.

还值得指出的是，这些标签需要可搜索，例如有多少人对午餐进行了哈希标签等等.

Its also worth pointing out that the tags need to be searchable, such as how many people have been hash tagging lunch, etc.

关于此事的建议会很棒，在将可变大小的输入存储在mysql中时，我总是有些困惑.每个笔记中可以有无限数量的主题标签，最好的存储方式是什么?

Advise on the matter would be great, I always get a bit stumped when it comes to storing variable sized inputs in mysql. There can be an infinite number of hashtags per note, what is the best way to store them?

推荐答案

我建议在消息和标签之间使用典型的多对多关系.

I would advise going with a typical many-to-many-relationship between messages and tags.

那将意味着您需要3张桌子.

That would mean you need 3 tables.

• Messages(列Id，UserId和Content)
• Tags(列Id和TagName)
• TagMessageRelations(列:MessageId和TagId-通过指向Messages.Id/Tags.Id的外键在消息和标签之间建立连接)

• Messages (columns Id, UserId and Content)
• Tags (columns Id and TagName)
• TagMessageRelations (columns: MessageId and TagId - to make the connections between messages and tags - via foreign keys pointing to Messages.Id / Tags.Id)

这样，您就不会多次存储标签，而只会创建与消息的新关系(当然，如果该标签已经存在于标签表中).

That way you do not store a tag multiple times but only create a new relation to a message (if that tag already exists in the tag-table of course).

通过这种方式

• 轻松地计算有多少标签(SELECT COUNT(*) FROM Tags)
• 每个标签仅保存一次，并且可以轻松为索引搜索标签
• 或计算每个用户使用某个标签的次数-例如:

• easily count how many tags there are (SELECT COUNT(*) FROM Tags)
• only save each tag once and search for tags can be easily indexed
• or count how many times a certain tag was used per user - for example:

SELECT COUNT(*) FROM Tags INNER JOIN TagMessageRelations ON Tags.Id = TagMessageRelations.TagId INNER JOIN Messages ON TagMessageRelations.MessageId = Messages.Id GROUP BY Messages.UserId

SELECT COUNT(*) FROM Tags INNER JOIN TagMessageRelations ON Tags.Id = TagMessageRelations.TagId INNER JOIN Messages ON TagMessageRelations.MessageId = Messages.Id GROUP BY Messages.UserId

这篇关于使用“标签"的应用程序的数据库设计；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！