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?
Regarding normalization and reusability I would suggest something like this:
Table pictures:
Table tags:
Table tags_pictures:
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.