Product/ Category management in mongoDB and MySQL

Question

I am a newbie with mongoDB. I write a simple application to manage product. I use mongoDB. This is my database

Category Collection:

db.category.insert({id:1,name:"Motor",description:"Sell Motor"})
db.category.insert({id:2,name:"Car":description:"Sell Car"})

Product Collection:

db.product.insert({id:1,name:"Honda CBR",price:15000,id_category:1})
db.product.insert({id:2,name:"Kawasaki ",price:16000,id_category:1})
db.product.insert({id:3,name:"Ford", price:50000,id_category:2})

I think to find Product with Category name is Motor, I have to write tree queries:

var result =  db.category.find({name:"Motor"})
var cat = result.next().id
db.product.find({id_category:cat})

In MySQL I can do the same thing with two table:

CREATE TABLE `product_manage`.`category`(  
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(2000),
`description` VARCHAR(2000),
PRIMARY KEY (`id`)
);

CREATE TABLE `product_manage`.`product`(  
`id` INT NOT NULL,
`name` VARCHAR(2000),
`price` INT,
`id_category` INT,
PRIMARY KEY (`id`)
);

So I don't know what is the advantage of MongoDB over MySQL in this example. I want to ask some more:

In mysql:

case a:

select id into @id_cat where name="Motor"
select * from product where id_category = @id_cat

case b:

select p.id,p.name,p.price from product p,category where p.id_category = category.id
and category.name = "Motor"

Which case is good for performance?

See [Join operation with NOSQL](http://stackoverflow.com/a/1996579). — eggyal, Nov 20 '13 at 08:32

Devesh · Answer 1 · 2013-11-20T10:06:02.330

The advantage of the mongodb will picture based on your requirement . In your case to show the product with the category name , you have to make join between the two tables which will have some performance hit. In case of mongodb you can have one document call Product and you will embed the Category inside it. Something like this

   {Id : 1 , "Name" : "hello mongodb" , Category : { "Id" : 1 , "Name" : "DB }}

Now with one query you can get the Product and its category. Even you can search on sub document like

          db.Collection.find({ Category.Id : 1 })

You will get all the Products with one query. In case you have more details in Category , keep some of the detail in the Product collection also which you show very frequently with the Product at the same time we will have the Category Collection also with detail information.

You can figure out by yourself even though the data like category name and id is repeated in all the document of the product , performance will be much better in mongodb with this kind of schema design.

Thank @Devesh, If Category there are many value, such as Description is a long text. I think Embed Category into Product is not a good way! — user2693571, Nov 20 '13 at 09:55
@user2693571 It all depends , in that case have the Category collection with more detail but keep some of the detail in the Product collection also which you show very frequently with the Product. It is all about your query — Devesh, Nov 20 '13 at 10:04

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

MongoDB is a Document DB, document can be any valid json. this is advantage of MongoDB.

But, MySQL is Relational DB. Relation means table (which has fixed schema)

In your case, MongoDB has no advantage over MySQL because category and product are fixed.

This slide can help you.

There are a lot of arguing 'should use' v.s. 'not use' MongoDB. If you are concerning to adapt MongoDB, in depth search of mongodb is needed.

UPDATED

Which case is good for performance?

It depends on your data size and sharding strategy. sorry there is no exact answer about your question. you need to build your own testbed and do perfomance test!

Thank you, this is the expected answer! – user2693571 Nov 20 '13 at 09:15 — user2693571, Nov 20 '13 at 09:15

Product/ Category management in mongoDB and MySQL

2 Answers2

UPDATED