Hello,
I’m having dilemma about designing documents. Let’s take a product document for example. The current design will be consisting multiple documents to get a product details (PRODUCT, SKU, VENDOR, CATEGORY)
Currently, I’m assigning foreign keys of sku, vendor, and category(3 level hierarchy of categories) to product because the fields are dynamic and I want to avoid updating multiple documents at once.
To get product details (this is my query fragment):
SELECT vendor_name, sku_price, category_1, category_2, category_3
FROM product
join sku…
join category…
join vendor…
Q1: Is my query terrible?
Q2: Is it better to put vendor name, categories… etc values in product document instead of foreign key and just update them once the primary document is updated?
I’m using Couchbase Server v5.1
This really depends on how much data you are querying and how well your data is indexed. That many joins isn’t inherently terrible, but if the query is taking too long (even with optimized indexes), then…
This will probably be faster, but it does require you to update values in multiple places. Again, this isn’t inherently bad, it’s just a little more work. If you were using a newer version of Couchbase Server (Enterprise), you might be able to use Eventing to do the updating on the cluster instead of in your application layer(s).
As of now, I didn’t encounter any performance issue. Is there a better approach in my query?
Thanks for you response 
It’s a possibility. I would recommend:
a) Trying the query optimizer. A covering index, in particular, might be the way to go (if you aren’t already using a covering index).
b) Create a new forum post here on N1QL with your full query and maybe some examples of the documents you are querying.