-1

I was wondering how people are securely storing money balances in a database. E.g how do you make sure that the database administrator does not modify balances or transactions? How do you make sure that the code that does a transaction not accidentally or intentionally by a rogue employee does not work correctly.

Banks, PayPal and any other apps that hold balances in USD or any other currency should have this problem

JaapH
  • 391
  • 2
  • 6
  • Unfortunately this type of question is off-topic: this is a very broad topic with lots of solutions, and those solutions could have specific dependencies on the database technologies in use. And there is no specific programming question here. It would just lead to opinions and discussion. – David Makogon Jan 02 '18 at 04:28
  • 1
    Your question is a bit unfounded, because I can counter by asking why can't a rogue teller just add money to random accounts? True, she doesn't have direct access to the database, but she does control software which writes to the database. – Tim Biegeleisen Jan 02 '18 at 04:28
  • Similiar question. I would take a look here https://stackoverflow.com/a/6220918/5319888 – ACerts Jan 02 '18 at 04:32
  • Well, you do daily / weekly / montly audits of your money. Multiple individuals need to verify if every transaction is legit. – Ricky Notaro-Garcia Jan 02 '18 at 04:38

1 Answers1

0

All the banking packages I've worked with store the balance with the account entity. Calculating it on the fly from movement history is unthinkable.

The right way is:

  • The movement table has an 'opening balance' transaction for each and every account. You'll need this in a few year's time when you need to move old movements out of the active movement table to a history table.
  • The account entity has a balance field
  • There is a trigger on the movement table which updates the account balances for the credited and debited accounts. Obviously, it has commitment control. If you can't have a trigger, then there needs to be a unique module which writes movements under commitment control
  • You have a 'safety net' program you can run offline, which re-calculates all the balances and displays (and optionally corrects) erroneous balances. This is very useful for testing.

Some systems store all movements as positive numbers, and express the credit/debit by inverting the from/to fields or with a flag. Personally, I prefer a credit field, a debit field and a signed amount, this makes reversals much easier to follow.

Notice that these methods applies both to cash and securities.

Securities transactions can be much trickier, especially for corporate actions, you will need to accommodate a single transaction that updates one or more buyer and seller cash balances, their security position balances and possibly the broker/depository.

From smirkingman answer on Database design: Calculating the Account Balance

Ricky Notaro-Garcia
  • 653
  • 6
  • 25