Reporting Patterns?

Question

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):

1. Log actions/behavior to a general purpose table (basically key/value with some additional indexable fields like user_id)
2. Roll up reportable actions into a summary table daily
3. 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)

Answer 1

Please, please, please break out all information you can into distinct columns. We get cases all the time where the data is basically a key or two and then all the info in one string column. And when it comes time to generate reports based on data in that string - you're in a world of hurt.

Answer 2

If you are using SQL Server 2005 or later you have an XML data type which can be queried.

Answer 3

What you describe is mostly what I've seen and what I've done in the past. There is a class of tools called Change Data Capture platforms that essentially do this same thing for you; they read the transaction logs and create table entries in their own repository. It has the same type of model where you have rows instead of columns and consequently it is more challenging to report on or ad-hoc query than a third normal form model. SQL Server 2005 and later have a "PIVOT" clause that makes it quite a bit easier. If you happen to be using that platform you should take a look.