0

I am trying hard to create a database design from scratch, that allows to store multiple different time series, where each one may be segmented into individual epochs. Inserting new data points for a time series must not require explicit knowledge of the corresponding epoch. However, the epochs must be assigned to the data points in a usual query.

It would be preferable to utilize existing infrastructure, which is SQL SERVER 2017 in this case.

My approach

So far I have been trying do accomplish this using 3 tables:

  • timeseries
    • each entry represents an independent time series incl. info about that time series
  • epoch
    • each entry represents an epoch incl. info about that epoch
    • an epoch is assigned to one specific time series
    • time series can have multiple consecutive epochs (1→N)
    • each epoch has a DATETIME value marking both its start and the end of the previous epoch of that time series
  • sample
    • each entry represents one data point of a specific time series
    • a data point is comprised of a DATETIME value and some numeric value
    • each time series will (of course) have multiple data points (1→N)

ERD draft

The problem arises from the fact, that epochs and data points need to be somehow matched via their corresponding DATETIME values. An epoch can contain multiple data points (1→N) which makes my problem look a bit like the one in this question. However, in order to identify the correct epoch for a data point, I currently need to find the most recent epoch start time for that time series which is smaller than (or equal to) the DATETIME value of the data point itself. This maximum search does not scale well for large numbers of epochs up to the point where a single query takes forever to finish.

I can get decent query performance by adding the epoch ID as a foreign key into the sample table via a trigger on insert/update (see highlighted connection in the ERD). This way I only perform the costly maximum search once and the main use case (querying data) is not affected by the problem.

So far I have been assuming that both epoch IDs and start times are strictly monotonically increasing, so I can search for the maximum epoch ID instead, but I do not know how to enforce that.

