Pictures & Tags - Database Design Question

Question

I have 3 tables which allows me to get all tags of some picture, or all pictures with a specific tag.

I would like also to know the number of times each tag exist. How could I get this info using MySQL ?

Is it worth to add a "Count" column to the Tags table to hold this info ?

Files Table

File ID    File Name    ...
-------    ---------
   1         a.jpg      ...
   2         b.png      ...
   3         c.jpg      ...
   .           .
   .           .
   .           .

Tags Table

Tag Name    Tag Creator     ...
--------    -----------  
 David        david         ...
 2010         julia         ...
   .            .
   .            .
   .            .

FilesTags Table

File ID    Tag Name
-------    --------  
   1        2010
   1        April
   1        David
   2        2010
   2        Julia
   3        Friends
   .          .
   .          .
   .          .

Answer 1

To find the number of times tag "ABC" occurs just do

SELECT COUNT(*) FROM FilesTags Table WHERE Tag Name = "ABC";

Hope this helps.

Answer 2

SELECT FilesTags.Tag_name, COUNT(*) as number_of_occurences
FROM FilesTags
GROUP BY FilesTags.Tag_name;

Answer 3

Zaki's response answers one of your questions but not the other one.

"Is it worth to add a "Count" column to the Tags table to hold this info ?"

It really depends on what you are going to do with the counts. Which in turn depends on the nature of your web site.

SO makes displays the counts alongside the tags; that's partly because its target audience is geeks and geeks like numbers. But also the count is useful information when it comes to tagging a question. Plus there's the Taxonomist's badge to consider, and the display of new tags on the Moderators' page. In other words, there's a lot of calls on the count, so it makes sense to hold and maintain the count against each tag.

Other sites don't extract nearly as much juice out of their tags. But, as an example, say you want to display a tag cloud; you might want to be avoid counting all the entries in FileTags each time you render it. Holding the count would then be a help.

But remember, writes can be expensive. So have a clear idea what you are going to do with the counts before you decide to store them. Maintaining them asynchronously, perhaps in a batched job, would be a good idea.