MySQL: Video Tagging

Hello All,

I wish to know how do you design a db for video tagging:

  1. Do you create a column in the video table for the tags and add the tags with a comma separate value, like: php, mysql, html, css

OR

  1. Do you create a separate table for tags and add one tag in 1 row for the video and then prefer JOINing the video and tags table?, Like:
id | video_id | tag_name
-----------------------------
1  |  556       |  php
2  |  556       |  mysql
3  |  556       |  html
4  |  556       |  css

Which approach do you prefer?

Thanks for sharing your thoughts.

you create one table for videos with info about the videos.
you create a second table with tagid and tag (php, mysql etc.)
you create a third table to relate tags to videos

videoid and tagid are the columns. one new row for each tag that is associated with a video. so one video may have five rows in this table while another video has 15.

At no time should you store comma separated values in a table, doing so violates first normal form of database normalization.

you can do this, yes, but only if you never ever want to search for all videos with a specific tag

yes, like that, except without the extraneous id

video_id | tag_name
------------------------
  556    |  php
  556    |  mysql
  556    |  html
  556    |  css

also, note that i do ~not~ think you should use a tagid – just use the tag itself, like above

Thanks guelphdad and r937 for sharing your thoughts :slight_smile:

Rudy, in your example all the tags would have to be typed out in full for every video they belong to. Why would you want to do that and not have a separate table where tags are entered only once?

where did i say i didn’t also have a table with the tags in them? :wink:

the tags in the relationship table are FKs to the tags table