Representing category splits in a personal finance database

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.

If I understand you correctly, you have an Account entity where one Account can link to one or more other Accounts.

The canonical solution to store such a relation in a relational database is to give each row of the Accounts table a foreign key that links back to the parent entry in the table.

In your example, this would look like this:

ID  Parent  Account name          Balance
--- ------  ------------          -------
1   NULL    Revenues              45670
2   1       Charitable Donations   1500
3   2       Homeless Shelter        800
4   3       Seattle Mission         400
5   4       Food                    200
6   4       Housing                 100

I have added an extra row to show how this scheme allows a parent to have multiple child entries. By filtering on the right columns, you can retrieve both the parent and the children of each entry.