CouchDB Many To Many Joins

I'm on the process of converting our application from using SQL Server and Entity Framework 5 to CouchDB. I'll explain the motivation in separate post.
This post is about entity relationship of Many to Many.

CouchDB does not have entity relationship the way RDBMS does. But it's not hard to create them.
Our application is multi-tenant and each user in our application can be allowed to access many tenants. This makes a many to many relationship between users and tenants.

First thing is modeling users and tenants as documents. I've took the very simple approach of creating a document for each user and each tenant. User document have field call $type with value of "user" and Tenant document have field call $type with value of "tenant". Simple :)
I use dollar sign in the field name to make it very clear it is not a native field of the document itself, it is a "system" field.

The way I've decide to model this relationship is by holding array of User document IDs inside the Tenant document. Generally you will want to hold the array on the side that is least likely to change.

The documents will look something like this:

{

• _id: "54",
• _rev: "3-53c1c569bc6c316d4d29f20c47f05184",
• $type: "user",
• username: "a@b.com",
• firstName: "ido",
• lastName: "ran",
• email: "a@b1.com",
• passwordSalt: "AABBCC=",
• password: "123HASH"

}

{

• _id: "20121207153325-Ido-Test",
• _rev: "2-8d9cb3b070a062d9a65ed22fa3f7bf9b",
• $type: "tenant",
• users:
  

  [
  

  • "54",
  • "63",
  • "84",
  • "124"

  ],
• name: "Ido Test"

}

Selecting the join of both users and tenants is done in two different views, one for selecting tenant(s) and their related user(s) and the second to select user(s) and their related tenant(s).

In order to select user and the related tenants we use CouchDB "Join" view as explained by Christopher Lenz:

function(doc) {

  if (doc.$type === 'user') {

    emit([doc._id, 0], null);

  }

  else if (doc.$type === 'tenant') {

    for (var i in doc.users) {

      emit([doc.users[i], 1], null);

    }

  }

}

In order to select tenants and the related users we use feature of CouchDB called Linked Documents. The feature allow us to emit values of structure { _id: "linked-document-id" } which allow us to later query the view with include_docs=true to select in one GET all the documents including the linked documents.

function(doc) {

  if (doc.$type === 'tenant') {

    emit([doc._id, 0], null);

    for (var i in doc.users) {

      emit([doc._id, 1], { _id: doc.users[i] });

    }

  }

}

In conclusion, it's pretty easy to have many-to-many relation in CouchDB. Storing the relation in one type of document gives some level of integrity but not transactional integrity.

The same views can be used to have One-To-Many relation but that will be in another post.