I'm dealing with a table that contains a lot of historical data. What I am doing is generating a bunch of metrics based on different combinations of column values and date periods (e.g. total number of widgets used by each person in the last six months).

Rather than doing these calculations on the fly, I have a query which is precalculating them. However I am not sure of the best way to actually store these values.

Ideas I've come up with

1. A table for every combination of columns

For example PersonWidget, PersonWidgetDepartment etc. These would have a composite primary key consisting of the the IDs of Person, Widget etc.

The problem here is that there would be a lot of tables, there are restrictions on max length for table names and creating new combinations means creating a new table every time (migrations would have to be created and so on).

2. One table for metrics with nulls for columns which aren't relevant

This would look something like:

PersonId | WidgetId | DepartmentId | TotalUsed

1          2          3              50
1          2          4              30
1          2          null           80
1          null       null           100

So when querying, if you wanted to know how many widgets a particular person used, you'd query for their ID + WidgetId IS NULL + DepartmentId IS NULL. This is obviously a bit cumbersome and doesn't quite feel right to me. The benefit is that there is only one table to deal with.

3. One table for metrics with every combination of historical columns having a metric value column

This would look something like:

PersonId | WidgetId | DepartmentId | TotalPerson | TotalPersonWidget | TotalPersonWidgetDepartment

1          2          3              100           80                  50
1          2          4              100           80                  30

There's a lot of redundant data being stored here and it means the table ends up having a huge amount of columns.

If anyone can provide input/ideas around a decent way to go about this, would be much appreciated.

  • If you are talking about hundreds, even thousands, of rows, don't bother, just calculate on the fly. Even with a million rows, you should experiment to see how bad the timing is. Meanwhile, show us SHOW CREATE TABLE for the Person and Widget tables, plus the SELECT to generate the desired output. Then we can discuss whether a Summary Table would be useful. – Rick James Aug 25 '18 at 1:05

Your Answer

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

Browse other questions tagged or ask your own question.