Running balance and Database normalization paradigm

Maybe I'm not good in googling but I'm seeking for a set of gold rules and recommendations in designing databases related to billing.

Lets say I have an SQL table with transactions

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

Based on the rule of database normalization I abandon the idea of storing and updating on every transaction pre-calculated running balance for every *billable_account_id* in the same table or elsewhere regardless of the size of the transaction table.

I'm using Postgres if that matters(though the subject is common) and I'm not an SQL ninja at all bu开发者_开发技巧t trying to be pedantic in designing.

Questions:

1. Am I right going with this approach?
2. If yes, what methods would you suggest in maintaining such table and in composing query for getting running totals?

Any references are very appreciated!

You can use analytic functions to generate a running total in most databases. In Oracle, something like

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

If you don't have a TRANSACTION_DATE, you could use ID assuming that you can guarantee that the generated IDs are monotonically increasing.

However, from a performance standpoint, you are likely going to want to bend if not break the third normal form normalization rules for OLAP/ DSS type reporting because people are going to want to report on totals pretty frequently and some accounts are likely to have large numbers of transactions. You may, for example, want to create a separate table that has an ending balance for each BILLABLE_ACCOUNT_ID for each month end and then use the analytic function to just add the current month's transactions to last month's ending balance. In Oracle, you may want to create a materialized view that will automatically maintain the running total.