0

My question is about code and database design. How should I design my code to calculate simple variables (stored in sqlite db) like the balance of a wallet?

Example: We have tables for customers, wallets, transactions. We have methods to fill these tables. When receiving money from a customer, we add the amount to the balance of a wallet and submit a transaction (date, amount, ...) into the transactions table.

I have 2 approaches to design my code and database - which one is more appropriate and bugfree?

A) When doing a transaction I add / subtract the amount to my the balance of a wallet and submit a transaction into a table transaction with information like wallet_id, amount, date...

B) I just submit the transaction to the transactions table and recalculate the balance of a specific wallet by checking for columns with the matching wallet_id and add up the wallet_balance from there.

Which is the right way? For me A) looks more prone to bugs and safe code requires more effort. I would go with B) which would be much easier then I would add more features like deleting transactions (+ recalculating balances) and so on.

What is your advice?

  • Welcome to DBA.SE and thanks for your participation. Your question contains bold and non-bold names for tables and (possibly) columns. It would help if you could format your question and provide full DDL statements for the hypothetical tables. Even a quick listing of the tables with possible columns would help interpret your question (including reference ID columns...). – John aka hot2use Sep 12 '19 at 8:15
3
BEGIN;
INSERT INTO transactions ... (info about the transfer of funds)...;
UPDATE accounts SET balance = balance - $amt WHERE acct_id = $me;
UPDATE accounts SET balance = balance + $amt WHERE acct_id = $you;
COMMIT;

That guarantees that either all 3 actions happened, or none of them, even in the case of a power failure.

Yes, this violates the don't-store-redundant-data rule, but it is standard practice, and much more efficient.

Another accounting note: Do not ever DELETE or UPDATE transactions, only INSERT more rows to fix problems -- this leaves an audit trail of what happened and when.

| improve this answer | |
  • +1 But any case the procedure which verifies/recalculates (and reports about an incident if wrong value detected) the balance(s) must exist. – Akina Sep 12 '19 at 6:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.