Views ala RDBMS

[dsebastien]

I have a really naive question about views.
Be aware that I'm really just learning about CouchDB at this point; we're using it for our app, but there are many things that I don't understand much so far. Among those things are indexes and views.

First off, here a bit of background about the data model of our system.
We have one CouchDB database per "workspace" in our system; a workspace being a place in which users of a specific organization (ie tenant) store information (in our case, meetings, topics to discuss, etc).

In each of the workspaces, we have documents of multiple types (e.g., categories, topics, meetings, etc) which are very structured and whose name start with the corresponding type (e.g., C_ for categories, M_ for meetings, etc).

Since our model is quite relational, documents of a certain type have references to documents of other types. For instance, a meeting is always part of a category, so that relationship is represented by a "category" property in the meetings, pointing to the id of the category document.

In addition, each document has a set of owners, which should be the only ones being able to update that specific document (i.e., in the future we'd like to have per-document access control, but I know that it's on the roadmap). At this point, we plan to put a proxy of sorts in between couch and the consuming apps to enforce those security boundaries.

Now that you have an idea about how our data is structured, here are my questions

1. Is it supposed to be possible / easy / straightforward to create a view that regroups information present in different document AND be able to query paginated results on that view? My miserable attempts to create a view that includes for instance the category name along with meeting information failed. All I could do in the view was have additional "records" for that information, but it just broke the pagination and didn't serve my purpose.

2. Are there ideas around or future features to be able to "link" documents to user accounts in CouchDB and be able to do joins on that? Here's my use case there: type "Sebastien" in a search box above a list of meetings and also be able to filter on the "owner" column, which is just a reference in the meeting document, pointing to the id of a CouchDB user.

[dsebastien]

So far, I abandoned point 1 since I couldn't find a way to express what I wanted.

For point 2, I've thought about copying/sync a minimal amount of user information in _users to each of the workspaces (i.e., databases) which the user is a member of.

[janl]

I think your best choice here is not applying your RDBMS best practices onto CouchDB, but try and model your data so it fits a document store better. E.g. you’re saying a meeting has a category field. That works fine a document model and CouchDB views can give you “all documents in a certain category”, but it doesn’t get much more fancy than this.

We currently have no plans for further linking documents to allow you to do what you outlined in your point 2. If you need to find documents that belong to Sebastian, then Sebastian needs to be in the doc. If you only reference the user id, then you need two queries:

1. get user id for Sebastian
2. get all docs for user id

Using multiple requests for this type of stuff is entirely normal.

[dsebastien]

Thanks a lot for your reply @janl, it's very helpful & appreciated!

Habits from RDBMS die hard ;-)
I wanted to try and avoid duplicating data while the database supported it, but here obviously we're asking too much.

This will help us better design the data model.

I imagine that I could "enrich" the fields I'm using to store the id of the linked documents, so for instance:

Meeting 1:

name: "Super",
category: {
  "_id": "dfgdg1df5g54d",
  "name": "Cool category"
}
...

Instead of

name: "Super",
category: "dfgdg1df5g54d"
...

That way I'll have the info readily available.

I now need to think about what this will mean for updates; My guess is that whenever I update a category document such as the one linked above, I need to go about and update all documents that refer to it, maybe using a batch process, finally reaching eventual consistency..

I suppose it should be okay for us to have delays in those updates since user information and category information shouldn't change too frequently.

Still, I feel like it would be great to have view akin to what we can define in classic RDBMS, with find/pagination support :)