database design question here.
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.
For instance, if a user entered.
"I like to have #lunch at #sizzler"
I would advise going with a typical many-to-many-relationship between messages and tags.
That would mean you need 3 tables.
messagesthemselves (minimal requirement: columns
tags(minimal requirement here is: columns
tagMessagesRelationsto make the connections between messages and tags (via foreign keys
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).
That then enables you to
SELECT COUNT(*) FROM tags)
INNER JOIN tagMessagesRelations ON tags.ID = tagMessagesRelations.tagID
INNER JOIN messages ON tagMessagesRelations.messageID = messages.ID
GROUP BY messages.UserID