simple tagging

tag.sql

CREATE TABLE tag
(
  id INT(10) unsigned NOT NULL auto_increment,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY tag_name (name)
);

CREATE TABLE tag_type
(
  id INT(10) unsigned NOT NULL auto_increment,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE tag_object
(
  id INT(10) unsigned NOT NULL auto_increment,
  tag_id INT(10) unsigned NOT NULL,
  tag_type_id INT(10) unsigned NOT NULL,
  object_id INT(10) unsigned NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (tag_id) REFERENCES tag(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_type_id) REFERENCES tag_type(id) ON DELETE CASCADE
);