Database design for apps using “hashtags”

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

That would mean you need 3 tables.

• 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).

This way you can

• 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