How to maintain subcategory in MYSQL?

Question

I am having categories as following,

• Fun
  • Jokes
  • Comedy
• Action
  • Movies
  • TV Shows

Now One video can have multiple categories or sub categories, let's say VideoId: 23 is present in Categories Fun, Fun->Comedy, Action->TV Shows but not in Action category. Now I am not getting idea that hwo should I maintain these categories in Database. Should I create only one column "CategoryId AS VARCHAR" in Videos and add category id as comma-separated values (1,3,4) like this but then how I will fetch the records if someone is browsing category Jokes?

Or should I create another table which will have videoId and categoryid, in that case if a Video is present in 3 different categories then 3 rows will be added to that new table

Please suggest some way of how to maintain categories for a particular record in the table

Thanks

Answer 1

You categories table could have a column in it called parentID that reference another entry in the categories table. It would be a foreign key to itself. NULL would represent a top-level category. Something other then NULL would represent "I am a child category of this category". You could assign a video to any category still, top-level, child, or somewhere inbetween.

Also, use autoincrement notnull integers for your primary keys, not varchar. It's a performance consideration.

---

To answer your comment:
3 tables: Videos, Categories, and Video_Category

Video_Category would have VideoID and CategoryID columns. The primary key would be a combination of the two columns (a compound primary key)

Answer 2

You have two choices, parentID (better as INT) to refer to the parent or an extra table with categoryID - parentID. The last one may provide a better logical separation and allows you to have multiple categories.

Answer 3

I suggest that create another table which will have videoId and categoryid. Then you can use sql-query as follow:

select a.*,GROUP_CONCAT(b.category_id) as cagegory_ids 
from table_video a
left join table_video_category b on a.video_id=b.video_id 
group by a.video_id