For a web application we are building I am supposed to overhaul our statistic collection service.
Basically we are collecting simple statistics such as positive comments, negative comments, views, likes, etc.
We are not using any analytics API and we are supposed to store metrics in a relational database.
What we came up with is the following database design:
statistics: from:DateTime, to:DateTime, ref_id, metric_id, value metrics: id, name
The Data is collected by a job that runs every few minutes and collects data from various sources. For every metric a seperate record is created. When the metrics value does not change instead of creating a new record the
to column for the
metric_id will be extended to the current DateTime.
So far so good. In a perfect world that idea should work good enough but unfortunately that probably won't be the case. There are basically three sources of failure:
- The job does not run for some reason
- A bug in the application causes metric/stat records to not be created
- 3rd party APIs which collect data may fail
In this case gaps will occur that aren't supposed to be there. My question is: Are there any best practices to "patch" those gaps? My thoughts on that:
- Silently extend the
toTimestamp of the record before the gap occured to the
starttime of the latest "after-the-gap" record
- Interpolate statistics linaeally: Subtract the metrics of the record after the gap and the record before the gap and distribute the results across the timespan of the gap
I don't like both ideas. Silently extending seems to be just a dirty cover-up for failure but at least end users won't be affected. The second options looks a bit better but results will still be inaccurate because statistics are most often not a linear matter.
I would appreciate any thoughts or ideas on that matter.