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?

Answers


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


Need Your Help

Whats the best way to implement a simple document management system?

java svn tagging versions document-management

I am planning to build a simple document management system. Preferably built around the java platform. Are there are best practices around this? The requirements are :

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.