0

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?

7
  • 1
    What DBMS and version?
    – bbaird
    Feb 5 '21 at 19:07
  • Also, do you have an existing data model and how free are you to update it?
    – bbaird
    Feb 5 '21 at 19:12
  • The existing database is in MySQL 5.2, hoping to update to MySQL 8.0 or MariaDB 10.5. I'm allowed to make any changes to the tables that I deem necessary, though any changes might not necessarily be easy to make since existing applications that rely on the current structure would need to be migrated. EDIT: I also have permission to change to any free-to-use database system.
    – Nick Muise
    Feb 5 '21 at 19:44
  • How big is your data? Microsoft SQL Server offers multiple ways to automatically store every change to a row in a normalized form out-of-the-box, and they do offer a free version of their database system but it has a limitation of 10 GB per database.
    – J.D.
    Feb 5 '21 at 19:55
  • 1
    @J.D. I hadn't considered creating multiple databases. I'm probably going to avoid doing that, though, as a lot of problems were caused by the structure of the current database having separate tables for each water treatment machine, and having separate databases (even if only two) for what is essentially the same data would probably cause more of those same problems that I'm trying to solve. I appreciate the suggestion in any case!
    – Nick Muise
    Feb 5 '21 at 20:13
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.

1
  • Now that I think of it, I don't actually know why I thought Datapoints needed to have direct foreign-key references to Configurations - for some reason, I thought that using Datapoint.TimeStamp BETWEEN Configuration.Start AND Configuration.End as a join condition was somehow bad practice and would lead to problems. Thanks!
    – Nick Muise
    Feb 5 '21 at 21:31
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

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.