couchdb many to many headache

Question

So I've read things on translating relationship on couchdb but I still can't come up with a proper map function to represent many to many.

Imagine you have some docs (note this is an example, not actual model):

{name:"whatever1", from:"A"}
{name:"whatever2", from:"A"}
{name:"whatever3", to:"B"}
{name:"whatever4", to:"C"}

and some other type of docs defining the relationship:

{name:"link1", link: true, from: "A", to: "B"}
{name:"link2", link: true, from: "A", to: "C"}

I'm trying to come up with the right function that returns me the list of combinations:

{ name: "link1",
  froms: ["whatever1", "whatever2"],
  tos: ["whatever3"]
}

Does anyone know how to write such a map, should I change the structure of my docs? Please if you just want to link for a guide, don't post.

Answer 1

Generally speaking, many-to-many is simply not possible in stock CouchDB. You seem like somebody who just wants it straight with no sugar coating, so there it is.

Joining related data is not a strength of CouchDB, and of course it is the primary strength of relational databases. However, this fact is muddied a little bit because most people "intuitively" model their data relationally because that's how we're all trained.

Your difficulty is precisely the cost you must pay to CouchDB to get its other features: HTTP API, flexible clustered, multi-master, or offline operation, etc. By design, its sync features are possible because documents are simple, and unrelated to each other.

However, another strength of CouchDB is concurrency. So if you are already enjoying CouchDB's other features, and you just need to get over this one hump, you might simply "join" on the client side.

A simpler map function simply emits all the froms (key:["from", "A"], value:"whatever1") and tos (key:["to","B"], value:"whatever3"). Thus you can query it with ?key=["from","A"] and get a list of all whatevers for any given from or to value.

For each link document, you have a from and to value to check. Thus, query both (either one at a time, or simultaneously, which is quite easy in Javascript for example):

1. The view with option ?key=["from","A"]
2. And also the view with ?key=["to","B"]

And once both results return, you have the answer for that link.

You will find that, since you may only make efficient requests from CouchDB (index scans), CouchDB can support a very high rate of requests and a high number of concurrent connections.

Is this easy? In Javascript, it's not so bad, but no, basically it is not easy. This question is fundamentally cutting against the CouchDB grain and with the relational grain. (I am personally excited about Drizzle for problems like this.)