How would you design this MongoDB Schema?-CodePudding

This is a fairly generic issue, and after just getting started with MongoDB I am trying to find which is the better option for this schema design.

Suppose we have those entities: Product, Category and Order

In an SQL scenarion, this would be the traditional approach for the schema:

Category -> Product -> OrderDetails <- Order (A Category has Many Products and and each Product has only A Category | A product is in many Orders and An order has Many products, therefore we create an OrderDetails join table)

How should the same approach be in a MongoDB Schema? Suppose a category has at most 10 to 12 products only.

Scenario 1: Embedding Products into Categories:

Category:



{
        "_id" : Category_ID,
        "name" : "Category description"
        "products" : [ { "product_name": "First product", "price": 500  }, { "product_name": "Second product", "price": 150  } ] 
}

Order:


{
        "_id" : Order_ID,
        "order_total" : 500
        "products" : [ { "product_name": "First product", "price": 500 }] 
}

Scenario B: Embedding Category into Product AND having an arrays of product references in Order

Product

{
        "_id" : First product,
        "price" : 500
        "category" :  { "category_name": "First category name", "description": "some random description"  } 
}

Order


{
        "_id" : Order_ID,
        "order_total" : 500
        "products" : [ product id1, product id2] 
}

CodePudding user response：

Depending on how many products each category has, embedding the product inside the category might be an ideal scenario (there's a 16MB limit on documents), and depending on the usage pattern, for example, do you always get a category and display the products inside of it? or is the usage pattern getting the product each time?

Another question to ask is, does the category need its own identity, do you have a page that lists more information about a category? Or is a category just a description? If that's the case then I'd personally treat it more as a tag, see the example below:

{
  "_id" : id,
  "name" : "Product name",
  "description" : "Product name",
  "price" : 500
  "categories" : [ "Category name 1", "Category name 2" ]
}

As for the order document, I'd copy the whole product into the order, you always see similar patterns within say SQL, and this is because if the product changes like its price or name, you want the order to have a snapshot of the product at the time of the order. You don't want the price of the order to change after the customer has paid and it's been shipped.

{
  "_id" : Order_ID,
  // "order_total" : 500 * this would be calculated
  "products" : [
  {
    "_id" : id,
    "name" : "Product name 1",
    "description" : "Product name 1",
    "price" : 500
    "categories" : [ "Category name 1", "Category name 2" ]
  },
  {
    "_id" : id,
    "name" : "Product name 2",
    "description" : "Product name 2",
    "price" : 500
    "categories" : [ "Category name 1", "Category name 2" ]
  }
  ]
}

I know this isn't an actual pure answer, more like a bunch more questions, however, I hope it gives some direction for your schema design.