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?

New contributor
user190707 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

closed as primarily opinion-based by LowlyDBA, Marcello Miorelli, John Eisbrener, Akina, Marco Sep 12 at 8:11

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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...). – hot2use Sep 12 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.

  • +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 at 6:52

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