Not the answer you need?
Register and ask your own question!

Tag schema

zanzizanzi EntrantCurrent User Role Beginner
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

Comments

  • PeterPeter Percona CEO Percona Moderator Role
    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.
  • toastytoasty Contributor Inactive User Role Beginner
    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
  • PeterPeter Percona CEO Percona Moderator Role
    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.
  • lkn2lkn2 Entrant Current User Role Beginner
    How about Freetag?
    http://code.google.com/p/freetag/


    -- 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;</pre>
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.