为相关标签搜索设置MYSQL数据库的正确方法? [英] Correct way to set up MYSQL database for related tag searches?
问题描述
我有一些人在我的网站上上传艺术品.他们输入标题，标签和文件.
I have people uploading art on my site. They enter titles, tags, and the file.
上传后，我会标记标题，用逗号分隔的标记以及转换后的图像文件的文件路径.
Once it's uploaded i have tags the title, tags separated by commas and the filepath for converted image file.
我现在想通过标签来找到与此相关的最接近的相关技术.因此，要找到最接近的匹配项，我必须爆炸标签并搜索每个标签?服务器上似乎有很多工作.我想知道有人能告诉我什么是正确的存储标签和数据的方式，我该怎么做?
I now want to find closest related art by tags to this one. So to find closest match I have to explode the tags and search for each individual one? It seems like a lot of work on the server. I was wondering can anyone tell me what's the correct way to store the tags and data and what search would I have to do?
我必须有一个包含标题和文件路径的表，以及另一个包含艺术品ID和其中一个标签的列的表.意思是如果我有铅笔，动物，野生动植物"，那么标签表中是否会有三行具有相同的艺术品ID?
Would I have to have one table that holds the title, and filepath and another table to hold id of the art and a column of one of the tags. Meaning if I have "pencil, animal, wildlife" I'd have three rows in the tags table with the same art ID ?
推荐答案
只需按数据实体的含义和含义将它们分开即可.对于title，tags和file，听起来您有两个实体:
Just separate out your data entities by what they are and what they mean. For title, tags and file it sounds like you have two entities:
Picture
----------
ID
Title
File
Tag
----------
ID
Name
也就是说，title和file(在您的情况下，我想您是将其存储为文件系统上文件的路径，这很好)是一个实体，而tag是其自己的独立实体.由于每个Picture可以具有多个tag，并且每个tag可以与多个Picture相关，所以这是多对多关系.因此，通常会创建一个支持性的非实体表以将它们链接到数据库中:
That is, the title and the file (in your case I guess you're storing that as the path to the file on the file system, which is fine) are one entity, and a tag is its own separate entity. Since each Picture can have multiple tags and each tag can relate to multiple Pictures, it's a many-to-many relationship. So one would generally create a supporting non-entity table to link them in the database:
PictureTagRelationship
----------
PictureID
TagID
有了它，您可以获得Picture:
SELECT Picture.Title, Picture.File FROM Picture WHERE Picture.ID = ?id
及其标签:
SELECT Tag.ID, Tag.Name FROM Tag
INNER JOIN PictureTagRelationship ON Tag.ID = PictureTagRelationship.TagID
WHERE PictureTagRelationship.PictureID = ?id
(您也可以通过几种方式在单个查询中执行此操作，为简单起见，我将其分为两个.两个查询应该没什么大不了的，但是如果您需要高度优化数据库访问开销或者，如果您真的希望将其作为单个查询，那么我敢肯定您可以做些事情.)
(You can do that in a single query in a couple of ways as well, I just split it into two for simplicity. Two queries shouldn't be a big deal, but if you need to highly optimize your database access overhead or if you really want it to be a single query then I'm sure something can be done.)
或者您可以获取特定标签的所有图片:
Or you can get all the pictures for a specific tag:
SELECT Picture.ID, Picture.Title, Picture.File FROM Picture
INNER JOIN PictureTagRelationship ON Picture.ID = PictureTagRelationship.PictureID
WHERE PictureTagRelationship.TagID = ?id
此设计还可以进行其他调整，还可以使用许多其他方式查看和报告数据.但是，所有这些都是关键点:
There are other tweaks that can be made to this design and plenty of other ways to view and report on the data. But in all of this is one key point:
请勿使用逗号分隔的列表来存储数据.将每个数据实体标准化为自己的结构并进行相应的存储.关系数据库对于这种事情非常有用.但是，每当您将单独的数据元素存储为定界字符串时，就会丢失这些元素的分隔.这使得报告这些数据变得更加困难，与数据进行交互更加困难，很多对其进行更新更加困难，并且对需要支持它的其他人来说也不太直观.
Don't use comma-delimited lists to store data. Normalize each data entity into its own structure and store it accordingly. Relational databases are great for that sort of thing. But any time you store separate data elements as a delimited string, you lose that separation of those elements. This makes it more difficult to report on that data, more difficult to interact with it, a lot more difficult to update it, and less intuitive to anybody else who needs to support it.
请记住，数据库中的任何一个字段都应存储一条信息，而仅 一条信息.如果必须将多个信息填充到单个字段中，则说明您没有正确使用关系数据库.
Just remember that any one field in the database should be storing one piece of information and only one piece of information. If you have to cram multiple pieces of information into a single field then you're not using the relational database properly.
这篇关于为相关标签搜索设置MYSQL数据库的正确方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
