I am writing the schema for a simple bank database. Here are the basic specifications:
- The database will store transactions against a user and currency.
- Every user has one balance per currency, so each balance is simply the sum of all transactions against a given user and currency.
- A balance cannot be negative.
The bank application will communicate with its database exclusively through stored procedures.
I expect this database to accept hundreds of thousands of new transactions per day, as well as balance queries on a higher order of magnitude. To serve up balances very quickly I need to pre-aggregate them. At the same time, I need to guarantee that a balance never contradicts its transaction history.
My options are:
Have a separate
balancestable and do one of the following:
Apply transactions to both the
TRANSACTIONlogic in my stored procedure layer to ensure that balances and transactions are always in sync. (Supported by Jack.)
Apply transactions to the
transactionstable and have a trigger that updates the
balancestable for me with the transaction amount.
Apply transactions to the
balancestable and have a trigger that adds a new entry in the
transactionstable for me with the transaction amount.
I have to rely on security-based approaches to make sure no changes can be made outside of the stored procedures. Otherwise, for example, some process could directly insert a transaction into the
transactionstable and under scheme
1.3the relevant balance would be out of sync.
balancesindexed view that aggregates the transactions appropriately. Balances are guaranteed by the storage engine to stay in sync with their transactions, so I don't need to rely on security-based approaches to guarantee this. On the other hand, I cannot enforce balances be non-negative anymore since views -- even indexed views -- cannot have
CHECKconstraints. (Supported by Denny.)
Have just a
transactionstable but with an additional column to store the balance effective right after that transaction executed. Thus, the latest transaction record for a user and currency also contains their current balance. (Suggested below by Andrew; variant proposed by garik.)
Have you designed or managed a database like this with a high load profile? What was your solution to this problem?
Do you think I've made the right design choice? Is there anything I should keep in mind?
For example, I know schema changes to the
transactionstable will require I rebuild the
balancesview. Even if I am archiving transactions to keep the database small (e.g. by moving them somewhere else and replacing them with summary transactions), having to rebuild the view off tens of millions of transactions with every schema update will probably mean significantly more downtime per deployment.
If the indexed view is the way to go, how can I guarantee that no balance is negative?
Let me elaborate a bit on archiving transactions and the "summary transactions" I mentioned above. First, regular archiving will be a necessity in a high-load system like this. I want to maintain consistency between balances and their transaction histories while allowing old transactions to be moved somewhere else. To do this I'll replace every batch of archived transactions with a summary of their amounts per user and currency.
So, for example, this list of transactions:
user_id currency_id amount is_summary ------------------------------------------------ 3 1 10.60 0 3 1 -55.00 0 3 1 -12.12 0
is archived away and replaced with this:
user_id currency_id amount is_summary ------------------------------------------------ 3 1 -56.52 1
In this way, a balance with archived transactions maintains a complete and consistent transaction history.