Data Model for large sub arrays - storing number of rows in a separate field

[devreso...@gmail.com]

Original data model: I am designing a system that I initially came up with a following document structure for a collection A with two nested sub arrays B and C so that when I query a document A, I get all data related to it.

CollectionA

fields: 

{

a_id,

a_name,

a_description,

Array of object B [

{

b_id,

b_name,

b_type,

Array of object C [

{

c_id,

c_name,

c_amount

},...

]

},...

]

}

Issue:

The issue now is The size of array B could be anywhere between 0 to 100 and The size of array C could be anywhere between 0 to 2000 per each B object.

That becomes a huge single document to query and pass to client (browser).

The requirements I have are :

1) Query Collection A based on id or name and get details of A, plus details of all B objects with count of C for each B object.

2) Query by b_id and get details of B object and get all (or first 200) C objects.

3) Query by b_id and get subsequent set of C objects (if the original result is more than 200).

Proposed data model:

I am thinking of moving C into a separate collection as below.

1) Now when I query collection A, how do I get the number of C objects for each B object inside the document?

2) Is there a way I can store a calculated field inside B such that everytime a document is added to C with b_id, the field in B object is incremented by 1?

3) Any better approach is appreciated.

CollectionA

fields: 

{

a_id,

a_name,

a_description,

Array of object B [

{

b_id,

b_name,

b_type,

},...

]

}

Collection C

fields:

{

a_id,

b_id,

c_id,

c_name,

c_amount

},...