# Tag schema

**URL:** <https://forums.percona.com/t/tag-schema/96>\
**Category:** Other MySQL® Questions\
**Created:** [October 6, 2006, 4:26am UTC](https://forums.percona.com/t/tag-schema/96 "2006-10-06T04:26:49Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![zanzi](https://avatars.discourse-cdn.com/v4/letter/z/cdc98d/32.png) [@zanzi](https://forums.percona.com/u/zanzi)\
**Post date:** [October 6, 2006, 4:26am UTC](https://forums.percona.com/t/tag-schema/96/1 "2006-10-06T04:26:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [October 6, 2006, 4:38am UTC](https://forums.percona.com/t/tag-schema/96/2 "2006-10-06T04:38:17Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![toasty](https://avatars.discourse-cdn.com/v4/letter/t/22d042/32.png) [@toasty](https://forums.percona.com/u/toasty)\
**Post date:** [October 6, 2006, 7:28am UTC](https://forums.percona.com/t/tag-schema/96/3 "2006-10-06T07:28:16Z")

</div>

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

Hope it’s useful!  
Toasty

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [October 6, 2006, 7:40am UTC](https://forums.percona.com/t/tag-schema/96/4 "2006-10-06T07:40:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lkn2](https://avatars.discourse-cdn.com/v4/letter/l/dc4da7/32.png) [@lkn2](https://forums.percona.com/u/lkn2)\
**Post date:** [October 10, 2007, 2:01am UTC](https://forums.percona.com/t/tag-schema/96/5 "2007-10-10T02:01:12Z")

</div>

How about Freetag?  
[URL][http://code.google.com/p/freetag/[/URL]](http://code.google.com/p/freetag/%5B/URL%5D)

– 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;