替代超表和“正常”表之间的多到多关系。

Question

我试图在一个名为“度量衡”的超级表和一个名为“食谱”的表之间建立一个多到多的关系。

一个测量可以有多个食谱，一个食谱可以连接到多个测量。

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);

上面所示的SQL脚本是我想连接的表。由于受时间刻度的限制，上述解决方案不起作用。

时间刻度有一个约束，即不能将超表值用作外键。是否有一种替代的解决方案来在表之间创建多到多的关系，而不实际使用多到多的关系。

Answer 1

TimescaleDB是为时间序列数据设计的，其中每个点通常被附加到某个时刻，并包含所有相关数据。将每个点链接到元数据是很常见的，而元数据已经存在，但是，相反的操作是不常见的。TimescaleDB通过分块数据对时间序列数据进行了优化，因此DMLs和许多选择查询不需要访问所有块。但是，要将外键约束保持到超表中，可能需要在引用表measurement_recipe中的每个插入中触及所有块。

这个问题的用例是具有复杂度量的时间序列。提议的架构似乎是原始模式的规范化。我想它简化了对测量数据的查询。我看到两种处理复杂测量的方法：

1. 在复杂结构(如JSONB或数组)的帮助下，将数据去功能化，并将食谱和度量存储在单行或几行的measurement表中。缺点是，有些查询将很难编写，定义一些连续聚合可能是不可能的。
2. 按照问题中的提议进行了规范化，但不强制使用外键约束。它将允许存储引用值，这些值可用于连接表。由于规范化是作为转换传入复杂数据的一个步骤自动完成的，如果转换代码中没有bug，则约束将被保留。这些错误可以通过回归测试来预防。但是，在规范化模式下，不可能使用连续聚合，因为不允许连接(使用联接维护连续聚合可能需要接触所有块)。

我的建议是选择方案1，并尝试在那里变得聪明。我没有很好的建议，因为不清楚JSON中的原始数据结构是什么，查询是什么。