I hope this question isn't too broad. In the future I may need to add some accounting and financial-tracking systems to some applications (mostly web-based applications, but my questions pertains to desktop apps as well).
Now, creating a simple record of financial transactions is theoretically easy. One database table with a few columns could do the job. Even MS Access, Excel, or even just a plain ASCII text file could be used to store transaction dates, account IDs, and dollar amounts. However, I feel that even a frequently backed-up SQL table with transactional integrity might not be robust enough for serious financial tracking.
I hear terms like "double-entry accounting", and I get the feeling that most financial tracking apps (for example, Mint.com, or GnuCash) have a much more complicated data structure or process in place to make double-sure that everything adds up perfectly, exactly as it should, and that no data is ever lost or corrupted.
My question is: When designing an app to track financial transactions, what special design considerations should be made? It seems like there could be so many potential issues… issues with rounding precision, parity checks, some kind of audit process, special backups, security/encryption, extra ways to protect data in the case of a crash mid data-entry…. I don't really know what I should be asking specifically, but I get the feeling that the programming industry has a set of best practices I know nothing about. What are they?
Edit:
It looks like I opened a bigger can of worms than I expected. To clarify, I'm thinking specifically of two types of apps:
- "Check registry"-type apps like GnuCash or Quicken that maintain a record of an individuals transactions for their own use.
- Apps that track invoicing/credit/or "points" for vendors and customers that deal with a company.
I probably will not be doing any direct banking or (AFAIK) anything that has a ton of finance-related government regulations attached to it.
Best Answer
You will get many answers to this I am sure, many idealist answers too, I can only answer from my experience with financials and what actually goes on.
You have already covered the majority of issues.
Rounding precision tends to not actually be much of an issue in my experience. The majority of large financial organisations that have not grown up overnight (i.e. everything except hedge funds) have a huge range of legacy applications that are split up due to various fuels. They tend to not do rounding precision consistently; generally a certain error profit and loss is simply accepted for rounding. Indeed many man hours are spent in places I've worked where humans where the ultimate 'yes that is close enough' selectors when it comes to matching exact/expected sums. Remember, this is an answer based on reality, not what should happen.
Encryption - don't rely on it frankly. Store indentifying data in a physically and logically separate system than de-identified data (i.e. account code everywhere, personal data separate).
Generally while backups are required, offline backups are rarely called on - things have gone badly wrong at that point. Warm production copies are generally required - however this will depend on your own specific needs. In general practise we have a warm onsite production copy of all systems AND a disaster recovery site with its own production and warm copies. Warm copies tend to be a few minutes behind in replication etc.
Auditing is the key to every financial system I’ve ever worked on. You have 2 fundamental requirements A) Can you track every single change made to the data, by whom, when and why? B) Can you prove the historical state of your data? That it hasn’t been tampered with?
A) is required for operations teams – your system will be used in 100 ways you never expected, and this information is vital for expansion, ad-hoc reporting, legal reasons and debugging.
B) See the AMEX vs. Vee Vinhnee case – where AMEX were unable to collect 40k owed to them as they could not prove that their records were not made up. The solution generally used for this is trusted time stamping. Large financials have guarantor banks that guarantee transactions and thus inherently provide trusted time stamping. There are commercial providers for this for other walks of lives/scenarios.