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:

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?
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.
Is there a "proper" DB term for such a table describe in #2?

Almost same as: http://stackoverflow.com/questions/20856/how-do-you-recommend-implementing-tags-or-tagging — Kamil Lach, Nov 27 '11 at 08:32
Link possibly answers question #1. Questions #2 and #3 are important to me, too. — StackOverflowNewbie, Nov 27 '11 at 08:39

score 0 · Answer 1 · answered Nov 27 '11 at 08:44

0

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';

answered Nov 27 '11 at 08:44

Mahmoud Gamal

78,257
17
139
164

Any "gotchas" I need to worry about with `INSERT IGNORE`? And for #3, I was looking for a "proper" term to refer to such tables. For example, I've heard people call "foo_tag" as "mapping table." Looking for something similar. – StackOverflowNewbie Nov 27 '11 at 08:49
I think there are no "gotchas" for using it just see this: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update ,and for #3 If you just asking about naming convention I think it might be as SO named it just `Tags` see the Db Structure on the right in this page: http://data.stackexchange.com/stackoverflow/query/new. – Mahmoud Gamal Nov 27 '11 at 08:56

score 0 · Answer 2 · answered Nov 27 '11 at 08:46

#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];
}

score 0 · Accepted Answer · answered Nov 27 '11 at 08:52

0

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.

answered Nov 27 '11 at 08:52

hakre

193,403
52
435
836

`tag.name` will be UNIQUE. If `bar` is already a record and has a `tag_id` of, say, 10. When trying to insert `bar` again, what is returned? 10??? That's what I want. – StackOverflowNewbie Nov 27 '11 at 08:56
Yes 10, `LAST_INSERT_ID(tag_id)` will be `LAST_INSERT_ID(10)` then, setting the last insert id to 10. Calling the `mysqli_insert_id` (or equivalent from the C API `last_insert_id()`) will return that 10 value then. Just test it. Note that this is MySQL specific. – hakre Nov 27 '11 at 09:00

MySQL: tagging system

3 Answers3