We help IT Professionals succeed at work.

schema design for sub-categories

High Priority
189 Views
Last Modified: 2019-07-07
As an example, I want to have food categories, sub categories and the actual menu items themselves. All menu items have to have a category, but not all have to have a sub-category.

So, something like:

Example of category only:

Hamburgers
     - Bacon and cheese
     - Chicken 

Open in new window


Example of category and sub-category:

Pizza
    Traditional
       - Cheese and mushroom
       - Tomato and ham
   Deluxe
      - Bacon, ham, salami, steak

Open in new window


This means the sub category is optional. So, when building the form to add a menu item, should there be an option for none on the subcategory or if nothing is selected from the dropdown menu would it be fine to insert an empty string into the database?

This is what my schema looks like but not sure if it is right for what I need. I am

const menuSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    minlength: 5,
    maxlenth: 255
  },
  description: {
    type: String,
    required: true,
    minlength: 5,
    maxlength: 255
  },
  price: {
    type: Number,
    required: true,
    min: 0
  },
  category: {
    type: categorySchema,
    required: true
  },
  subcategory: {
    type: subcategorySchema
  }
});

const Menu = mongoose.model("Menu", menuSchema);

Open in new window

   
The category and subcategory schema are pretty much the same but would it be better to have 2 separate schemas for category and subcategory or have just a categories schema with an embedded subcategory within it?

const categorySchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    minlength: 5,
    maxlength: 255
  }
});

const Category = mongoose.model("Category", categorySchema);

Open in new window

Comment
Watch Question

Author

Commented:
I did find this if anyone finds it helpful in helping me :)

https://docs.mongodb.com/ecosystem/use-cases/category-hierarchy/
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
When dealing with NoSQL or document based databases it is difficult (if you come from a relational / SQL background) to think in terms of rows and columns. One of the major drawcards of the document based data architecture is the ability to have none like fields and child entities.

Your subcategories are (I imagine) a sub-collection on your categories - if present you render them - if not you simply render the categories.

In Mongo fields are optional by default so you define subcategory in the schema but you simply do not define it in the data if it does not exist.

The link you posted seems to be more about how to maintain a cat / subcat hierarchy - which is probably overkill for what you are wanting to do.

Author

Commented:
Okay, so should I just put the subcategories within the categories collection instead of having 2 separate collections?

const categorySchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    minlength: 5,
    maxlength: 255
  },
  subcategory: {
    title: {
      type: String
    }
  }
});

const Category = mongoose.model("Category", categorySchema);

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
Ultimately it depends on the data and you plan to use it. One argument against doing this is if you want to access subcategories (or a list of them) independently of the categories.

For instance lets for argument you foresee a requirement where you will need to list all sub-categories in the system. If you use the schema you describe above this will be difficult as you will need to query every Category to get the list.
If however (and probably the case here) you are only interested in subcategories relative to the category they are in - so subcategory is contextually bound to category. Sub-category brown in the Socks category - is contextually different from the Shoes category - although they both relate to category the colour is not necessary the same colour brown.

In this case it makes sense to keep the sub-category as a child of the category.

Author

Commented:
What I had thought of doing is when you click on 'pizzas' you see:

Traditional
- Pizza 1
- Pizza 2
- Pizza 3
- Pizza 4
- Pizza 5

Deuxe
- Pizza 6
- Pizza 7
- Pizza 8
- Pizza 9
- Pizza 10

And at the top of the page I might want to filter ie: have 2 buttons, Traditional and Deluxe. If you click on Traditional you only see traditional pizzas and if you click on Deluxe you only see deluxe pizzas.

Now that I have written that out I will probably actually need an array as one category can have multiple subcategories

const categorySchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    minlength: 5,
    maxlength: 255
  },
  subcategory: [
    {
      title: String
    }
  ]
});

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
array - yes - apologies I misread your earlier post and assumed it was an array.

Your filter would then query the db based on a value in that array (if filtering on sub-categories)

Author

Commented:
Sorry to keep on about this, just want to make sure I do it right first time so I don't have to try fix it later.

