1

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?

Juan Cortés
  • 20,634
  • 8
  • 68
  • 91
Alan_m
  • 75
  • 7
  • you might want to look at my "simple" answer here: http://stackoverflow.com/questions/3534597/rewriting-mysql-select-to-reduce-time-and-writing-tmp-to-disk/3535735#3535735 which has 125 million Item2Tags (poster_category) with a "cold" query runtime of 0.02 seconds – Jon Black Nov 15 '10 at 00:00

1 Answers1

1

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.

Juan Cortés
  • 20,634
  • 8
  • 68
  • 91
  • Thanks for expanding on your answer for Question 2. It sounds like having the foreign keys would be beneficial. Are they low maintenance? Meaning, as long as my tables are created that way do I have to change/manage the foreign keys anymore? – Alan_m Nov 14 '10 at 23:16
  • Nope, unless you want to drop them which would imply a single query such as `ALTER TABLE DROP FOREIGN KEY ` – Juan Cortés Nov 14 '10 at 23:19
  • These foreign keys are working out great. They prevent me from doing "stupid" things to my tables. Thanks @thisMayhem. – Alan_m Nov 15 '10 at 00:24