-1

I am recording the data for 3 counters and have the choice of using either of following schema:

Date|Sensor|Value

Date|Sensor1Value|Sensor2Value|Sensor3Value

When visualizing using either of the above schemas, x-axis will be the date. In case of 1st schema the sensor will be the legend and value will be the y-axis.

Whereas in case of the 2nd schema, each column will need to be added as y-axis, and there will be no legend.

What amongst the above 2 schemas are better suited for reporting (plotting graphs)?

variable
  • 8,262
  • 9
  • 95
  • 215

1 Answers1

3

The best answer will depend on 3 things:

  1. the type of visualizations you're trying to build
  2. which visualization(s) tool you're planning to use and
  3. if you plan to add more sensor values in the future

Essentially, you're either going to pivot your data when storing it (second schema model with 1 column for each value) or you're going to store the data and rely on the visualization or your database query to perform the pivot logic.

In my experience in working with BI and analytics tools, it's almost always better to store data using the first Model (Date | Sensor | Value). This will provide the most flexibility when it comes to visualization tools and also if you need to add future sensor values, you won't need to modify your database table structure. If you need to convert your data into the second model, you can always build a View or temp table that uses a dynamic pivot query.

David A
  • 31
  • 4