How would you model customer > order > ordertem > product in NoSql database?

Question

I'm currently learning Node.JS and need to implement a database. All of the Node books seem to think MongoDB is the best solution but I can't seem to get my head around NoSql databases like Mongo and Couch, I'm an MS SQL Server guy!

So, I understand that you can keep structured data as records (JSON) but I'm not sure how you'd model a typical ecommerce app with the following (simplified) tables...

customers (id, name, address)
orders (id, customerID, orderDate)
orderItems (id, orderID, productID)
products (id, title, description, image)

So, normally I'd write a query like this (but better optimized obviously)....

SELECT Customers.name, Products.title 
FROM (orders INNER JOIN customers ON orders.customerID = customers.id)
INNER JOIN orderItems ON orderItems.orderID = orders.id 
INNER JOIN products ON orderItems.productID = products.id 

If I could see an example of how this would work in a NoSQL database then I might start to "get it".

Alternatively, am I just better off sticking with MSSQL Server or MySql, both of which are compatible with Node anyway?

Answer 1

An important consideration when designing a schema for MongoDB is not what your data is, but how you will be using it. Without working out what type of reads and writes you will be doing (and how performant they will be) it can be difficult to design an "optimal" schema.

There are some basic guidelines that you can consider to avoid running into problems. One of them is avoid designing documents which keep growing unbounded. That means you should not embed orders into customer documents. Another rule is that things that aren't "of interest" on their own (or don't exist on their own) are probably better off being embedded. This suggests that orderItems do not deserve their own collection and should simply be treated as attributes of orders (which is what they are, in fact).

This exact exercise is covered in MongoDB developer training, being a pretty typical example of schema design.

Bottom line is that you should have three collections:

Products
Customers
Orders

Orders will reference customers (optionally denormalizing some information from customer collection) and they will reference products (in the array of orderItems they will contain).

Further collections, and exact fields in all these collections depend on your specific use case, but I can't see a feasible scenario to have fewer collections than these three.