Category and Subcategory Schema design

Question

So basically it is for blog posts and its categories and subcategories. A blog post can have multiple categories and each of those categories can have multiple subcategories (based on the post).

For example, let's say I have two posts, Post 1 and Post 2. Post 1 are in categories Motivation and Journey. Based on the content of Post 1, Motivation can have subcategories like, Success and Money.

In the same way, Post 2 could be in categories Education and Journey. With Education having a subcategory Knowledge and Journey having a subcategory called Success.

I have already designed the Post and Category schema but I'm having a hard time with the Subcategory schema.

Here's how the Post schema looks like:

const postSchema = new mongoose.Schema( {
    post: {
        type: String,
        required: true,
        trim: true
    },
    category: [ {
        _id: false,
        categoryID: {
            type: mongoose.Schema.Types.ObjectId,
            required: true,
            ref: 'Category'
        }
    } ]
});

Here's the Category schema:

const categorySchema = new mongoose.Schema( {
    catName: {
        type: String,
        required: true
    },
    count: {
        type: Number,
        default: 0
    }
});

*******COUNT field shows the total number of posts that have that specific category. 
For example, Motivation category could be in 100 posts.*******

So my question is, how can I write the schema for Subcategories?

If I put subcategory reference in Post schema, then I won't be able to link subcategories with categories because each category will have different subcategories based on the post.

If I put subcategory reference in Category schema, then that is also not feasible because one category can be in multiple posts.

Or should I put Post reference and Category reference in Subcategory schema?

Please suggest me the best way I can do this.

Answer 1

For such use cases, I would do something like this -

Collection 1 - Post

post : string

Collection 2 - Category

name : string
count : number

Collection 3 - SubCategory

categoryId : ObjectId // FK to category
name : string

Collection 4 - PostCategories

postId : ObjectId // FK to post
categoryId : ObjectId // FK to category
subCategories : [ (ObjectId) ] // array of sub-categories, pointing to sub-category

Database design mostly depends on your use case. If you just want to see the count per post, you can just store category and sub-category relations in a single array (in PostCategories collection)

PostCategories, which I have mentioned here, can be used if you want to search based on the categoryId (if want to show posts related to one category on the UI, you can have an index on categoryId and this would be the fastest approach)

If you want to search even based on the sub-category too, you can modify the PostCategories table to have many-to-many relations.

postId : ObjectId // FK to post
categoryId : ObjectId // FK to category
subCategories : ObjectId // FK to sub-category