sql - 运行平衡和数据库规范化范例

Question

也许我不擅长谷歌搜索，但我正在寻找一套设计与计费相关的数据库的黄金法则和建议。

假设我有一个包含事务的 SQL 表

transactions(id int, credit float, debit float, billable_account_id int)

基于database normalization的规则我放弃了在同一个表或其他地方为每个 *billable_account_id* 存储和更新每个交易预先计算的运行余额的想法，无论交易表的大小如何。

如果这很重要，我会使用 Postgres(尽管这个主题很常见)而且我根本不是 SQL 忍者，但试图在设计中表现得迂腐。

问题:

1. 我采用这种方法是否正确？
2. 如果是，您建议采用哪些方法来维护此类表以及编写查询以获取运行总计？

非常感谢任何引用!

Answer 1

您可以使用分析函数在大多数数据库中生成运行总计。在 Oracle 中，类似

SELECT billable_account_id,
       SUM( (CASE WHEN credit IS NOT NULL THEN credit
                  WHEN debit IS NOT NULL THEN -1 * debit
                  ELSE 0 
                  END) ) OVER (PARTITION BY billable_account_id
                                   ORDER BY transaction_date ) running_total
  FROM transactions

如果您没有 TRANSACTION_DATE，则可以使用 ID，假设您可以保证生成的 ID 单调递增。

但是，从性能的角度来看，如果不破坏 OLAP/DSS 类型报告的第三范式规范化规则，您可能会想要改变，因为人们会非常频繁地报告总计，并且某些帐户可能进行大量交易。例如，您可能想要创建一个单独的表，其中每个月末的每个 BILLABLE_ACCOUNT_ID 都有一个期末余额，然后使用分析函数将当月的交易添加到上个月的期末余额中。在 Oracle 中，您可能希望创建一个物化 View 来自动维护运行总计。