2

I have a 'product' document, which has an attribute 'categoryId' to reference the 'category' document. When I fetch a product, I would like to be able to get the name of the category, not only the id. I know that I should create a view for this, but I'm new to couchbase, and I've looked around in google but I couldn't find how exactly to do it.

this would be the product document

{
    "id": 1,
    "name": "Guitar",
    "categoryId": 5,
    "jsonType": "product",
    ... (other fields)
}

And this would be the category document

{
     "id": 5,
     "name": "Musical Instruments",
     "description": "",
     "jsonType": "category"
}

Thanks!

Community
  • 1
  • 1
damian
  • 4,024
  • 5
  • 35
  • 53
  • Could you please provide additional specifications - e.g. how many records do you expect to return in one call, how does CB fit into your architecture, how many objects in your database, etc. – theMayer Oct 22 '14 at 12:14
  • I usually show a list of products (most recent at home page, or the results for a user search). That's why product and category name have to be in one single document/view. I use elasticsearch to make the search and provide document IDs, and then CB reads those IDs. This is a new website I'm developing so for now very few objects. once this goes live it won't be very big, maybe 1.000 records? – damian Oct 24 '14 at 15:02
  • My advice - pick one of the two. Either use all Elasticsearch or all Couchbase. You might find Elasticsearch easier to get running for a small setup. – theMayer Oct 25 '14 at 02:26

2 Answers2

1

What you are trying to do here is to create an inner join on Couchbase. Now, your question doesn't exactly specify output format for your data, but there is a bit of a design conflict here in my opinion. Document databases really work best when the things you wish to assemble are all located within the same document. In essence, this looks like a relational design put into a non-relational database - a square peg in a round hole so to speak.

That being said, I would explore the use of a N1QL ("nickel") query to accomplish this. While I haven't yet utilized the N1QL tool yet (because I designed my database to not need joins, etc.), it shows promise, especially for small data sets. http://docs.couchbase.com/prebuilt/n1ql/n1ql-dp3/#join.html

The other alternative is to use web service logic to perform concurrent lookups and deliver the results. Once you are able to give more details on your application, I can come back with my approach to help solve your problem.

I answered a very similar question here - see if this helps.

Community
  • 1
  • 1
theMayer
  • 15,456
  • 7
  • 58
  • 90
  • Hi @rmayer06, thanks for your interest. I don't really care about the output format as long as it has the name of the category. It could have a "categoryName" attribute, or a "category" object. I understand what you say about inner join, but how else could I design this? embed category in document? in that case, if category is updated (though I don't think that is likely) I would have to update all products with that category – damian Oct 23 '14 at 14:13
  • Please respond to the additional questions I asked earlier about scale, etc. (comment on your original post). And, regarding updates to category name - you are correct. Design trade-off. – theMayer Oct 23 '14 at 21:44
  • I gave you the reward because it was about to expire. So in your opinion I could use elasticsearch alone? Is it because my system is rather simple? How do I handle security, since elasticsearch does not provide authentication mechanisms? – damian Oct 25 '14 at 15:54
  • 1
    I would recommend Elasticsearch. If your application requires security, that has to be implemented through a web service layer. Use the database to store data, a web service to feed your page, and javascript/jquery for the front end. – theMayer Oct 27 '14 at 18:59
  • Have a look the link to my other answer on SO - I added it to the end of my answer above. – theMayer Oct 29 '14 at 14:00
1

I do not know all of your access patterns for this data, but if this particular one is critical, why not just put the category name in the product document? and for the key of the category names, in case you do need to go get that specific object, make it something meaningful. Doing this will not increase the size of the document by much either. In Couchbase an object's key can be 250 bytes, so use them to make a key that your app can create from the information it has. For example, the key for your category document above would be "category::Musical-Instruments". Then if your product document has that embedded, it could go get the details of that category by the key quickly, or if you just wanted the full name of the category, your app slices off the category:: and replaces the - with a space. or whatever way you want to do this.

Another option and a slight variant on the above is to have the category keys in the product document be an array of keys (if your product might need to be in several categories) and again the keys for the category documents are something meaningful and something your app can parse if need be.

In summary, "id" : 5 is valid, but Couchbase provides ways for you to make things meaningful to your app and can allow for super fast lookups even at scale. This is the route I would go down more than likely and what I would recommend to you to think about to ultimately solve your problem. I would recommend reading this blog post for more information along these lines.

NoSQLKnowHow
  • 4,449
  • 23
  • 35
  • Hi @Kirk, I thought about this, but what if category name changes? the id can't be changed so the category has to be deleted and a new one created. also, categories are a tree, I'm using materialized path. so I would have to update all products that belong to this category and all subcategories. I don't think this will happen often, so if there's no other way I guess I'll have to do it... – damian Oct 24 '14 at 15:05
  • If it were me, I would say solve for +95% of the use case and plan for the 5% or less edge cases. If you change category names so rarely, then it might be worthwhile to embed the name like I have it. Or you can spend a huge amount of time coding around that case that might never happen. Then you create a documented process/plan for how to deal with those very rare edge cases where you want to change the category name. Also, I am not saying there is no other way to do it. I am just saying how I would solve this given what you have told us. – NoSQLKnowHow Oct 24 '14 at 17:42