CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
panthro
panthro

Reputation: 24061

Categories and Sub Categories

I have a list of articles, these belong to various categories.

Sometimes a category will have sub categories, clicking on the category will show all of each sub titles articles for the category and clicking on a sub category will show all articles for the sub category only.

I am having trouble figuring out a schema.

Articles

id | title | article | category_id

Categories

id | title

  1. How can I represent sub categories in the categories table?

  2. How can I organise the data so that I can easily get all articles that belong to a category and also get the ones that belong to a sub category?

Upvotes: 0

Views: 185

Answers (1)

David Aldridge
David Aldridge

Reputation: 52336

Typically you would introduce a parent_category_id column in the the categories table, which links categories and subcategories together.

You have to be careful not to link a category to itself of course, and if you have multiple subcategory levels then you want to avoid the presence of loops.

You would extract the subcategories by a self-join on category between id and parent_category_id, then a join to the article table.

If you had articles that could be joined to the category or the subcategory, then:

(select id from category where id = 3
 union all
 select id from category where parent_category_id = 3)

... gives you the set of category ids to which you would join to find the complete set of articles.

Upvotes: 1

Related Questions