CODEWITHSUNDEEP
mongodbdatabase-designnosql
Marcel Jackwerth
Marcel Jackwerth

Reputation: 54782

Is MongoDB the wrong tool for this many-to-many problem?

I'm currently designing a shop system with these entities:

An account can acquire a license for a product (many-to-many). I will have a page where I display all the Products licensed by a given account.

Current concept: Product has an array of licensed Accounts, so that I can use find(licensed_accounts: ObjectId("4d731fe3cedc351fa7000002")).

I am expecting a lot of accounts. For popular products, that array might contain millions of ObjectIds (12byte * 1,000,000 = 12MB). One million will bring the document close to its current 16MB size-limit already.

Is there a better approach to handle this? Or is MongoDB the wrong tool for that many relations?

Upvotes: 1

Views: 621

Answers (3)

Maxence
Maxence

Reputation: 13329

Look at this slide : http://www.10gen.com/presentation/mongosf2011/schemabasics. There is a many-many sample with several alternatives.

In your case, you should store your products IDs in the account document:

accounts:
  { _id: ObjectId("..."),
    name: "ACME",
    product_ids: [ ObjectId("..."), ObjectId("...")]}

One product can have a lot of accounts, but one account should only have a few products? Isn't it?

To display all the products for an account:

> db.products.find({_id : {$in: account.product_ids});

Upvotes: 1

rook
rook

Reputation: 67039

First you should read: When to use MongoDB or other document oriented database systems?

A major power of MongoDB and nosql is the idea of sharding. In this case it is not clear what value(s) would be best to use as a shard key.

In any case, you could solve this problem with nosql, or with a relational database. I think that the use of a join works well to correlate accounts and products. So I would use SQL because it would reduce the number of queries required.

One approach that would be radically nosql would be to only have one collection of the document type that you need. However if you need to update a product you'd have to make a large number of changes, which is more evidence that this is more of a relational problem.

Upvotes: 0

Matt Briggs
Matt Briggs

Reputation: 42248

how about a "join table" type concept? You have Products, Accounts, and Licenses. A license has a product id and an account id. That way you wont pay the cost of massive documents, and can still accomplish what you are trying to do.

Mongo vs other stuff has tradeoffs, one of those things is that many to many really sucks in a document database (the tradeoff is one to many is pretty much free) If this is for reporting purposes, I would look into a nightly (or weekly or whatever) map-reduce query to get the data into something that makes your job (and mongos) easier. Map reduce is incredibly flexible, and you can tell it to persist the results as a collection, it also scales well in a cluster, but single node performance wise it is very slow (which is why I would do it as a nightly thing)

Upvotes: 0

Related Questions