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?
Best authentication solution for RESTful Database Server
Is it possible to serialize and deserialize a void pointer to & from a blob in a database?
Maps: Does calculating distance between 2 points factor in altitude?
And one tag will belong to many items.
Unique Constraint on two fields and their reverse
This implies to me that you'll quite possibly need an intermediary table to overcome the many-to-many obstacle..
Copy records from two database in sqlite
Postgresql one db with multiple schemas vs multiple db with one schema
Table: ItemsIt might be that your web app is insanely popular and need denormalising down the road, but it's pointless muddying the waters too early..
Columns: Item_ID, Item_Title, Content.
How to store and compress data for real time data logging?
Columns: Tag_ID, Tag_Title.
Storing R Objects in a relational database
Columns: Item_ID, Tag_ID.
He tries a few and reports his results, both in terms of ease of constructing common queries, and in terms of performance.
Number of tags, number of tagged items, and number of tags per item were all factors.
The posts are from 2005; I'm not aware of any updates since then..
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..
one item can be associated with multiple tags and one tag can be associated with multiple items.
Those two values should provide a unique composite primary key as well..
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..
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..
Table: Items Columns: Item_ID:int, Title:text, Content:text Table: Tags Columns: Item_ID:int, Tag_Title:text