What is the most efficient way to store tags in a database?
I am implementing a tagging system on my website similar to one stackoverflow uses, my question is - what is the most effective way to store tags so that they may be searched and filtered?
My idea is this:
Table: Items Columns: Item_ID, Title, Content Table: Tags Columns: Title, Item_ID
Is this too slow? Is there a better way?
One item is going to have many tags. And one tag will belong to many items. This implies to me that you'll quite possibly need an intermediary table to overcome the many-to-many obstacle.
Table: Items Columns: Item_ID, Item_Title, Content
Table: Tags Columns: Tag_ID, Tag_Title
Table: Items_Tags Columns: Item_ID, Tag_ID
It might be that your web app is insanely popular and need denormalising down the road, but it's pointless muddying the waters too early.