It seems like every app I've been involved with building eventually struggles with how to efficiently handle reporting.
Currently, our model flows something like this (developed internally -- no idea if this is good practice):
- Log actions/behavior to a general purpose table (basically key/value with some additional indexable fields like user_id)
- Roll up reportable actions into a summary table daily
- Roll up daily actions into weekly/monthly tables when appropriate
The general purpose table is nice, because there's a single way to log things in the app, but it makes it somewhat painful to do reporting out of since the value column ends up being a serialized version of relevant data and can't necessarily be easily queried/indexed.
My question is, is there a better way to handle this? Are there any common patterns/practices that I'm missing?
(Also, this is my first question in this SE, so feel free to tell me where to move it if it doesn't belong)