Search code examples
databasedatabase-designtagsdata-modelingtagging

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?

Solution

  • 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.

    Something like:

    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 very very popular and need de-normalizing down the road, but it's pointless muddying the waters too early.