在数据库中存储标记。存储标签一次或多次？ [英] Storing Tags in Database. Store tag once or many times?

问题描述

我想对数据库中存储标签的toxi方法进行更多说明。

 表：项目
列：ItemID，标题，内容
 
表：标记
列：TagID，Title 
 
表：ItemTag 
列：ItemID，TagID 
  

这可能是一个愚蠢的问题（但我不知道答案）...每个条目，在表标记，有一个唯一的标题。例如，我只存储一个标签一次或存储它每次我使用它？

为了说明，下面的两个表中我应该结束：

  TagID标题
 1鱼
 2 Cat 
 3狗
  

或

  TagID标题
 1鱼
 2鱼
 3猫
 4鱼
 5狗
 6 Cat 
  

如果使用第一个表，在输入标签之前，我首先必须运行和sql语句，以确定它是否存在，正确吗？

任何帮助将不胜感激。

解决方案

我的手指最近烧坏了，基本的是，你需要存储标签，如你在第一种情况下显示。它适用于检查标记是否存在（因为在第二种情况下，对于现有标记，您的数据库将返回与标记的外观一样多的行），并且有利于通过标记检索项目（通过一个标记ID选择项目ID比选择项目

如果你因为索引而烧毁你的手指，你应该总是检查如何执行查询（对于mysql EXPLAIN / DESCRIBE SELECT ）。

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.

解决方案

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

这篇关于在数据库中存储标记。存储标签一次或多次？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！