Deeply nested documents vs flat document structure

SQL++
1

Hi,

I am building an app leveraging couchbase as the backend and I am at a little bit of a fork in the road in terms of data schema: I can either have large deeply nested document requiring lots of unnesting OR I can go with flatter more simple documents requiring lots of joins.

I have a traditional database background where I would have a bunch of tables with foreign keys referencing other tables, but json affords us the flexibility to do it a different way, so my question is: from a couchbase perspective, which one is the most performant: doing unnesting on large deeply nested documents or doing joins on small flatter documents?

Here is an example: I have a document called race that looks like this in a deeply nested large document (such document in the app, could be over 3,000 lines long):

{
"raceid": "blahblahblah",
"boats": [
    {
    "boatid":"dffqwewe",
    "crews": [
        {
        "userid":"werwer",
       "checkedin": true
       },....
       ]
    },....
    ]
}

or I can have bunch of smaller flatter documents representing the same thing, like this ( but then I would have 100,000s documents):

{
"crewid":"sefwerr",
"userid":"sdfsdfs",
"boatid":"asdasdas",
"raceid":"sdfsferwef",
"type":"racecrew"
}
{
"boatid":"wefwerwer",
"raceid":"sdfsferwef",
"type":"raceboat"
}

Which is will provide me with the best performance? I feel like it is #2, but I am not sure as I see everyone seems to have larger deeply nested documents.

Thanks a lot in advance,
Bertrand.

2

It’s difficult to answer this question without knowing every query you want to run and the size of your data.

Generally speaking, using JOINs requires more processing. So a good rule of thumb is, denormalize, aggregate, and nest as much as it makes sense to, and leave everything else separated. This also allows you to use direct key-value lookups more (instead of N1QL). Key-value lookups will always be the fastest way to access data in Couchbase.