What is the most efficient way to store tags in a database

Question

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

User · Answer

Actually I believe de-normalising the tags table might be a better way forward, depending on scale.

This way, the tags table simply has tagid, itemid, tagname.

You'll get duplicate tagnames, but it makes adding/removing/editing tags for specific items MUCH more simple. You don't have to create a new tag, remove the allocation of the old one and re-allocate a new one, you just edit the tagname.

For displaying a list of tags, you simply use DISTINCT or GROUP BY, and of course you can count how many times a tag is used easily, too.

User · Answer

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

User · Answer

Items should have an  ID  field  and Tags should have an  ID  field  Primary Key  Clustered    Then make an intermediate table of ItemID TagID and put the  Perfect Index  on there

User · Answer

You can t really talk about slowness based on the data you provided in a question  And I don t think you should even worry too much about performance at this stage of developement  It s called premature optimization   However  I d suggest that you d include Tag ID column in the Tags table  It s usually a good practice that every table has an ID column

User · Answer

If you don t mind using a bit of non-standard stuff  Postgres version 9 4 and up has an option of storing a record of type JSON text array   Your schema would be   Table  Items Columns  Item ID int  Title text  Content text  Table  Tags Columns  Item ID int  Tag Title text     For more info  see this excellent post by Josh Berkus  http   www databasesoup com 2015 01 tag-all-things html  There are more various options compared thoroughly for performance and the one suggested above is the best overall

User · Answer

Actually I believe de-normalising the tags table might be a better way forward, depending on scale.

This way, the tags table simply has tagid, itemid, tagname.

You'll get duplicate tagnames, but it makes adding/removing/editing tags for specific items MUCH more simple. You don't have to create a new tag, remove the allocation of the old one and re-allocate a new one, you just edit the tagname.

For displaying a list of tags, you simply use DISTINCT or GROUP BY, and of course you can count how many times a tag is used easily, too.

User · Answer

You can t really talk about slowness based on the data you provided in a question  And I don t think you should even worry too much about performance at this stage of developement  It s called premature optimization   However  I d suggest that you d include Tag ID column in the Tags table  It s usually a good practice that every table has an ID column

User · Answer

Items should have an  ID  field  and Tags should have an  ID  field  Primary Key  Clustered    Then make an intermediate table of ItemID TagID and put the  Perfect Index  on there

User · Answer

If you don t mind using a bit of non-standard stuff  Postgres version 9 4 and up has an option of storing a record of type JSON text array   Your schema would be   Table  Items Columns  Item ID int  Title text  Content text  Table  Tags Columns  Item ID int  Tag Title text     For more info  see this excellent post by Josh Berkus  http   www databasesoup com 2015 01 tag-all-things html  There are more various options compared thoroughly for performance and the one suggested above is the best overall

User · Answer

Actually I believe de-normalising the tags table might be a better way forward, depending on scale.

This way, the tags table simply has tagid, itemid, tagname.

You'll get duplicate tagnames, but it makes adding/removing/editing tags for specific items MUCH more simple. You don't have to create a new tag, remove the allocation of the old one and re-allocate a new one, you just edit the tagname.

For displaying a list of tags, you simply use DISTINCT or GROUP BY, and of course you can count how many times a tag is used easily, too.

User · Answer

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

User · Answer

I d suggest using intermediary third table for storing tags lt   items associations  since we have many-to-many relations between tags and items  i e  one item can be associated with multiple tags and one tag can be associated with multiple items  HTH  Valve

User · Answer

If space is going to be an issue  have a 3rd table Tags Tag Id  Title  to store the text for the tag and then change your Tags table to be  Tag Id  Item Id    Those two values should provide a unique composite primary key as well

User · Answer

If space is going to be an issue  have a 3rd table Tags Tag Id  Title  to store the text for the tag and then change your Tags table to be  Tag Id  Item Id    Those two values should provide a unique composite primary key as well

User · Answer

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

User · Answer

Items should have an  ID  field  and Tags should have an  ID  field  Primary Key  Clustered    Then make an intermediate table of ItemID TagID and put the  Perfect Index  on there

User · Answer

Items should have an  ID  field  and Tags should have an  ID  field  Primary Key  Clustered    Then make an intermediate table of ItemID TagID and put the  Perfect Index  on there

User · Answer

I d suggest using intermediary third table for storing tags lt   items associations  since we have many-to-many relations between tags and items  i e  one item can be associated with multiple tags and one tag can be associated with multiple items  HTH  Valve

User · Answer

If space is going to be an issue  have a 3rd table Tags Tag Id  Title  to store the text for the tag and then change your Tags table to be  Tag Id  Item Id    Those two values should provide a unique composite primary key as well

User · Answer

Actually I believe de-normalising the tags table might be a better way forward, depending on scale.

This way, the tags table simply has tagid, itemid, tagname.

You'll get duplicate tagnames, but it makes adding/removing/editing tags for specific items MUCH more simple. You don't have to create a new tag, remove the allocation of the old one and re-allocate a new one, you just edit the tagname.

For displaying a list of tags, you simply use DISTINCT or GROUP BY, and of course you can count how many times a tag is used easily, too.

User · Answer

I d suggest using intermediary third table for storing tags lt   items associations  since we have many-to-many relations between tags and items  i e  one item can be associated with multiple tags and one tag can be associated with multiple items  HTH  Valve

User · Answer

You can t really talk about slowness based on the data you provided in a question  And I don t think you should even worry too much about performance at this stage of developement  It s called premature optimization   However  I d suggest that you d include Tag ID column in the Tags table  It s usually a good practice that every table has an ID column

User · Answer

I d suggest using intermediary third table for storing tags lt   items associations  since we have many-to-many relations between tags and items  i e  one item can be associated with multiple tags and one tag can be associated with multiple items  HTH  Valve

User · Answer

You can t really talk about slowness based on the data you provided in a question  And I don t think you should even worry too much about performance at this stage of developement  It s called premature optimization   However  I d suggest that you d include Tag ID column in the Tags table  It s usually a good practice that every table has an ID column

User · Answer

If space is going to be an issue  have a 3rd table Tags Tag Id  Title  to store the text for the tag and then change your Tags table to be  Tag Id  Item Id    Those two values should provide a unique composite primary key as well

User · Answer

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

[database] What is the most efficient way to store tags in a database?

Examples related to database

Examples related to database-design

Examples related to tags

Examples related to tagging