Managing picture tags in SQL

I would like to build a database of pictures. Each picture may have 1 or more tags, for example: Paris, April 2010, David.

How would you store this information ? I thought to have a Files table with 1 row per file, and one of the columns would be Tags IDs separated by commas, for example: 2,4,14,15

In other table called Tags I thought to have 1 row per 开发者_如何学Gotag, like this:

Tag ID    Tag Name
------    --------
   1       April
   2       David
   3       Paris

Do you think this is a good idea to manage tags like that ? For example, how would I easily get all tags names of specific picture ?

You should have 3 tables. Files, Tags, and FileTags.

FileTags should have File ID and Tag ID. One row for one tag assignment.

You could then easily query all files with a certain tag:

select distinct f.* from Files f 
join FileTags ft on f.FileID = ft.FileID 
join Tags t on ft.TagID = t.TagID
where t.TagName = 'Paris'

Or all tags for any file:

select distinct t.* from Files f 
join FileTags ft on f.FileID = ft.FileID 
join Tags t on ft.TagID = t.TagID
where f.FileID = 7

What you are suggesting (Tag IDs separated by commas) is considered a no-no for most relational database designs, as this is not normalized.

You should have an additional many-to-many table with TagId and FileId columns.

This means you can store a single tag (say Paris), and associate it with many pictures. And for each picture you will be able to store many tags.

In my opinion, if you comma separate values in a column, you will have a very hard in the future doing a search like "get me all the pictures with a given tag" I'm sure there are many ways, but a standard way of handling this is to have an intermediate many-to-many mapping table that stores PhotoID and TagID which relates which tag belongs to which picture.

I would probably have one table for the pictures and one for tags, than use a many-to-many table binding them together. This way it is easy to lookup either all tags for a picture or all pictures for a tag.

Mysql lets you can get all the tags quite easily even out of the normalized format

SELECT f.FileID, group_concat(t.tag_name) 
FROM Files f 
     JOIN FileTags ft ON f.FileID = ft.FileID 
     JOIN Tags t on ft.TagID = t.TagID
GROUP BY f.FileID

See details here.

If you are chasing very last bit of performance and are ready to sign that you will not object to shortcomings you could consider using set types.

Caveat: both of these options lock you into specifics of a particular RDBMS implementation (here mysql) as other RBMSes might not user the same syntax or even have the above features (so in perfect world none of the above suggestions are good).

Be sure to read on limitations regarding the set data type, for example it is not really fun to have to extensively redesign application because you realize that you have to have more that 64 tags.

I would have 3 tables:

1. File with columns "Id", "Path", ...
2. Tag with columns "Id", "Name" ...
3. FileTag with columns "FileId", "TagId", ...

Then I would create foreign keys:

• "FileId" is foreign key from File table
• "TagId" would be foreign key from Tag table

Of course implementation details are up to RDBMS of your choice.