CODEWITHSUNDEEP
sqlmysqldatabase
djmzfKnm
djmzfKnm

Reputation: 27195

How to maintain subcategory in MYSQL?

I am having categories as following,

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

Upvotes: 0

Views: 322

Answers (3)

ZA.
ZA.

Reputation: 10477

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

Upvotes: 1

tuergeist
tuergeist

Reputation: 9401

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.

Upvotes: 1

colithium
colithium

Reputation: 10327

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)

Upvotes: 1

Related Questions