One of our products generates vast amounts of reasonably detailed logs into a database table and we're looking to analyse them, possibly build a report from them. Reports can be run on demand, which makes this scenario quite a good candidate for performing the log analysis once and storing the results; the result set won't change once calculated, and test reports with >20 minute run times have already proven it foolish to put SQLServer to the bother of JSON decoding millions of lines of log data, looking for interesting stats, every time the report is run.
There's a process that runs already when the log is closed, that scrapes some info out of it. Upgrading that process to calculate a lot more statistics is trivial; this query is about how to store the results
I'm split between having a table with a dedicated column for each stat, and having a bunch of key-value pairs that name a stat and provide its value. The stats are all numerical values, and not every kind stat can be calculated for each log depending on which product generated the log data (example: if it was a video chat, there will be no "number of messages posted" stat). New stats may be added in future
So, should the table look like:
ID | MessagesPosted | PeopleAttended | VideoStreamsRecorded 12 | 45 | 6 | NULL 13 | NULL | 7 | 4
ID | Name | Value 12 | 'MessagesPosted' | 45 12 | 'PeopleAttended' | 6 13 | 'VideoStreamsRecorded' | 4 13 | 'PeopleAttended' | 7
I appreciate that it's possible to dynamically adapt to either a changing number of rows or a changing number of columns in the front end code and pivot operations can flip columnar data to rowar and back without a problem, and the front end can look for specific values and handle their absence regardless so really I suppose the choice is one of "stringly typed, no nulls" or "strongly typed, nulls".. Making it rowar might make nHibernate balk at it less, as the schema isn't changing drastically when management decide they want 120 new stats adding, but it feels dirtier as one day they might not want a numeric value, and I don't really want to get into storing values as string just to a retain "MostValuablePerson" stat..
Are there compelling reasons for one method over the other?