What special considerations are needed when designing databases to hold financial records?

Question

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:

1. "Check registry"-type apps like GnuCash or Quicken that maintain a record of an individuals transactions for their own use.
2. 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.

Answer 1

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.

Answer 2

The considerations are mostly legal ones. If you just look on it from a safety/reliability perspective, an excel sheet may not be inherently less secure than a sheet of paper in some drawer. Access' transactional integrity may be better than that of a clerk who gets interrupted by a call.

But, in order that your customers are allowed to use it, you have to make in conform to your local laws. Some things i encountered (in germany)

• Document formats for storage matter, because there are laws that businesses must keep their paperwork for 10 Years. In 10 Years, maybe your program is not available anymore. Therefore, you have to store documents in a DIN/ISO-certified way (.pdf seems to suffice in germany)
• Audit Trails are often necessary, e.g. you may have to present different versions of the same document, with modification dates.
• Location of Storage matters, because of the 'Datenschutz' (privacy), which may be different in the country of storage. This makes cloud-based services a little tricky.
• Some documents have to be stored in a non-mutable way. how exactly this is achieved seems to depend mostly on legalistic nitpicking (a paper is immutable, a cd is mutable, a cd signed by a worker is not)

You should definitively contact an attorney, for specifics, or at least work in close partnership with a customer.

Answer 3

Reliability factors become amazingly important when you're dealing with people's money. If Twitter loses a tweet, it's not that big a deal; if you charge someone's credit card but lose their order, somebody in your organization is going to get an earful from an angry customer. So two things: 1. You don't want that to happen in the first place, but 2. it will happen eventually no matter how careful you are, so you want to put a LOT of energy into the kind of logging and tracing mechanisms that will help you go back and find the "lost" order and correct the situation.

The absolute worst thing is to have, for example, a credit-card charge, but NO record at all of what it's for, or who it belongs to, etc.

For financial stuff, you really do need to think through even the seemingly super-improbable scenarios and plan for how to handle them: "We charged the credit card, but the database server is down before we can update the corresponding record." OK, what now? Void the charge? Log the transaction to a file so a human can fix it later? Ok, what if the disk is full and you can't write to the file? Etc., etc.

Answer 4

[1] Use security tables (dont confuse with the internal D.B. users) for users and for your app. modules, forms, webpages :

User = {userID, username, pwd, email1, email2}
Modules = {moduleID, moduleTitle, moduledescription}
ModulesPerUser = {modulesperuser, userID, moduleID}

[2] Do not delete records in your app., use a status field, maybe integer, maybe boolean or bit, that indicate that record is considered "deleted". Make you app. show records that are not deleted (marked as deleted, by that field), and make some special case forms, where the app. does show the records marked as deleted.

anytable = {anytableID, anytablefield1, anytablefield2, ..., bool anytableisdeleted }

This feature is called "virtual deletion". The real deletion is frecuently called "physical deletion".

[3] Use fields in all tables that relate to access, store the (single) user that create the record, and the (last) user that changed the record, and the datetime, if possible add the module or option where each record was modified:

AnyTable = {anytableID, anytablecreateuserID, anytablecreatedatetime,
anytableupdateuserID, anytableupdatedatetime,
moduleID, anytablefield1, anytablefield2, ..., anytableisdeleted }

[4] In some cases, currency or money values may affect results, when used several records in a detail, and, added into a single value, ina header record.

Most database brands support, this days, a currency or money data field. Use it.

In some special circumstances, some people store them into a fixed float value that support several decimals digits, ("decimal") or even as string values.

These technique its a double-edged sword. If your application require that sort of presicion, search the web, for a tutorial on how to implement it, properly.

Cheers. [dont forget to give an open tuna can to the kitty, or unleast upvote]

Answer 5

You've tagged your question with security but you are mostly talking about consistency and reliability, so I'll try to answer that part of the equation:

• Use database transactions to ensure integrity of batch operations. The most basic example is a transfer between two accounts - one account is deducted the amount and the other is credited. Use transactions to ensure a partial transfer never happens (only one side gets changed).
• For precision, use the DECIMAL type instead of floats. Calculations are much slower but you shouldn't feel it since most financial calculations are very basic
• Use replication for uptime and hotcopies for backup. You should also look into LVM snapshots for recovery

Answer 6

Some of the considerations I see are that you have to account for internal controls. This means all access for actions against tables(Insert/delete/update) must be through stored procs (and no dynamic SQl) so that no tables allow write or delete rights directly on the table to anyone except a system admin. You also have to account for internal controls that do not allow someone to create a new company and then charge items to that company (a route for fraud). Actions like that will always require people in two different roles to approve. Also checks should not be cut unless one person enters data and another approves the expense.

All tables should have triggers that create audit records. You are looking to prevent fraud and if it happens to determine exactly who took the actions when.

In financial applications, you are much more concerned with a lot of back end processes that are never seen by the User Interface. Your first concern is to prevent fraud and thus many checks that no one is aware of are made in the backend.

I would not tackle a financial application of any kind without reading the GAAP (in the US, other countries have their own accounting standards) thoroughly and having a CPA as a consultant as incorrect accounting practices can lead to jail time. This is a highly technical field and someone without the requisite knowdlge has no business attempting to create software in this area.

Answer 7

Accounting is often about verification. As long as you remember this and understand the relationship between each entity, its pretty hard to get it wrong.

I'll try to list out as many checks as I can but will invariably miss some, hopefully it will be enough for you to get started on your own digging.

Total Debits == Total Credits, this is true whether you are talking about the ENTIRE set of accounts or just one transaction in isolation. If it doesn't tally, you missed at least one posting somewhere. This is how the General Ledger balances itself.

Accounts Receivables (net debits to the controlling account) == Total billed (sum total of all billable amounts) - Total received (sum total of all payments received). This is an example of how the transaction totals in actual PHYSICAL and tangible document terms should balance with the General Ledger (double entry).

Bank Balance (according to your bank statement) == Your General Ledger total for that account + whatever cheques received that are not deposited - whatever cheques issued that are not banked in. This is an example of how bank/cash accounts tally with the General Ledger.

As you can see, every transaction, sub ledger, even stock, ties in directly to the General Ledger.

If you are doing unit testing, its pretty easy to run tests that ensure these balances exist every time you insert/update transactions as long as you know what to check for.

Of course there are more balances to check/tally but you should get the gist of the work required. Essentially, everything balances against everything else, whether it be physical documents, General Ledger items, bank statements. Its supposed to be a perfect balance, or in cases where you are lazy to deal with rounding, close to perfect.

The more checks you can perform while you are developing it, the lesser the odds of you getting something wrong.

BTW, when you deal with rounding, try to go with decimals instead of floats, it will save you a lot of headaches down the road. :P