Storing Tags in Database. Store tag once or many times?

Question

I would like a bit more clarification on the toxi method of storing tags in a database – mentioned elsewhere on SO.

The database schema is:

Table: Item
Columns: ItemID, Title, Content

Table: Tag
Columns: TagID, Title

Table: ItemTag
Columns: ItemID, TagID

This is probably a stupid question (but I don't know the answer)... Should each entry, in the Table Tag, have a unique title. i.e. I only store a tag once or store it every time I use it?

To illustrate, which of the two tables below should I end up with:

TagID     Title
1         Fish
2         Cat
3         Dog

or

TagID     Title
1         Fish
2         Fish
3         Cat
4         Fish
5         Dog
6         Cat

If using the first table, before entering the tag I would first have to run and sql statement to find out if it exists, correct?

Any help will be appreciated. I had my fingers burn recently due to hacking together and indexing, want to start getting the basics correct.

Answer 1

The basics are that you need to store tags like you're showing in first case. It's good for checking if tag exists (since in second case for existing tags your db would return as many rows as there are those appearances of tag) and good for retrieving items by tag (select item id by one tag id is better than selecting item ids by a set of tag_id which has same representational meaning).

If you had burnt your fingers because of indexing - you should always check how query is being executed (for mysql it's EXPLAIN/DESCRIBE SELECT).