Correct database structure for daily changes tracking

Hello, I’m working on an app to track the changes of users statistics, I would simply get the stats of user account each day and use it later on to calculate weekly/monthly statistics, something like the following-

In a conventional database you’d simply create a table for each account, but I doubt that’s the correct way to do it with bubble.

Previously with bubble I have tried using fields “date” and “video count” as" list of dates" and “list of numbers
But there has been some problems with duplicate values not being saved so as a result we had uneven number of dates and numbers, seems like it also has a limitation of 1000 or 10.000 lines?

Please let me know if you know of a way to do it correctly within bubble, or if you are using external databases for it, thanks!

1 Like

Hi there, @tomajx… if I understand your post correctly, it sounds like you just need a data type that has fields for the stats you want to track. Then, you would create a new thing in that data type each day for each user, and you would be able to get weekly/monthly statistics by grouping the things in the data type by user. Does that sound like what you are trying to do?


Thanks Mike!
You know what it actually kind of makes sense, it would be something like

I’m only a bit afraid of clogging up the database, say for example we would monitor 1k accounts
That’s 1k new rows in 1 databases table per day

Having deleted some rows from the bubble database saying it’s slow is a major understatement.
So yeah I believe it would work, but for how long that’s the question, maybe there are some alternatives, really hoping someone who has done this would share their experience

1 Like

365,000 new rows per year… eh, peanuts. :wink:

1 Like

With 1.000 accounts only :open_mouth:

1 Like

Getting a bit stuck on seemingly simple calculation, for this project :thinking:

Here’s the data type for the youtube channel

And this is how the data looks-

So every day we would add a new row to the database with channel Id, views, subscribers and video count.

Question is, how to get the sum of videos for all accounts?
It seems kind of silly, but we want to only get each channel Id’s last row by date

Tried doing it this way, but it gets adds up all the videos, while it only makes sense to get 1 row (newest by date) :roll_eyes: :thinking:

I could be off base here, but there will only be one record per account per day, right? If that is the case, could the expression be Search for ytaccountss:each item's videos:sum with a constraint on the search for accounts where Created date > Current date/time:rounded down to date?

1 Like

Thanks again Mike, and yes it would definitely work!

But one downside I can see is that in case we don’t grab some account details for today, or we would stop monitoring some account - there would be nothing to take for it (instead of taking the last information that we have)

1 Like

Okay, this suggestion is probably pretty lame/hacky, but I’m not sure how to use group by to do what you are trying to do, and my mind still wants to simplify the whole thing to a search with a constraint. So, just food for thought, but you could consider having a yes/no field called something like latest info that gets set to yes on the newly-created thing for each account each day. As part of the process of creating the things each day, you could run a backend workflow that finds the last thing associated with the current day’s list of accounts and clears out their latest info field (so, each account would only ever have one thing where the field is set to yes). With that setup in place, it would be easy to get the info you are trying to get by using the yes/no field as the constraint on the search.

1 Like

Yes, it’s a little hacky, but it can really work, actually going to use it, so marking it as a solution! :white_check_mark:

But to anyone reading I would leave the question open, because it would be nice to know if something like that is possible with basic search functionality, sure seems like it should be possible somethow… :thinking:

1 Like