How effective is this Tagging solution?

Question

Im is working on an image sharing site and wants to implement image tagging.

I read Questions # 20856 and # 2504150

I have few problems approaching the above issues. First of all, it's easy to connect an image with a tag. However, retrieving images by tags is not so simple. Not easy, because you will need to get the image-to-tag relationship from one table, and then make a large query with a group of OR statements (one OR for each image).

Before I even explored the topic of tags, I started testing the following method:

These tables are as examples:

Table: Image Columns: ItemID, Title, Tags Table: Tag Columns: TagID, Name 

The Tags column in the Image table takes a line with the tagID tag from the Tag table enclosed in a hyphen (-).

For instance:

 -65-25-105- 

Associates an image with TagID 65.25 and 105.

With this method, it’s easier for me to get images by tag, since I can get a TagID with one request and get all the images using another simple request, for example:

 SELECT * FROM Image WHERE Tags LIKE %-65-% 

So, if I use this method for tags,

How effective is it?

Is LIKE% -65-% query a slow process?

What problems can I face in the future?

Answer 1

For this you need 3 tables.

 Table: Image Columns: ImageId, ItemID, Title Table: Image_Tag Columns: ImageId, TagId Table: Tag Columns: TagID, Name 

Then, to get all the images for the tag that you would use:

 SELECT ImageId, Title FROM Image_Tag LEFT JOIN Image USING (ImageId) WHERE TagId = $TagId 

This is a typical way to handle many-to-many relationships in a relational database. You will probably win by reading about http://en.wikipedia.org/wiki/Database_normalization

Edit: I see that these are already addresses in other issues that you referred to, so I will explain further. The biggest problem I am facing is that you cannot use id column indexing, which makes your queries less efficient. It seems like it would be inconvenient to update. I would rather not try to do this, at least try using a 3-table solution. As soon as he "clicks" on you, you will thank me.