How can I inner join firestore collections for a paginated listview

Question

We can easily paginate a Recyclerview with the Firestore collection. But in my case, I have three dependent collections. They are

 1. product (10000000 entries expected) 
 2. category (100000 entries expected) 
 3. region (50000 entries expected)

In Recyclerview I have to show details of product, category, and region like in the below image. As we know, there is no option for inner join queries like SQL. I have category id and region id stored in each product object. After fetching the products list I have to fill the category name and the region name based on the category and region id in each product object. After building that list of products, I have to pass this to Recyclerview adapter. How can I achieve these kinds of complex architecture with Firestore to meet normal SQL features? In my case, Firestore is confirmed. Also, there will be pagination.

I tried by fetching all categories and regions in different variables while initializing the page. While paginating, I have to find and put desired category name and region name based on their ids. But we need to keep both category and region list variables with a large number of data till the end of the activity which is not a good practice. I need to overcome this. FYI: I am using Kotlin

Please take a look at the below samples of the basic structure of each document object.

Product

{
    "productId": 122,
    "productName": "product name 1",
    //other product details here
    "categoryId": 758,
    "regionId": 395
}

Category

{
    "categoryId": 90474,
    "categoryName": "category name 200",
    //other category configuration details here
}

Region

{
    "regionId": 2372,
    "regionName": "tokyo",
    //other region details here
}

Practical scenario

1. If I need to give a specific percentage of discount to specific categories, then we need to calculate it separately in each row of products.

2. If I need to show different shipping charges based on different regions, then also the same issue.

That means every time I added specific details to the category and region, then I have to update all products in the list with the updated region and category details.

Answer 1

#1 Answer: DON'T TRY TO TREAT NoSQL AS SQL.

SQL DRY principle specifically keeps data in only ONE table, and uses record ID's to cross reference between tables.

NoSQL features allow for amazing scale and speed, but you have to use it as NoSQL. More specifically "DRY" becomes "DO Repeat Yourself", not least because records are damn close to unlimited size. If "joined" data is at all close to static, it is best to COPY it to the referencing record.

Rather than cross reference between tables using Record ID's, use those tables (such as your example "categories") as the SOURCE OF TRUTH (i.e. data must be SOURCED from them) and COPY the information to the referencing record. In your example, you have:

{
    "categoryId": 90474,
    "categoryName": "category name 200",
    //other category configuration details here
}

Then your Product information becomes:

{
    "productId": 122,
    "productName": "product name 1",
    //other product details here
    "category": {
      "categoryId": 758,
      "categoryName": "category name 200"
      //other category configuration details here
    },
    "region": {
      "regionId": 2372,
      "regionName": "tokyo",
      //other region details here
    }
}

As such, when you retrieve the product records, all the "joined"information is present. The reason to include the Id's (remember document/record is quite large) is for the INFREQUENT times it needs to be updated (remember above: mostly static) you can query for records that use it, and update them. This is likely an almost vanishingly rare event.