CODEWITHSUNDEEP
databaseweb
VladimirS
VladimirS

Reputation: 37

How to store tags for images?

There are web-site where you can find and download beautiful wallpapers. They are divided into different groups, for example:

There are a lot of images and different tags. Amount of pictures and tags are rapidly increases from day to day.

A client wants to find a picture which presents both mountains and forest. So he chooses tags "mountains" and "forest". A web-developer has already put picture on a server and link it with the tags. But how? There might be four variants:

  1. Tags are stored in one cell like that: "mountains|forest". In order to search for them it is necessary to split string value.
  2. Tags are in array which was serialized and then saved in cell.
  3. There are data base tables. One contains images ids and other properties. Other contains tags and it's ids. So tag and images are linked to each other by ids.
  4. Completely different method: to use special source to store images and tags for them.

What way is the best? Are there any other ways to solve such task?

Upvotes: 1

Views: 1068

Answers (2)

Rejected
Rejected

Reputation: 4491

What you're looking for is a simple many to many relationship in the database. Options 1 and 2 are bad options, and you shouldn't use them for a myriad of reasons.

We'll use a simple example for this. First, you have a table with images in them. It has three columns, id, image_name, image_data. You also have a table with all your tags in them. It has two columns, id, and tag.

Your many-to-many table will then have two columns named image_id, and tag_id.

So, lets say you have the following images:

1    'house_in_hills.png'    BLOB  
2    'mountains.png'         BLOB  
3    'old_shoes.png'         BLOB

And the following tags

1    'Mountains'
2    'House'
3    'Person'

Your many-to-many table would have entries that would look like:

1    1  # Image 'house_in_hills.png' has tag 'Mountains'
1    2  # Image 'house_in_hills.png' has tag 'House'
2    1  # Image 'mountains.png' has tag 'Mountains'

And so on. I'd highly recommend reading up on Many-to-Many relationships.

Upvotes: 2

laindir
laindir

Reputation: 1710

The relationship between images and tags is called many-to-many. An image can have many tags, and a tag can be applied to many images. In a normalized database, you might represent this with the following tables:

image
======
id
file_path
...

tag
====
id
name
...

image_tag
=========
id
image_id
tag_id

Breaking out the relationship this way allows you to independently deal with each type of resource: images, tags, or a link between the two.

Upvotes: 1

Related Questions