Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

Thanks, Ron

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Go with your first approach, it is pretty much the standard way of modeling this type of relationship. (updated to record user who added the tag)

Items
ItemID (PK)

ItemTags
ItemID (FK)
TagID (FK)

Tags
TagID (PK)

UserTags
ItemID (FK)
UserID (FK)

You could also do this by adding a UserID column to the ItemTags table and allowing duplicate rows for each tag, but the above approach his likely to be more performant.

The second approach seems like a bad idea since it wouldn't give you the flexibility to add more tags in the future and likely would make queries more difficult to write.

As for indexing: Index each of the foreign keys (the primary keys should automatically get indexed).

Use an RDBMS, a NO-SQL solution probably wouldn't give you any benefit for this type of data structure.

Whatever you do, avoid creating multi-value fields with some kind of comma or space delimited values. It is a performance killer and makes queries really hard to write.

share|improve this answer
    
Yes each tag will be applied to an item once, the first time it is set by the user. If the tag is set by another user, it will update the tag_hits for the same tag, as indicated in my question. Thanks –  Ron Oct 9 '11 at 18:54
    
That's a bit confusing. So you are saying you need to have the tag applied multiple times then (if it can be set by another user). I modified my answer to accommodate the hit count with the assumption you don't need to keep track of who all tagged it. –  JohnFx Oct 9 '11 at 18:57
    
Sorry for the confusion JohnFx. You do bring up an pertinent point - I failed to account for the users in my question. How would you modify it to record which user set the tags? A single field in the ItemTags table with a list of all users who chose that tag perhaps? or a record in the table for each user? or another table, UserItemTags, linking each user to an item and the tag they chose? Thanks again –  Ron Oct 9 '11 at 19:26
    
@Ron Avoid multi-value fields at all costs. Another table would be appropriate. I'll modify my answer to demonstrate. BTW: An upvote or accepting the answer is all the thanks I need. –  JohnFx Oct 9 '11 at 21:11
    
It depends on what an "Item" is before you can rule out a NoSQL solution. –  JeffO Oct 10 '11 at 0:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.