Search code examples
mysqlforeign-keysinnodbtaggingfolksonomy

Toxi mysql performance, tagging. Help understanding

I am using the Toxi scheme for tagging items on my website. Being quite new to mysql let alone tagging, I'm just doing a sanity check. Here is my table initialization script.

CREATE TABLE IF NOT EXISTS Items (
  item_id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  item_name VARCHAR(45) NULL ,
  media_type VARCHAR(20) NULL ,
  file VARCHAR(45) NULL ,
  description VARCHAR(500) NULL ,
  PRIMARY KEY (item_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Tags (
 tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
 tag_text VARCHAR(25) NOT NULL ,
 PRIMARY KEY (tag_id) ,
 UNIQUE INDEX (tag_text)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Item2Tag (
 item_id INT UNSIGNED NOT NULL ,
 tag_id INT UNSIGNED NOT NULL  ,
 PRIMARY KEY (item_id, tag_id) ,
 INDEX (tag_id) ,
 FOREIGN KEY fk_Item (item_id) REFERENCES Items (item_id) ,
 FOREIGN KEY fk_Tag (tag_id) REFERENCES Tags (tag_id)
) ENGINE=InnoDB;

http://forge.mysql.com/wiki/TagSchema

Question 1

Is my understanding correct that there is an entry in the "Item2Tag" table for every "item_id" to "tag_id"? It just seems like that is going to be a huge table when I have ~3000 items and each item could have ~5 tags. Is that not a concern/not really a big table?

Question 2

Could someone help me understand importance of having Foreign Keys/References? Why do I need those and what do they do?

Solution

  • Question 1: Yes, that's correct.

    Question 2: You don't really need them for Toxi schema as far as I know. But they help you avoid having entries in the reference table while not in the item table. It's more of a constrain to prevent headaches than a need. ie. You delete item number x, the entry associated to item number x also gets deleted.