Tag schema

[zanzi]

What tag schema is in your opinon the most efficient?

---

1 table:

TABLE items
id (int)
…
tags (text) FULLTEXT search

---

2 tables:

TABLE items
id (int)
…

TABLE tags
item_id (int)
tag (varchar)

---

3 tables

TABLE items
id(int)
…

TABLE tags
item_id (int)
tag_id (int)

TABLE tag_names
id (int)
name (varchar)

Thanks in advance

[Peter]

It of course depends on what you really going to do with it as well as data distribution, such as number of unique tags etc.

Generally I favor using FullText search approach but in case there are just few real tags using tag_id based approach is better.

[toasty]

Funny that this post comes up 30 mins after I was reading this:
http://www.pui.ch/phred/archives/2005/04/tags-database-schem as.html

Hope it’s useful!
Toasty

[Peter]

Yeah. I know that post does not really favors MySQL FullText Search solution.

At large extent because MySQL FullText is slow by itself. But you should do good with other search engines for sure.

Note in real world you would often need more complicated things, such as finding latest, most popular items by tags etc.

If you do sorting inside of MySQL and it comes together with selfjoins etc it might become quite ugly.

[lkn2]

How about Freetag?
[URL]http://code.google.com/p/freetag/[/URL]

– MySQL dump 9.11-- Freetag Structure v2.02---- Table structure for table freetags–CREATE TABLE freetags ( id int(10) unsigned NOT NULL auto_increment, tag varchar(30) NOT NULL default ‘’, raw_tag varchar(50) NOT NULL default ‘’, PRIMARY KEY (id)) TYPE=MyISAM;---- Table structure for table freetagged_objects–CREATE TABLE freetagged_objects ( tag_id int(10) unsigned NOT NULL default ‘0’, tagger_id int(10) unsigned NOT NULL default ‘0’, object_id int(10) unsigned NOT NULL default ‘0’, tagged_on datetime NOT NULL default ‘0000-00-00 00:00:00’, PRIMARY KEY (tag_id,tagger_id,object_id), KEY tag_id_index (tag_id), KEY tagger_id_index (tagger_id), KEY object_id_index (object_id)) TYPE=MyISAM;