I have also tried to mark the most recent epoch for each time series with a flag called active to avoid the maximum search, but I do not know how to ensure, that these flags stay consistent with the DATETIME values at all times. Theoretically it is also possible to add data to a previous epoch, so I cannot rely on the active flag in all cases (although this is not a standard scenario and it won't hurt much to disallow that).

Here is my current source code to (re)create the tables/views/triggers:

BEGIN TRANSACTION rebuild_DB;

DROP TRIGGER IF EXISTS dbo.trig_sample_epoch;
DROP TRIGGER IF EXISTS dbo.trig_epoch;
DROP VIEW IF EXISTS dbo.view_ts_ep;
DROP VIEW IF EXISTS dbo.view_ts_ep_sa;

DROP TABLE IF EXISTS dbo.sample;
DROP TABLE IF EXISTS dbo.epoch;
DROP TABLE IF EXISTS dbo.timeseries;

-----------------------------------
-- tables
-----------------------------------

CREATE TABLE dbo.timeseries (
    "ts_id" INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
    "name" VARCHAR(50) NOT NULL
    );

CREATE TABLE dbo.epoch (
    "ep_id" INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
    "ts_id" INTEGER NOT NULL,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "active" BIT NOT NULL DEFAULT 1,  -- new entries are active by default, trigger sets old entries to 0
    FOREIGN KEY (ts_id)
        REFERENCES dbo.timeseries (ts_id),
    CONSTRAINT AK_tsid_epid UNIQUE (ts_id, ep_id)
    );

CREATE TABLE dbo.sample (
    "sa_id" INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
    "ts_id" INTEGER NOT NULL,
    "ep_id" INTEGER DEFAULT NULL,
    "val" NUMERIC(19,9) NOT NULL,
    "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ts_id)
        REFERENCES dbo.timeseries (ts_id),
    FOREIGN KEY (ts_id, ep_id)
        REFERENCES dbo.epoch (ts_id, ep_id)
    );
GO

-----------------------------------
-- views
-----------------------------------

CREATE VIEW dbo.view_ts_ep AS
    SELECT
        ts.ts_id, ts.name,
        ep.ep_id, ep.created_at, ep.active
    FROM dbo.timeseries as ts
        LEFT JOIN dbo.epoch as ep ON (ts.ts_id = ep.ts_id)
    WHERE ep.active = 1;
GO

CREATE VIEW dbo.view_ts_ep_sa AS
     SELECT 
        ts.ts_id, ts.name,
        ep.ep_id, ep.created_at AS ep_created_at, ep.active,
        sa.sa_id, sa.val, sa.created_at as sa_created_at
     FROM dbo.sample AS sa
         LEFT JOIN dbo.timeseries AS ts ON (sa.ts_id = ts.ts_id)
         LEFT JOIN dbo.epoch AS ep ON (sa.ep_id = ep.ep_id);
GO

-----------------------------------
-- triggers
-----------------------------------

CREATE TRIGGER dbo.trig_sample_epoch ON dbo.sample
AFTER INSERT, UPDATE
AS
   -- The following code takes long if the epoch table is large.
   IF TRIGGER_NESTLEVEL(OBJECT_ID('dbo.trig_sample_epoch')) <= 1
   BEGIN
       WITH source1 AS
       (
           SELECT inserted.sa_id AS sa_id, MAX(dbo.epoch.ep_id) AS ep_id   -- <<<<< using MAX(ep_id) requires strictly monotonically increasing IDs + DATETIME values
           FROM inserted LEFT JOIN dbo.epoch
               ON (inserted.ts_id = dbo.epoch.ts_id AND inserted.created_at >= dbo.epoch.created_at)
           GROUP BY inserted.sa_id
       )
       MERGE dbo.sample AS target1 USING source1
       ON target1.sa_id = source1.sa_id
       WHEN MATCHED
           THEN UPDATE SET target1.ep_id = source1.ep_id;
   END;
GO



CREATE TRIGGER dbo.trig_epoch ON dbo.epoch
AFTER INSERT
AS
    -- The following code does not ensure that all epochs are marked correctly as active/inactive.
    BEGIN
        UPDATE dbo.epoch
        SET dbo.epoch.active = 0
        WHERE dbo.epoch.ts_id IN (SELECT inserted.ts_id FROM inserted)
            AND dbo.epoch.active = 1
            AND dbo.epoch.ep_id NOT IN (SELECT inserted.ep_id FROM inserted);
    END;
GO

------------------------------------------

COMMIT TRANSACTION rebuild_DB;

Question 1
Is there a more elegant way to avoid performing a maximum search for each new data point in a situation like this?

Question 2
If the solution involves something like the active flag I used: How can I make sure that there is at most one epoch marked as active?

1 Answer 1

0

I developed such a schema many years ago for a commercially available equipment tracking system where the data-gathering component turned off and on at odd times (epochs).

You are on the right track with epochs but I don't think the active flag is necessary as the schema / processing should be "good for all time".

Let me think how I did it and re-read your progress. I will need a day or so to get back into it and if no one else has thoughts, get back to you.

=====================

Let's not use box-and-arrow pictures.

===========

Given Data from the remote unit:

TimeAtStartOfEpoch  TimeOfSample        CurrentRunTimeInEpoch

And Epoch created as the data is processed onsight:

TimeAtStartOfEpoch  CumulativeRunTimeAtStartOfEpoch

============

Where "run time" is the run time of a vehicle "meter" but, the meter gets reset every once in a while as the vehicle is maintained (Epochs).

Let's say there already are some Epochs...

  1. Join (all the Data table) with (all the Epoch table) so that Data with no Epoch shows up and can be used to create new Epochs.

  2. Create missing Epochs (but with no CumulativeRunTime column values).

  3. Join again and all data has Epochs.

  4. Write some procedural code to scan the above join, calculate and save running, cumulative run time for Data and Epochs that have no such values.

Some observations:

A) I hope this is accurate and clear. It's been a while.

B) Create an initial attempt stripped of everything but very, very basic SQL and (TSQL) code.

C) Don't be afraid to initially let the DBMS handle the full cost of the above joins. You need the clarity to start. Layer in performance and data integrity as needed. Perhaps the joins have ("date" > "a month ago") phrases added to them after a while. As you add in performance and data integrity, if it doesn't work as expected you can retreat and figure out the cause.

D) Also, don't be afraid to code sequential passes through the data. If the problem requires two passes through the data, trying to "optimize" the solution by arranging things to do all the work in one pass is, false economy at many levels. It is, not SQL.

You have a great start there. Good luck.

Tom Alborough

9/5/2023

If this still needs answering: I'm seeing if I can share more details about the design - the product is patented in the US and Europe.

1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Sep 4, 2023 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.