将标签存储在数据库中。商店标签一次还是多次?

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

我想进一步说明toxi在数据库中存储标签的方法——在其他地方也提到过。

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

或

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?

如果使用第一个表，那么在输入标记之前，我必须先运行sql语句以确定它是否存在，对吗?

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

如有任何帮助，我们将不胜感激。我的手指最近被烧了，因为一起黑客和索引，想要开始得到基本的正确。

3 个解决方案

#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).

基本的是，您需要像在第一种情况中显示的那样存储标记。检查标签是否存在很好(因为在第二个案例对现有标记你的分贝会返回多行有这些表象的标记)和良好的检索项的标签(选择项id标签id是比选择项id的一组相同的tag_id表征意义)。

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).

如果你因为索引而烫伤了手指——你应该经常检查查询是如何执行的(对于mysql，它是EXPLAIN/DESCRIBE SELECT)。

#2

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

如果“Fish”和“Fish”是相同的标签，那么您应该只在标记表中有一次。

So, I would go with your first solution -- which, indeed, implies doing a select before your insert, to determine whether the tag already exists or not ; and, if it exists, using its already existing TagID for the link between the item and the tag, in the ItemTag table.

因此，我将使用您的第一个解决方案——实际上，这意味着在插入之前进行选择，以确定标记是否已经存在;如果存在，则在ItemTag表中使用其已存在的TagID作为项目和标记之间的链接。

Actually, this is the reason for which the ItemTag exists : it is an association table, which stores correspondances between items and tag : for each item, you can have several tags, and, for each tag, you can have several items.

实际上，这就是ItemTag存在的原因:它是一个关联表，它存储项目和标签之间的对应关系:对于每个条目，您可以有多个标记，对于每个标记，您可以有多个项。

This will also, btw, make things easier to get a list of items that are attached to a specific tag.

另外，顺便说一下，这也可以让你更容易地获得附加到特定标签上的条目的列表。

#3

You should have the tags only once in the tag table; the whole point of the ItemTag table is to provide you with an n:m association (each item having multiple tags, and each tag belonging to multiple items).

您应该只在标记表中使用一次标记;ItemTag表的整个要点是为您提供一个n:m关联(每个项目具有多个标记，每个标记属于多个项目)。

If you would repeat the tag titles, you could simplify the structure by having the Tag table use ItemIDs right away, not tag IDs.

如果您要重复标记标题，您可以通过让标记表立即使用itemid(而不是标记id)来简化结构。

#1