优化数据库设计

Question

我有一张桌子上有不同货币的用户存款(例如，它们不是真实的)：

|user_id|amount|datetime|currency_type|
---------------------------------------
|      1|   200|2020-etc|          eur|
|      2|   150|2020-etc|          usd|
|      1|   150|2020-etc|          usd|

要计算用户存款总额，我需要：

select currency_type, sum(amount) from deposits d where user_id = 1 group by currency_type; 

^结果：

|currency_type|sum|
-------------------
|          eur|200|
|          usd|150|

让我们想象一下，这个表中的记录数量在不断增长( currency_types可以是数百条)。计算将花费越来越多的时间。因此，我的方法是为每种资产类型使用单独的表和期末存款余额。

deposit_statements表(每个用户每月1条记录)

|deposit_statement_id| date| user_id|
-------------------------------------
|                   1| ....|       1|

语句表(复合PK deposit_statement_id + currency_type)

|deposit_statement_id| date| closing_deposit_balance|currency_type|
-------------------------------------------------------------------
|                   1| ....|                     200|          eur|
|                   1| ....|                     150|          usd|

然后从理论上取上个月deposit_statements的记录和所有期末余额的总和(分别为每种货币)和存款表中所有存款的总和，该表的日期大于deposit_statements.中的日期。如果有人能帮我写这个查询那就太好了。

是否有更好的方法来解决这个问题？

第二个可能的解决方案(分区)：

如果完全抛出deposit_statements并在语句表中添加user_id并按一个月对其进行分区，该怎么办？

Answer 1

我不建议半途而废。首先，你应该等到你的性能出现问题。您可能会感到惊喜，在大型数据库中，您仍然可以在合理的时间内将100个值相加。

然后，如果您决定确实需要当前余额，请向user_id表中添加一列。用当前余额初始化值。然后使用触发器来维护值。

将值拆分为“语句”和“更近期”的值听起来像是查询的复杂性。它还冒着这样的风险:一次历史性的调整会使事情变得不正常。

我一般不喜欢触发器。但是，如果目标是要有一个容易获得和准确的当前平衡，那么它们听起来是一个更好的解决方案。

Answer 2

我相信有更好的方法。

我会设计一个这样的系统，其中有两个表-一个表用来存储事务(将money 添加到，即将从帐户中提取出来的真实事实)，另一个表用于在相关时间间隔内存储帐户余额。我们总是可以从事务表(它是真相的主要来源)重新构建历史记录--但是这个历史表为我们提供了任何给定时间点的预先计算值。

因此，事务表可能具有以下结构

+---------------+-----------------+
| id            | Serial          |
+---------------+-----------------+
| created       | TimeStampTZ     |
+---------------+-----------------+
| is_withdrawal | Boolean         | (we can get rid of this flag if you allow negative numbers for the amount)
+---------------+-----------------+
| currency_id   | Integer         |
+---------------+-----------------+
| amount        | DoublePrecision |
+---------------+-----------------+
| account_id    | Integer         |
+---------------+-----------------+

历史表可能具有以下结构

+-------------+-----------------+
| account_id  | Integer         |
+-------------+-----------------+
| currency_id | Integer         |
+-------------+-----------------+
| balance     | DoublePrecision |
+-------------+-----------------+
| valid_from  | TimeStampTZ     |
+-------------+-----------------+
| valid_until | TimeStampTZ     |
+-------------+-----------------+

如果需要，可以在第三个表中为当前(最新)余额保持缓存值。

+-----------------+-----------------+
| account_id      | Integer         |
+-----------------+-----------------+
| currency_id     | Integer         |
+-----------------+-----------------+
| current_balance | DoublePrecision |
+-----------------+-----------------+