Add min and max dates to site-specific and aggregate data

[amabdallah]

Let's evaluate the idea of adding two new columns to the site table (?) that store the min and max date values that exist for it within the SiteVariableAmounts_fact. The min and max are calculated whenever new data is added to that site SiteVariableAmounts_fact.
Both the min and max values are needed in our dashboard or the API (give me a list of all sites that have time-series data within this date range (1980-220). We probably could add this min and max to the date table, if that is more efficient for query speed.
It might be easier to let the data user or dashboard code do that at their end. Something to think about in our next set of schema updates.

The same need and logic applies to the AggregatedAmounts_fact table.

[rwjam]

Let's evaluate the idea of adding two new columns to the site table (?) that store the min and max date values that exist for it within the SiteVariableAmounts_fact. The min and max are calculated whenever new data is added to that site SiteVariableAmounts_fact.

How would that work if the site had multiple records with different min and max dates? I think min and max date values are specific more so to the record, not the site. Adding the min and max dates should probably go into the SiteVariableAmounts_fact table.

Both the min and max values are needed in our dashboard or the API (give me a list of all sites that have time-series data within this date range (1980-220). We probably could add this min and max to the date table, if that is more efficient for query speed.

Adding it to the date table could work too, but you would still need to add a min and max ID value to the SiteVariableAmounts_fact table so that bridging element would exist.

It might be easier to let the data user or dashboard code do that at their end. Something to think about in our next set of schema updates.

Maybe. Storing those features within the data would make it easy to populate the API with. But if it’s just an APP feature, adding it to the app itself should be easy. Might be worth asking Ryan M and Dub and see what they think.

The same need and logic applies to the AggregatedAmounts_fact table.

Same issue. Date information is record specific, not necessarily related to the site.