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?
This question is related to
database
database-design
tags
tagging
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.
I'd suggest using intermediary third table for storing tags<=>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.
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.
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.
I'd suggest using intermediary third table for storing tags<=>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.
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.
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.
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.
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.
I'd suggest using intermediary third table for storing tags<=>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.
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.
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.
I'd suggest using intermediary third table for storing tags<=>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.
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.
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.
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.
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.
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.
Source: Stackoverflow.com