Many-to-many relationships in CouchDB or MongoDB

Question

I have an MSSQL database which I am considering porting to CouchDB or MongoDB. I have a many-to-many relationship within the SQL db which has hundreds of thousands rows in the xref table, corresponding to tens of thousands of rows in the tables on each side of the relationship. Will CouchDB and/or MongoDB be able to handle this data, and what would be the best way of formatting the relevant documents for performant querying? Thanks very much.

Answer 1

For CouchDB, I would highly recommend reading this article about Entity Relationships.

One thing I would note in CouchDB is to be careful of attempting to "normalize" a non-relational data model. The document-based storage offers you a great deal of flexibility, and it's seldom the best idea to abstract everything into as many "document types" as you can think of. Many times, it's best to leave much of your data within the same document unless you have clear cases where separate entities exist.

One common use-case of many-to-many relationships is implementing tagging. There are articles about different methods you can use to accomplish this in CouchDB. It may apply to your requirements, it may not, but it's probably worth a read.

Answer 2

Since the 'collection' model of MongoDB is similar to tables you can of course maintain the m:n relationship inside a dedicated mapping collection (using the _id of the related documents of the referenced documents from other collections).

If you can: consider redesign your application using embedded documents.

http://www.mongodb.org/display/DOCS/Schema+Design

In general: try to turn off your memories to a RDBMS when working with MongoDB. Blindly copying the database design from RDBMS to MongoDB is neither helpful nor adviceable nor will it work in general.