database - 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 if the only data that mattered was current data, as I could just store the configuration settings on a Site level 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 another table, trackingall 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?

question from:https://stackoverflow.com/questions/66068544/is-it-possible-to-store-historical-configuration-settings-for-each-row-of-data-w

Answer 1

If I understood your request :

1 - a water datapoint is identified by a composite key consisting of the ID of the site, and a timestamp :

• SiteID
• TimeStampID

2 - a water datapoint can have multiple configurations when a break down happens for example :

• ConfigurationID
• StartDate
• EndDate

Let's consider a DataPoint having the following information for a specific day :

   DataPoint SiteID TimeStampID
   1001      101    01-02-2021 09:00:01
   1001      101    01-02-2021 10:20:31
   1001      101    01-02-2021 17:45:00

At that day, a break down started at 11:01:20 and ended at 11:34:22.

ConfigurationID DataPoint StartDate           EndDate
155             1001      01-02-2021 11:01:20 01-02-2021 11:34:22