How to handle large data?

[BlueTech]

Hi,

I want to build an app for a company that has around 10,000 transactions pre week.

It's a subscription based module, with 5,000 subscribers, so every week a row is added for each subscriber with a due/bill and another row with an income when the payment appears on the bank statements.

Usually I use google sheets as the data source. I know google has a limit of 10 million cells. This means that once a year for example I will need to combine all transactions for each subscriber from the previous year into a single row and save the old transactions on a separate database.

This isn't end of the world, but I want to know how would you guys handle this? Would you start with a different database, like Bigquery etc? Or perhaps I should look for an alternative to AppSheet?

[AleksiAlkio]

First question.. how many app users?

[BlueTech]

5-10 users. 

[JSO]

Second question: How is the data loaded?

[BlueTech]

The dues are generated by a bot, the payments will be entered either by a csv upload, or potentially integration with the payment provider, perhaps via apps script.

[AleksiAlkio]

I would do this with a real database like SQL or MySQL so you won't need to think about backups.. though in the dabase itself, but not no need to copy data from data source to another one.

[BlueTech]

Can you then see all rows on appsheet? Or would need to create a filter to only show the last 100,000 rows? I would also need to be able to get the balance for each user, considering all of their transactions, not just whatever is currently visible on appsheet. 

[AleksiAlkio]

How many different users e.g. in a year?

[JSO]

For the balance, I think it's better to save the value in the user table and add the amount with each new transaction or, in case of modification, first subtract the value of the transaction and then add the new value. This way it will be quick to have it available in an instant.
On the other hand, you must have a process that is capable of recalculating the balance when you order it (in case there is any discrepancy).

[AleksiAlkio]

Another way is to let the database to calculate the balance.

[jones007]

Hello I am having the same issue i apologize for bothering you I had a question if you could give me a minute I’m trying to design essentially the same thing just I don’t know how to even deploy to iOS and android yet could you help me out?

[BlueTech]

Would MySQL be the right fit for my app? Is updating and adding rows seamless?

[Rifad]

[Updated] Supabase mets AppSheet - Google Cloud Community  Supabase might be a good choice.

[BlueTech]

I'm currently testing out data partitioning, as it seems like even with a google sheet, where you can have up to 10 million cells, you still can't have more than 100,000 rows of data.

My idea is to partition every quarter into its own partition.

However, I'm not sure if data partitioning will solve my issue. It seems like you can only have one partition visible at a time.

I wonder, if there's a way to have a single partition visible for transactions, where on the main transactions view I can only see let's say the last three months, but when going into a contact's profile, I can see transactions across multiple partitions?

[AleksiAlkio]

I'm afraid you can only read one partition at the same time.

[BlueTech]

Thanks, then I guess I will have no choice but to use a SQL database with filter rules to display only transactions of the last few months on the default transactions table.

I have so many questions now.

1) Would I be able to search for a transaction within ALL transactions?

2) Can I see all transactions for a specific customer?

3) Can I still have a dashboard that displays the total by year/month/category etc?

Please if someone has built a similar app with huge amount of data please share your experience.

Thanks!

[AleksiAlkio]

#1 - Yes, but you need to create your own search functionality and when you save the search, it syncs and security filter reads the data properly. Meaning.. you need to build the security filter in a correct way so it will do that.
#2 - Same as above
#3 - Because you don't have all the data in the app because of the volume, you need to create a view in the database for the app that groups the data in a way so the dashboard is able to show the totals per customer.