Is it possible to store historical configuration settings for each row of data without cramming all the configuration settings into each row of data?

Question

For background: I was recently hired as a database engineer for a water treatment company. We deploy water treatment machines to sites across the country, and the machines treat water and send continuous data back to us regarding the state of incoming water (flow rate, temperature, concentration of X in incoming water, etc.), and regarding the treatments the machine applied to that water at that point in time. Over time, sites (and their various components) change a lot: a machine might break down and need to be replaced, a different concentration of chemical may be used to fill the machine's tanks, its flow meters and other sensors might be recalibrated or set to scale differently, its chemical pumps might be replaced, and on and on. These affect the interpretation of the data: for example, if 5 mL of chlorine was added to the incoming water at 01/01/2021 12:00:05, that means two completely different things if the chlorine was 5% concentrated or 40% concentrated.

Water treatment datapoints are identified by a composite key consisting of the ID of the site, and a timestamp. It would be easy to store configurations if the only data that mattered was current data, as I could keep configuration settings in the Site table and pull them up for datapoints as needed. But we need to be able to correctly interpret older data. So, I thought about storing configurations in its own table table, which would track all the settings for each site over each time period, but it's not possible to create a foreign key between the continuous timestamps of the datapoints and the start/end dates of the configurations - the closest thing would be some kind of range check, like "Datapoint.TimeStamp BETWEEN Configuration.Start AND Configuration.End". So the only other option I see is to store every configuration setting for every datapoint alongside each datapoint, but that seems like a terrible solution given how many configuration settings there are and how many datapoints are generated, especially since most of the settings don't even change often.

So, is there a way to store historical configurations for each row of data in a way that is at all normalized, or is the only possible solution to cram all the settings into each datapoint?

Answer 1

What you are describing is called slowly changing dimensions (SCD), with each configuration item being essentially a dimension for the measurements (facts). If you don't want to switch to a database that supports temporal queries, you can still implement one of the SCD approaches in MySQL (which temporal tables still do under the hood anyway). You don't need to declare Datapoint.TimeStamp as a foreign key, and there's nothing wrong with using Datapoint.TimeStamp BETWEEN Configuration.Start AND Configuration.End as a join condition.

Answer 2

Here are some random thoughts. I guess the configuration could change without the existence of a datapoint at that particular point. First I would have a table for the sites:

CREATE TABLE sites
( site_id ... NOT NULL PRIMARY KEY
, <other attributes>
);

Now, you can have your table relate to that

CREATE TABLE measurements
( site_id ... NOT NULL REFERENCES sites (site_id)
, measurement_ts ... NOT NULL
, <measurements>
, PRIMARY KEY (site_id, measurement_ts)
)

It is not clear to me from your description if you should futher normalize the measurement values or not, so I'll leave them as-is.

Now you can add the configuration as:

CREATE TABLE configurations
( site_id ... NOT NULL REFERENCES sites (site_id)
, begin_time TIMESTAMP NOT NULL
, end_time TIMESTAMP
, ...
, PRIMARY KEY (site_id, begin_time)
)

Neither is it clear for me whether the configuration values should be further normalized, so I'll leave that as well

When you change the configuration, you close the previous one by setting end_time to now, and add a row with begin_time = now, end_time = null.

@J.D. mentions one of the commercial vendors with some support for temporal data as an alternative. You may want to have a look at IBM Db2 community edition as well. The limitations are 100Gb storage, 4 cores and 16Gb ram. In case you are interested, you can find out more at ibm-db2-developer-community-edition

For temporal tables, in particular, you can find more info at Time Travel Query using temporal tables