SQL

All three should be one table, at least based on the diagram and your description. Read this https://martin.kleppmann.com/2011/03/07/accounting-for-computer-scientists.html.

Look into the concept of sub-ledgers if you're wanting some additional tables along with the general ledger.

Past that, historical balances being rolled up by various dimensions is another thing I've done. No point in continuously summarizing the GL from the beginning of time when those values should never change and people only care to see summary values for a period.

In my mind, tables being identical is indeed a code smell. Combine them and add a type column, if you're going for normalization.

The one exception, for me, would be if there's a performance issue. For example, I frequently work with data where we want to maintain history, and the easiest way to do that is to dupe each record, every time it changes. A "normalized" solution would have you do that in the same table, and just add a version ID to each record. But with a high volume of data (that you're probably going to use VERY sparingly) that may either cause reduced performance, or just be annoying to deal with (I.E. constantly having to remember to filter to just the most-recent versions).

Also, from a higher-level business perspective, look into ledger-based accounting. It's the generally-used system among professional accountants and accounting software. One transactions (or distributions) table (I.E. the ledger) contains all movement of money, in the form of dollar amounts being moved from a source account to a destination account. Accounts are categorized as Expenses, Income, Assets, or Liabilities (also sometimes Equity). So, if you pay an electric bill, that's just a transaction/distribution moving money from an Asset account (E.G. "Checking XXXX") to an expense account (E.G. "Utilities: Electricity"). Tracking expenses and income becomes simply a matter of looking at account balances, and auditing is performed by making sure all account balances, when they're tracked ot built maybe from separate systems, or separate ledgers, add up to $0.

I’d probably combine these to allow for a single load audit, and move the specific values to satellite tables based upon type. Of course, another way to do that would be the design you have but with an additional read model table for the audit, which you’d update upon insertion,

Of course, just because things look similar doesn’t mean they are the same, but I think in this case these are all balance changes on the account, and it’s ultimately all the same timeline.

Protip: See if you can generate an Export from your bank and base your tables on that instead!

A friend wanted to make a personal finance tracking application because he wanted to know where his money disappeared to, but I told him that he'd get bored from tracking all the price lists he'd have to insert, so I recommended creating an export, load that in a DB and then use Grafana to generate some graphs, and he's been quite happy with that.

Of course that's not quite about what you're asking, since this is about learning, but just sharing an idea :)

From your diagram it looks like the categories could be different for either Income or Expenses, so having them in separate tables makes sense for me. If you combine them then you'll need separate fields for the categories, which would end up being NULL for certain types.

EDIT: Having another think about it, you could have a single category table with a type, then a single transactions table. Then the transactions could have a single category field.