How would you model this database?

Hello,
New MongoDB user here with SQL knowledge.

See image below, where I’m showing 2 different simplified schemas for the same database for an app I am working on.

The database is for an e-commerce store. The orders collection holds the order data. The products collection holds the product catalog data, which gets copied to the products_ordered collection representing products ordered by a customer.

Users of the app will regularly be viewing orders and therefore for this use case it makes sense to go with scenario A, and have everything related to a given order be in a single document.

However users of the app will also regularly need to view all products that are on any unfulfilled order, and for this use case I feel like scenario B is better.

In the end, I believe scenario B would be better because I think that in cases where a single order is viewed, it would be pretty quick to query the products for that order from the products_ordered collection, whereas in the case when all products are viewed at once, scenario B would be significantly faster than scenario A.

What are your thoughts?

Thank you !

Some thoughts:

  • subtotal and total are calculated values and thus suspect.
  • As regards what is “faster” it’s not clear from the examples you have plumbed all aspects of MongoDB performance.

Some will disagree with the following, but fwiw, ask yourself if you are order centric or customer centric. If everything radiates out from entity customer then it’s easy to design for MongoDB. If customer is on the other side of an interdepartmental firewall and your nose is pressed to the grindstone of orders then you may be better off with a classic RDBMS.

Hi @Samuel_Leith,

In my opinion scenario A will mostly suite MongoDB better.

I am not certain how exactly the query flow in your application works but usually customers will login into a store and they will see all available products to browse with a summary of thier profile like amount of orders and notifications.

Therefore I think you might benefit from using an extended pattern from orders to products where customer collection will hold orderid and orders will hold userid and the product list with main details to show on order page.

If the application want to find full product details run a query to products collection:

There is definitely no reason why MongoDB can’t be as good for E-commerce as RDBMS and usually much better , so its just a matter of understanding query patterns…

Best
Pavel

1 Like

Hello,
This is not for an E-commerce platform per se, but rather for an internal App we will be using. I ended up going with scenario A and it is working perfectly well, thank you.

Site done: As for the calculated fields, there are indeed a few that are saved in the database because these are actually fetched via the Magento API, and it is important for our internal App to have the exact same info as shown on Magento.

For any data originating from without our app and not from Magento, the a calculated field would not be saved in the database.

Cheers!

1 Like