How do I best query for user-owned data when using NoSQL

Question

Say I have a typical website which provides authenticated users access to their stored ('owned') data. Authorised users must be able to perform CRUD operations on their data. If I were using a RDBMS-based solution I would use a series of joins based on the user_id retrieved from the session in order to make sure that I only serve users data which belongs to them, for example;

select comments.* from comments
inner join blogs on blogs.id = comments.blog_id
inner join clients on clients.client_id = blogs.client_id
inner join users on users.client_id = clients.client_id
where users.user_id = X;

This pseudo-SQL example gives me all the comment data which belongs to the user.

If I were taking a NoSQL approach I'd have a blog document which embedded a comments array and exposed a client_id field which I could populate in Mongoose, but the issue comes in when I have to run a separate query to fetch all the clients which belong to the user.

Is there a more efficient way to do this? I'm worried I'm missing a basic concept. I suppose the answer may be to store more related data within documents, so have a base client document which stores arrays of users and blogs with nested comments, but this seems design seems too rigid.

Answer 1

As far as the SQL side of things goes, there is opportunity to optimize, but it depends on what you want to optimize around. If you are seeking to simplify queries or speed up performance, denormalization is one way to achieve this. For example, you could include the client_id in the comment table in addition to the blog_id given that it appears that blogs can have only one associated client_id. Once you do that, the query above becomes:

select comments.* from comments
inner join users on users.client_id = comments.client_id
where users.user_id = X;

This is less complex, and depending on the size of the tables could have some performance benefit. The tradeoff is that you will end up with a somewhat more complex data model with redundant data across tables making it potentially harder to maintain data integrity.

Note, in your original example above, it is possible to simplify the query. without denormalizing. You aren't actually using any of the "clients" table data in the result, so including it in the join is extraneous given that the "users" table includes the client_id and can be joined directly to the blogs table.

For the NoSQL side of things, there is a lot of variability across platforms with respect to how they treat the individual fields within a document and how they can be indexed and/or made searchable. For example, you could still keep each post as a separate document and, in MongoDB at least, you could index the client_id field to make it faster to search for and retrieve the right posts, but the optimal approach might be different on another platform.

One concern with building a large document that contains all of the blog posts and comments for the client, as you suggest above, is that you could run into challenges with performance. On a query, you might have to retrieve the whole document, move it over the network, and navigate through the document to get to the item you want. This could be slow. If multiple users are adding comments at the same time, you could run into more lag as the requests are queued waiting for write locks to be released.

Decomposing into multiple documents/collections with MongoDB, for example, carries, the additional complexity of having to run multiple queries to retrieve the item you want, but it lets you read and update at a smaller grain and could result in improved performance and probably lower overall complexity in your code as you no longer need to manage the entire set of blog posts for the client as one large, complex, collection.