Tag storage in the database. Store tag one or more times?

Question

I would like to clarify a little the toxicity method of storing tags in a database mentioned elsewhere on SO.

Database Schema:

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) ... If each entry in the Tag Tag has a unique title. those. Am I only saving the tag once or am I saving it every time I use it?

To illustrate which of the two tables below should contain:

 TagID Title 1 Fish 2 Cat 3 Dog 

or

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

If you use the first table, before entering the tag, I must first run the sql statement to find out if it exists, right?

Any help would be appreciated. Recently, I had my fingers due to hacking and indexing, you want the source data to be correct.

Answer 1

The basics are that you need to store tags, as shown in the first case. This is useful for checking for the presence of a tag (since in the second case for existing tags, your db will return as many lines as there are tags) and is useful for retrieving elements by tag (choosing an element identifier with a single tag identifier is better than selecting an ids element using the tag_id set, which has the same idea).

If you burned your fingers due to indexing, you should always check how the query is executed (for mysql it EXPLAIN/DESCRIBE SELECT ).

Answer 2

If " Fish " and " Fish " are the same tag, you should probably have it only once in the Tag table.

So, I would go with your first solution, which really involves executing select before your insert , to determine if the tag already exists or not; and, if it exists, using the already existing TagID for the link between the item and the tag in the ItemTag table.

In fact, this is the reason ItemTag exists: it is an association table that stores correspondences between elements and a tag: for each element you can have several tags, and for each tag you can have several elements.

It will also, by the way, make it easier to get a list of items attached to a specific tag.

Answer 3

You must have tags only once in the tag table; the entire point of the ItemTag table should provide you with an n: m relationship (each item has multiple tags and each tag belonging to multiple items).

If you repeat the tag names, you can simplify the structure by specifying the tag table directly using the ItemID, rather than identifier tags.