CODEWITHSUNDEEP
sqlpostgresqltimescaledb
Collin
Collin

Reputation: 954

Alternative for a many to many relation between a hypertable and a 'normal' table

Im trying to create a many to many relation between a hypertable with the name 'measurements' and a table with the name 'recipe'.

A measurement can have multiple recipes and a recipe can be connected to multiple measurements. 

DROP TABLE IF EXISTS measurement_ms;
CREATE TABLE IF NOT EXISTS measurement_ms
(
    id                      SERIAL,
    value                   VARCHAR(255) NULL,
    timestamp               TIMESTAMP(6) NOT NULL,
    machine_id              INT          NOT NULL,
    measurement_type_id     INT          NOT NULL,
    point_of_measurement_id INT          NOT NULL,
    FOREIGN KEY (machine_id) REFERENCES machine (id),
    FOREIGN KEY (measurement_type_id) REFERENCES measurement_type (id),
    FOREIGN KEY (point_of_measurement_id) REFERENCES point_of_measurement (id),
    PRIMARY KEY (id, timestamp)
);

CREATE INDEX ON measurement_ms (machine_id, timestamp ASC);
CREATE INDEX ON measurement_ms (measurement_type_id, timestamp ASC);
CREATE INDEX ON measurement_ms (point_of_measurement_id, timestamp ASC);
-- --------------------------------------------------------------------------
-- Create timescale hypertable
-- --------------------------------------------------------------------------
SELECT create_hypertable('measurement_ms', 'timestamp', chunk_time_interval => interval '1 day');


DROP TABLE IF EXISTS recipe;
CREATE TABLE IF NOT EXISTS recipe 
(
    id                      SERIAL PRIMARY KEY,
    name                    VARCHAR(255) NOT NULL,
    type                    VARCHAR(255) NOT NULL,
    code                    INT NOT NULL
);


DROP TABLE IF EXISTS measurement_recipe;
CREATE TABLE IF NOT EXISTS measurement_recipe
(
    id                          SERIAL  PRIMARY KEY,
    measurement_id              INT     NOT NULL,
    recipe_id                   INT     NOT NULL
    FOREIGN KEY (recipe_id) REFERENCES recipe(id),
    FOREIGN KEY (measurement_id) REFERENCES measurement_ms(id)
);

CREATE INDEX fk_measurement_recipe_measurement ON measurement_recipe (measurement_id ASC);
CREATE INDEX fk_measurement_recipe_recipe ON measurement_recipe (recipe_id ASC);

The SQL script as shown above are the tables that i want to connect. The solution above doesnt work because of the constraint by timescale.

Timescale has the constraint that you cant use hypertable values as a foreign key. Is there a alternative solution for creating a many to many relationship between tables without actually using a many to many relation.

Upvotes: 1

Views: 943

Answers (1)

k_rus
k_rus

Reputation: 3219

TimescaleDB is designed for time series data, where each point usually is attached to some moment in time and contains all relevant data. It is common to link each point to metadata, which are already present, however, doing opposite is uncommon. TimescaleDB is optimised for time series data by chunking data, so DMLs and many select queries don't require to touch all chunks. However, maintaining foreign key constraints into hypertable might require to touch all chunks on every insert into referencing table measurement_recipe.

The use case of the question is time series with complex measurements. The proposed schema seems to be normalisation of the original schema. I guess it simplifies querying the measurement data. I see two approaches to deal with complex measurements:

  1. Keep data denormalised and store both recipes and measurements in measurement table in a single row or few rows with help of complex structures such as JSONB or array. The drawback is that some queries will be difficult to write and defining some continuous aggregates might not be possible.
  2. Do normalisation as proposed in the question but don't force foreign key constraints. It will allow to store referencing values, which can be used for joining the tables. Since the normalisation is done automatically as a step of transforming incoming complex data, the constraints will be preserved if there are no bugs in the transformation code. The bugs can be prevented through regression testing. Still with normalised schema it will not be possible to use continuous aggregates, since joins are not allowed (maintaining continuous aggregates with joins might require to touch all chunks).

My suggestion is to go for option 1 and try to be smart there. I don't have good proposal as it is unclear what the original data structure in JSON is, and what the queries are.

Upvotes: 2

Related Questions