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

2) 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.

No question: Number 2!

NEVER store a delimited list in a single DB field. (NEVER is a little too strong...make that NEVER unless when you roll 7 dice you get all 1's. And no re-rolls allowed.)

p.s.: There is no earthly reason for the id field in that tags table. You will never do a lookup by id; you will never link another table to this one by id.

A *probably* better design (depends on how many times you have duplicate tags) is:


Table: videos
video_id INT AUTO_INCREMENT PRIMARY KEY
video_name ... etc. ...

Table: tags
tagid INT AUTO_INCREMENT PRIMARY KEY
tag

Table: video_tags
video_id INT REFERENCES videos(video_id)
tagid INT REFERENCES tags(tagid)


So now if you have tags such as "SCIFI" and "HORROR" and "MAYHEM" that you use for many different videos, indeed you have a fully normalized database.

It isn't necessary to post your question in every forum you find. Post in a forum, wait a reasonable amount of time and see what answers come up.

Cross posting in multiple forums isn't considered good practice.

Thanks Old Pedant for sharing your thoughts