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?
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.
Mongo uses collections, which you could somewhat correlate to "tables", so you could have 4 collections here. But note there's no reason you couldn't combine "orders" and "order items" into just "orders", as you need to consider each entry can be more of a document that you can achieve with RDBMSs.
Couch is different, where you simply store documents. In this case you might flag each document with what "type" of document it is. You can then create view functions which can return the data you need via map/reduce.
With any of these, don't get too hung up on doing everything in one query, as it's not always possible.
Key point here is there's no single "NoSQL" approach to this, unlike RDBMS where SQL is the unifier.
Each DB and type of NoSQL store has its plusses and drawbacks, and you'll need to determine what the best fit for you is.
Hope this helps.
