MySQL: tagging system

Question

Assume I have a table that can have 0 or more tags:

TABLE: foo
- foo_id (PK)

TABLE: tag
- tag_id (PK)
- name

TABLE: foo_tag
- foo_tag_id (PK)
- foo_id (FK)
- tag_id (FK)

Questions:

1. The above is the most common and simplest way to implement a tagging system that I have seen. I do realize that there are potential problems with tagging systems in terms of scalability. Am I going to have that here?
2. Is there a way to do something like this in PHP or SQL: insert a new tag "bar". If "bar" does not exist, add it to the tag table and return the last inserted id. If "bar" does exist, do not add it and return the tag_id for it.
3. Is there a "proper" DB term for such a table describe in #2?

Answer 1

Please see http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html for a workaround to get the last ID on update:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

This will update the last insert id to the value of the updated value. In your case:

INSERT INTO tag (name) VALUES ('bar')
  ON DUPLICATE KEY UPDATE tag_id=LAST_INSERT_ID(tag_id);

tag.name needs to be a unique key.

Answer 2

If I don't miss something, If your problem with this tagging system is #2 in your question which is insert a new tag "bar" if it is not exists, then try using INSERT IGNORE this will skip inserting the tag if it exist, and I think this is the term you are looking for in #3 like this:

INSERT IGNORE INTO Tag set name = 'bar';

Answer 3

#2 I would set unique key to tag.name. Assuming you have auto increment on tag_id, you may try this:

function tagId($tag) {
  mysql_query("INSERT IGNORE INTO tag SET name='$tag'");
  if(mysql_affected_rows()) return mysql_insert_id();
  $result = mysql_query("SELECT tag_id FROM tag WHERE name='$tag'");
  if(!$result) return null;
  $data = mysql_fetch_row($result);
  if($data) return $data[0];
}