Category and subcategory in same or different table?

Question

I am designing database for a blog, the blogs have categories and subcategories. Each blog has only one category (which may be category or subcategory).

I do not feel it's right to store the categories and subcategories in different database tables, so the problem is:

must category and subcategory in same or different tables?
How would I design the database tables to accomplish this?

Kordi · Accepted Answer · 2016-03-05T11:51:23.017

3

Normal Approach

Table A: ID, CATEGORY
TABLE B: ID, CATEGORY_ID, SUBCATEGORY

Just use 2 Tables.

Approach with subcategories related to more than one category

Table A: ID, CATEGORY
TABLE B: ID, SUBCATEGORY
TABLE C: ID, CATEGORY_ID, SUBCATEGORY_ID

Use this if you have subcategories added to more than one category.

Different Approach

TABLE A: ID, CATEGORY, SUBCATEGORY AS TEXT (SUBCATEGORIES as JSON List. e.g. ["SUB_1", "SUB_2"])

If you have a lot of subcategories added to more than one categories. In this case just save the SUBCATEGORY_ID in the list. Theen

TABLE A: ID, CATEGORY, SUBCATEGORY AS TEXT (SUBCATEGORIES_ID saved as JSON List. e.g. ["1","2"]) 
TABLE B: ID, SUBCATEGORY

It just depends if you want to search after subcategory in this case. If yes take the normal approach.

edited Mar 05 '16 at 11:51

answered Mar 05 '16 at 11:34

Kordi

2,405
1
14
13

some categories have no child so search can be based on subcategory or parent category, is the second approach is better in this case? If yes, is their another way without JSON? – Mathew Mar 05 '16 at 11:43
i would then take the different approach. You must save the subcategrories in a textfield, so json is a safe format to read and write. – Kordi Mar 05 '16 at 11:47

score 1 · Answer 2 · edited May 23 '17 at 12:17

_{First, let me just point out that the StackExchange site for Database Administrators is more on-topic for DB design questions.}

As @Kordi's answer already mentioned, there are essentially two approaches:

Using one table per level you wish to make navigable. OR;
Nesting your (sub-)categories all in one table.

Each of these have their own advantages/disadvantages. However, for a situation where you "have only one category (which can be category or subcategory[...])" I would say it's easier to use multiple tables.

However, since your question also specifies that you wish to only use one, you may want to take a look at this previous answer on hierarchical data in relational database.

Category and subcategory in same or different table?

2 Answers2