Would it be better to do the above or perhaps in my form when creating a new category have a dropdown where the user can select a parent instead? (see attached image) Then I wouldn't need an array after all I think.

const categorySchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    minlength: 5,
    maxlength: 255
  },
  parent: {
    title: String
  }
});

Open in new window


Then in the DB you would have for example

_id: 5d1874af26541007bdb7edd1
Category: Traditional
Parent: Pizza

_id: 5d1874b826541007bdb7edd2
Category: Deluxe
Parent: Pizza
parent.png
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
I would go with the first because it makes more sense.

How are you linking the product to the category schema? Are you just storing category and sub-category in the product schema.

Author

Commented:
I haven't really finalised my product schema yet. But that was the initial idea. Probably to store the category ID and subcategory ID in the product collection. I want to go with the id's incase anyone was to change the category and subcategory. I don't really like that because I'm going to have to do 'joins' but its probably safer than just storing the names.

Something like this: (although I am not yet 100% sure how to store both Id's)

const menuSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    minlength: 5,
    maxlenth: 255
  },
  description: {
    type: String,
    required: true,
    minlength: 5,
    maxlength: 255
  },
  price: {
    type: Number,
    required: true,
    min: 0
  },
  category: {
     type: Schema.Types.ObjectId,
  },
  subcategory: {
    type: Schema.Types.ObjectId,
  }
});

const Menu = mongoose.model("Menu", menuSchema);

Open in new window


Do you think that is fine or is there a better way?
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
Will you ever NOT use a subcategory? i.e. is it possible to have products linked only to categories?
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
Another way of doing it is having a categories table (given it is only 2 deep) - you have parent id for each document which is 0 for category elements.

Each product then just gets a categoryId - (one). If it has a parent is a sub-category otherwise it is a category.

Not sure what the best practices are with Mongo but with other NoSQL DB's like Firebase it is a good idea to duplicate key data in documents to avoid having to do Joins - so in your product you store the Category name and Sub Category name.

If you change the name of either you run a query to apply the update in the related records. Saves a JOIN - the problem comes when you start trying to apply relational practices to NoSQL databases - the same rules do not apply.

Author

Commented:
To answer your first question, yes, there will be mostly times where there will not be a subcategory. At this stage there are 4 categories and only 1 out of 4 categories need subcategories.

Author

Commented:
Yes, I am moving away from php/mysql and want to focus on full stack javascript development. But trying to get my mind to shift from SQL to NoSQL is hard!
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019
Commented:
Then I would just store the category on the Product and it's sub-category if available. Category is a unique key so maintaining another unique key doesn't really make a lot of sense.

All you would need to do here is when someone changes the name of a category - you run a process that updates all instances of the old with the new.

Same applies with sub-category - avoids the complexity of having to use joins etc.

Author

Commented:
I was actually thinking along similar lines. Only store category names inside of the categories collection and store the sub category only in the actual product collection. I just hope that will work out down the line but I am going to try it as it is the simplest.

So, my categories collection would literally just be:

const categorySchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    minlength: 5,
    maxlength: 255
  }
});

const Category = mongoose.model("Category", categorySchema);

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
Ok but I was meaning just store Category and SubCategory as strings in the product.

Your category schema really only exists so you can build drop downs for your Product editor - correct?

Author

Commented:
I was meaning just store Category and SubCategory as strings in the product

Yeah, So products collection would look something like:

name
description
category
sub_category
price

Open in new window


Your category schema really only exists so you can build drop downs for your Product editor - correct?

Yes, and so the user can filter by category or view products by category
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
Right, so your product becomes the place to store the actual values - no id's just the text values.

You maintain a separate list of category / subcategory values that are just there to make selections (populate drop downs)

Searching is done on the actual text value.

Author

Commented:
Okay, got it. But then for the categories schema should I revert back to storing the subcategories in an array within the categories schema?

const categorySchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    minlength: 5,
    maxlength: 255
  },
  subcategory: [
    {
      name: String
    }
  ]
});

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.