Structuring time-series data in the database

Hi All,

I’m pretty new to Bubble and so I apologise if this is a basic question. I’m working on a financial planning app for which time-series data needs to be stored on a monthly basis. The primary fields which need to be stored per month are: monthly investment amount, market movement and closing asset balance. If you imagine looking at a mutual fund statement, if will typically show how an investment balance has changed from one month to the next, this is what I’m trying to achieve.

My question is how best to structure the database to accommodate this? These fields will need to be stored per client per month, and I’m trying to figure out the most efficient way of storing such info without the database getting out of hand.

Thanks in advance for the help!

I would create a table called “time series data” or whatever you prefer with all of the relevant inputs, and then on the user table create column called “user time series data” as a list with the type “time series data”.

Now whenever you create a data you simply add it to that users list and its in the format you are looking for.

Thanks, that helps!

If I’m understanding correctly, this would result in creating the relevant columns for each month within the "“time series data” table? Then as time goes by, just creating additional columns for each month? So for example after 5 years there will be 60 monthly columns?

I would just have …

Time Series

Timestamp (date)
metric 1
metric 2

Then the change in metric is current metric 1 - search for time series (timestamp < current timestamp) sorted (timestamp) :firstitem

Thanks Nigel!