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
DATETIMEvalue 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
DATETIMEvalue and some numeric value - each time series will (of course) have multiple data points (1→N)
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?
