Should I store tags in a text field or in separate

2019-08-16 23:13发布

I have a table with rows that goes like this:

id : path : tags
1 : pictures/pic1.jpg : car bmw
3 : pictures/pic2.jpg : cat animal pussy
4 : pictures/pic3.png : gun

Basically it's a gallery where I store paths to pictures in a table so i can list them on a webpage.

The user has an option to search for certain pictures based on keywords he provides. So for example he enters "car gun" into the search field and all pictures which have one of those tags are pulled for him. Basic tag search:

SELECT * FROM pictures WHERE tags LIKE '%car%' OR tags LIKE '%gun%'

Now what I would like to know is, what is the better solution for storing tags? Is it the one described above or this one:

Table pictures:

id : path
1 : pictures/pic1.jpg
3 : pictures/pic2.jpg
4 : pictures/pic3.png

Table tags:

pic_id : tag
1 : car
1 : bmw
4 : gun
..and so on..

Tags are stored in a separate table here and each tag has its own row, so during the search I will perform JOIN and search for matches that way.

Which solution is better?

标签: php mysql web
2条回答
三岁会撩人
2楼-- · 2019-08-16 23:36

Regarding normalization and reusability I would suggest something like this:

Table pictures:

id : path
1 : pictures/pic1.jpg
2 : pictures/pic2.jpg
3 : pictures/pic3.png

Table tags:

tag_id : tag
1 : car
2 : bmw
3 : gun

Table tags_pictures:

id : tag_id : pic_id
1 : 1 : 1
2 : 2 : 1
3 : 3 : 3
查看更多
疯言疯语
3楼-- · 2019-08-16 23:47

The best approach in these cases is to create an intermediate table for the tags per images, establishing a many to many relationship.

Then you will have three tables: one for storing the image with its id and attributes, another one for save the tag and its id, and finally a table which saves the image id and the tag id for all the tags.

查看更多
登录 后发表回答