database - 在数据库中拆分值和参数是否可以

Question

我想将大量数据存储在关系数据库中(在我的例子中是 RDS 上的 postgresql)，但我不确定要使用什么结构。我参加了包括数据库规范化在内的数据库类(class)，但我仍然不确定如何构建数据库。

我的数据由气候模型的表格结果组成，按区域(流域)汇总。

中的参数[温度、降水..等] length = 11
[1960 : 2014]
中的年份[1:12]
月份区域(盆地)[1 : 16000]

选项1

我的第一个选择是为每个指标创建一个单独的表并将数据存储如下:

|  ID   | basin_id | temperature | unit | year | month | temporal_resolution |
|-------|----------|-------------|------|------|-------|---------------------|
|     1 |        1 |        42.1 | k   | 2000 |     1 | month               |
|     2 |        2 |        1.87 | k   | 2000 |     1 | month               |
|    .. |       .. |          .. | ..   |   .. |    .. | ..                  |
| 11001 |        1 |        40.3 | m3   | 2000 |     2 | month               |
| 11002 |        2 |         2.3 | m3   | 2000 |     2 | month               |

选项 2

第二个选项创建一个垂直表:

|  ID   | basin_id |  indicator  | value | unit | year | month | temporal_resolution |
|-------|----------|-------------|-------|------|------|-------|---------------------|
|     1 |        1 | temperature |  42.1 | k   | 2000 |     1 | month               |
|     2 |        2 | temperature |  1.87 | k   | 2000 |     1 | month               |
|    .. |       .. | ..          |    .. | ..   |   .. |    .. | ..                  |
| 11001 |        1 | precipitation |  40.3 | m3   | 2000 |     2 | month               |
| 11002 |        2 | precipitation |   2.3 | m3   | 2000 |     2 | month               |

我的问题是是否建议或不应该使用拆分指标名称和值。如果数据垂直存储，则总行数将是 appr。 16000*11*12*55=116,160,000 我不确定是否很容易管理。

选项3

编辑:由于指标数量有限(12 个左右)，因此不需要垂直表结构。第三种选择是将不同的指标表合并成如下内容:

|  ID   | basin_id | temperature_k | precipitation_m | …  | riverdischarge_m3 | year | month | temporal_resolution |
|-------|----------|---------------|-----------------|----|-------------------|------|-------|---------------------|
|     1 |        1 |          42.1 |            42.1 | …  |              42.1 | 2000 |     1 | month               |
|     2 |        2 |          42.1 |            42.1 |    |              42.1 | 2000 |     1 | month               |
|    .. |       .. |            .. |              .. | .. |                .. |   .. |    .. | ..                  |
| 11001 |        1 |          42.1 |            42.1 | .. |              42.1 | 2000 |     2 | month               |
| 11002 |        2 |          42.1 |            42.1 | .. |              42.1 | 2000 |     2 | month               |

这导致 row_count 为 16000 * 55 * 12 = 10,560,000

Answer 1

这似乎是关系数据库中建模继承的示例。

您有一个抽象实体 - “observation” - 具有属性 zone、year 和 month，具体实体“temperature_observation”具有“temperature”属性，以及带有“cubic metres”的“precipitation”实体。

这SO question概述了可用的选项——它们都不是特别干净。您的选项 1 是“每个子类的表”。

您的选项 2 不是常见的解决方案之一；它可供您使用，因为您的数据显然只有 2 个属性 - 数量(数字)和测量单位。这种情况并不常见。

方案三是“单表继承”。这是一种常见的设计模式，如果您的子类数量有限，通常可以使用；一旦你得到很多子类，就会变得很难理解。

接下来您需要考虑的是如何查询这些数据。是“返回给定时期/流域的所有记录”的问题吗？在这种情况下，这无关紧要 - 您的两种选择都可以。

如果你想使用数据库进行更复杂的查询——

in which month was the temperature highest and precipitation lowest?

what's the average temperature in basins where precipitation is at least x?

• 那么您可能希望提高查询的“可读性”。

在我看来，选项 1 非常明确 - 任何查看您的数据库查询的人都会一眼就明白您在问什么。您将加入定义问题域的事物 - 流域、年份、月份。

选项 2 和 3 需要自连接，随着条件变得更加复杂，这可能会变得相当复杂且难以阅读。

例如，选项 1 中的问题降水量至少为 x 的盆地的平均温度是多少？ 是:

select avg(temperature)
from temperature_facts
where basin_id in 
  (select basin_id
   from precipitation_facts
   where precipitation > ?)

在选项 2 中，这变成:

select avg(value)
from   facttable
where  indicator = 'temperature'
and    basin_id in 
  (select basin_id
   from fact_table
   where value > ?
   and indicator = 'precipitation')

在选项 3 中，它类似于

select avg(temperature)
from fact_table
where basin_id in 
  (select basin_id
  from fact_table
  where precipitation > ?)

就我个人而言，我发现选项 1 更具表现力，但这是一个偏好问题。