In this article, we will see the product catalog database design and its associations. The below diagram explains the entities involved and their associations.
Let me justify the above entities associations, as MongoDB has an embedded document approach and refer one document to another document.
Product to Category association chosen as a reference-based approach. Because the product can be associated with multiple categories and categories can be modified by business users. So when the category goes with modification, I don’t want to update the products belongs to that category.
Product to Reviews association chosen as a reference-based approach. Because the reviews are user-generated content and it will grow over the period. If I choose reviews as embedded documents, then whenever the review is given by the shoppers, then the product should undergo for update and MongoDB has a limit on the single document size as 16 megabytes. The other reason is, some times we don’t want to manage reviews in our e-commerce system rather we will go with 3rd party integrations like Bazzarvoice or Yotpo kind of solutions. Going with the reference-based approach will enable us to detach the in-house management of the review system and choose above said 3rd party solutions.
Product to Inventory association, here again, I have chosen a reference-based approach. Because, the inventory is referenced by multiple systems like a customer service center, point of sales(POS) and online system, etc. Some times inventory may not be part of an e-commerce system rather it is maintained by ERP systems. In that case following the reference-based approach enable us to remove managing of inventory in the e-commerce system and use 3rd party inventory system.
The detailed field list of each entities document is given below.
Catalog Document Structure:
Category Document Structure:
Product Document Structure:
Inventory Document Structure:
Reviews Document Structure:
If anyone wants to provide inputs or want to debate on the above product catalog design, it is very much appreciated.
Siva Janapati is an Architect with experience in building Cloud Native Microservices architectures, Reactive Systems, Large scale distributed systems, and Serverless Systems. Siva has hands-on in architecture, design, and implementation of scalable systems using Cloud, Java, Go lang, Apache Kafka, Apache Solr, Spring, Spring Boot, Lightbend reactive tech stack, APIGEE edge & on-premise and other open-source, proprietary technologies. Expertise working with and building RESTful, GraphQL APIs. He has successfully delivered multiple applications in retail, telco, and financial services domains. He manages the GitHub(https://github.com/2013techsmarts) where he put the source code of his work related to his blog posts.
Smart Techie 
Hi, great article.
Could you please explain which information you recommend to save in a RDBMS DB instead of in a NoSQL one? I got confused because, por instance, I would have thought that product inventory transaction should be acid.
Or, does the info with eventual consistency should be updated in a nosql database?
Thanks in advance,
Best regards
You can keep transactional data also in NoSql. For example, MongoDB. You can keep order or customer information in MongoDB and make sure you update or insert the entire document at once. Or you can go with Cassandra where you can get eventual consistency.
what is difference id supplied by and supplied by mongodb , could help to explain ?
If you want to refer the id in other documents, then u can supply the id. Which will be controlled by us. If Mongo supplies, then the id will be 24 character hex string. It is cryptic to refer from other documents.
Hello Siva,
Thank you so much for responding.
what is the reason you put “catalog_ref_id” in Inventory Document Structure
For each sku, I wanted to manage the inventory. Here catalog_ref_id is sku id.
Hi, Great series! Looking forward to next article of the series, specially integration between mongo and Elasticsearch.
Plz can u explain diff b/w MongoDB and other DB?
Other DB means? RDBMS?
If so here are some of the differences.
RDBMS:
MongoDB: