Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts

    Question 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:

    Code:
    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,516
    Thanks
    77
    Thanked 4,379 Times in 4,344 Posts
    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.)

  • Users who have thanked Old Pedant for this post:

    phantom007 (08-06-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,516
    Thanks
    77
    Thanked 4,379 Times in 4,344 Posts
    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:

    Code:
    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.

  • Users who have thanked Old Pedant for this post:

    phantom007 (08-06-2011)

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #5
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    Thanks Old Pedant for sharing your thoughts


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •