在数据库中存储标签的最佳实践? [英] Best practice for storing tags in a database?

问题描述

我开发了一个网站，该网站使用标签(关键字)对照片进行分类. 现在，我在MySQL数据库中拥有的表具有以下结构:

I developed a site that uses tags (key words) in order to categorize photographs. Right now, what I have in my MySQL database is a table with the following structure:

image_id (int)
tag      (varchar(32))

每次有人标记图像(如果标记有效并且具有足够的投票权)，图像就会被添加到数据库中.我认为这不是最佳的处理方式，因为现在有5000多个带有标签的图像，标签表中有40000多个条目.我担心这会开始影响性能(如果尚未影响性能的话).

Every time someone tags an image (if the tag is valid and has enough votes) it's added to the database. I think that this isn't the optimal way of doing things since now that I have 5000+ images with tags, the tags table has over 40000 entries. I fear that this will begin to affect performance (if it's not already affecting it).

我考虑了另一种结构，认为获取与特定图像相关联的标签会更快，但是当我想要获得所有标签时，或者对于最受欢迎的标签，这似乎太可怕了:

I considered this other structure thinking that it'd be faster to fetch the tags associated to a particular image but then it looks horrible for when I want to get all the tags, or the most popular one for instance:

image_id (int)
tags     (text) //comma delimited list of tags for the image

是否有正确的方法或者两种方法大致相同? 有想法吗?

Is there a correct way of doing this or are both ways more or less the same? Thoughts?

推荐答案

使用多对多表将TAG记录链接到IMAGE记录:

Use a many-to-many table to link a TAG record to an IMAGE record:

DROP TABLE IF EXISTS `example`.`image`;
CREATE TABLE  `example`.`image` (
  `image_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`image_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

TAG

DROP TABLE IF EXISTS `example`.`tag`;
CREATE TABLE  `example`.`tag` (
 `tag_id` int(10) unsigned NOT NULL auto_increment,
 `description` varchar(45) NOT NULL default '',
 PRIMARY KEY  (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

IMAGE_TAG_MAP

DROP TABLE IF EXISTS `example`.`image_tag_map`;
CREATE TABLE  `example`.`image_tag_map` (
 `image_id` int(10) unsigned NOT NULL default '0',
 `tag_id` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (`image_id`,`tag_id`),
 KEY `tag_fk` (`tag_id`),
 CONSTRAINT `image_fk` FOREIGN KEY (`image_id`) REFERENCES `image` (`image_id`),
 CONSTRAINT `tag_fk` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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