I'm creating a system where items will have one or more tags.
Now there will be an item table that will store each item, and a tags table, that will store all tags used in the system.
My question is: What is the best design I can have that will allow me to link items to their respective tags, and record how many times an item received a particular tag from a user?
For example, I'm wondering if I should create a third table that would associate a particular item with a particular tag and how many times that tag was selected. The fields would be like this: item_id, tag_id, tag_hits - where tag_hits is the number of times the tag was used. This solutions would associate all items with their tags in a single table.
Or the other solution I'm thinking of is to create a table for each item and record each tag and the amount of the time the tag was used. So, for itemX I would create a table itemX and the fields would be: tag_id, tag_hits. For another itemY, I would create a table called itemY with the same fields. So for every item, there would be a table associating it with its tags. Now there could be hundreds of thousands of items in this system.
I suppose the developers of this website had to make similar consideration because each question is tagged. And you can browse questions by tag. My system will have similar functionality.
Can someone recommend a design solution, taking into consideration all that I have said, and performance consideration. Please indicate where you think indexing might help also. I use SQL databases in my development, but if you think a no-SQL solution would do it, please suggest a design along these lines. please be very specific.