将不同表的行组合为可以具有值的一行

Question

我试图为以下问题找出一个数据模型：

我有一个energy sources的列表

| name             |
|------------------|
| windmill         |
| oil              |
| water_powerplant |

attributes列表

| name    |
|---------|
| location|
| size    |
| oil_type|

属性values列表

| name    |attribute|
|---------|---------|
| offshore|location |
| onshore |location |
| big     |size     |
| small   |size     |
| crude   |oil_type |
| bunker  |oil_type |

以及哪个energy sources可以拥有哪个attributes的列表

| source          |attribute|
|-----------------|---------|
| windmill        |location |
| windmill        |size     |
| oil             |oil_type |
| water_powerplant|size     |

我现在需要的是一个表，可以存储源值和属性值的每个组合的能量值。例如，如何存储small offshore windmill的值？我能想到的最好的方法就是想出一些自定义约定，将多个属性值组合成一个值.

| source          |attribute_values             |kwh_per_hour|
|-----------------|-----------------------------|------------|
| windmill        |size=small location=offshore |285         |

或为属性创建固定数量的列。然而，这将大大限制灵活性。

| source          |att_1|att_val_1|attr_2  |attr_val_2|kwh_per_hour|
|-----------------|-----|---------|--------|----------|------------|
| windmill        |size |small    |location|offshore  |285         |

关于如何做到这一点，是否已经有了很好的解决方案？

Answer 1

在一个实体属性值模型中，您将有一个包含这三列的表，并且可以确保您的数据与外键引用的完整性：

CREATE TABLE powerplants (
  source text REFERENCES energy_sources(name),
  id integer,
  kwh_per_hour float,
  PRIMARY KEY (source, id)
);
CREATE TABLE powerplant_attribute_values (
  source text,
  id integer,
  attribute text,
  value text,
  PRIMARY KEY (source, id, attribute),
  FOREIGN KEY (source, id) REFERENCES powerplants,
  FOREIGN KEY (source, attribute) REFERENCES valid_attributes(source, attribute),
  FOREIGN KEY (attribute, value) REFERENCES valid_values(attribute, name)
);

( valid_attributes和valid_values表本身将有对energy_sources(name)和attributes(name)的外键引用，您可以选择使用ENUM的)

要用一个很好的字符串查询您的植物，您可以使用

SELECT format("A %s %s with %sKW",
  (SELECT string_agg(eav.value, ' ')
    FROM powerplant_attribute_values eav
    WHERE eav.source = pp.source AND eav.id = pp.id
    -- ORDER BY eav.attribute for reproducible result?
  ),
  pp.source,
  pp.kwh_per_hour)
FROM powerplants pp;

或者，如果您不太关心数据库为您执行属性验证，只需使用jsonb列：

CREATE TABLE powerplants (
  source text,
  attribute_values jsonb,
  kwh_per_hour float
);

​

SELECT format("A %s %s with %sKW",
  (SELECT string_agg(value, ' ')
    FROM jsonb_each_text(pp.attribute_values) AS kv(attribute, value)
  ),
  pp.source,
  pp.kwh_per_hour)
FROM powerplants pp;