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" 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.
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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
I would advise going with a typical many-to-many-relationship between messages and tags.
That would mean you need 3 tables.
TagId– to make the connections between messages and tags – via foreign keys pointing to
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:
INNER JOIN TagMessageRelations ON Tags.Id = TagMessageRelations.TagId
INNER JOIN Messages ON TagMessageRelations.MessageId = Messages.Id
GROUP BY Messages.UserId