3

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

Or:

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?

  • If you put the stats into columns, what determines which row to put them in? Or would the table have only one row at all times? – John Wu Jul 12 '17 at 0:20
  • There's a third option which is to make a table for each statistic. Adding a table can be done while the database is live and will never invalidate existing queries. (Adding a foreign key constraint, as you are likely to want, is slightly more invasive but with cascading delete still completely backwards compatible.) Unless the names are meaningless as far as the data model is concerned, this is arguably the "right" thing to do, but it can understandably be not worth the effort. – Derek Elkins Jul 12 '17 at 2:12
5

I would lean strongly towards your second, normalized example (three columns) for the reasons you've provided:

...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.

Both of these situations will be much better served with the flexibility of the normalized schema.

I believe that the "spreadsheet-style" approach in the first schema should only be used when you are reasonably certain that you will not be adding more columns any time soon.

There could be technical reasons to pick the first schema, but those are probably quite rare.

0

What you are asking about is fat tables vs. skinny tables, sometimes short-fat vs. tall-skinny. See here, for example.

While I would argue that fat tables' duplication of content types violate normalization, there is some debate over this, but here's why I prefer the skinny approach:

Try writing a query to sum or avg the values in both fat and skinny forms. You'll see that for the fat form, it is quite complex as you have to name each specific column, as well as edit the query each time the table is extended (to have other columns). Whereas for the skinny approach, you write one simple query and you're done.

Now add capture of min and max to both forms. Harder with the former as you have to add multiple columns; easy with the latter, you just add those two columns.

Now try to find the min of the min or max of the max with both forms...

Generally, the skinny form allows us to work with whole columns of values that are related to each other much better, e.g. for statistics. For another example, you can enumerate the names easily with skinny table, whereas with the other approach, you need to query the system tables to find the names of the columns.

However, some like the fat approach as you're dealing with a single row (usually representing an event of some sort), and that can make it easier to detect missing values or deal with the event as a whole — whereas with the skinny approach you have to search for missing values or to compose multiple rows into single entity representing the event.

So, I think it comes down to how often the capture list changes, and the maintenance of the commands and queries as a result of changes to the capture list.

  • 1
    You'll see that for the fat form, it is quite complex as you have to name each specific column don't you have to filter by name and hence hardcoding strings in the 2nd approach? At the time of running queries, which one do you think is going to have better performance? Are not skinny tables somehow a EAV model? I do the question because I have read many criticism in SE about this sort of models. – Laiv Jul 12 '17 at 6:43
  • @Laiv, indeed there are arguments for both sides. What I'm talking about there is that if you want to compute a sum, min or avg across all the values, in fat form, the query needs to mention each column that has values, whereas in skinny form it need only mention the Value column. – Erik Eidt Jul 12 '17 at 14:30
  • That's a great point you make about the pain of having to name the columns in a "fat" table. I ran into this problem in a timesheet application; all of the time slots had to be named in every single query to sum them for any sort of total. It was painful! – DaveGauer Jul 12 '17 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.