Database structure for storing Bank-like accounts and transactions

Question

We're in the process of adding a bank-like sub-system to our own shop.

We already have customers, so each will be given a sort of account and transactions of some kind will be possible (adding to the account or subtracting from it).

So we at least need the account entity, the transaction one and operations will then have to recalculate overall balances.

How would you structure your database to handle this?

Is there any standard bank system have to use that I could mock?

By the way, we're on mysql but will also look at some nosql solution for performance boost.

Answer 1

I don't imagine you would need NoSQL for any speed boost, since it's unlikely to need much/any parallelism and not sure how non-schema-oriented you might need to be. Except when you start getting into complex business requirements for analysis across many million customers and hundreds of millions of transactions, like profitability, and even then that's kind of a data warehousing-style problem anyway which you probably wouldn't run on your transactional schema in the first place if it had gotten that large.

In relational designs, I would tend to avoid any design which requires balance-recalculation because then you end up with balance-repair programs etc. With proper indexing and a simple enough design, you can do a simple SUM on the transactions (positive and negative) to get a balance. With good consistent sign conventions on the transactions (no ambiguity on whether to add or subtract - always add the values) and appropriate constraints (with limited number of types of transactions, you can specify with constraints that all deposits are positive and all withdrawals are negative) you can let the database ensure there are no anomalies like negative deposits.

Even if you want to cache the balance in some way, you could still rely on such a simple mechanism augmented with a trigger on the transaction table to update an account summary table.

I'm not a big fan of putting any of this in a middle layer outside of the database. Your basic accounting should be fairly simple that it can be handled within the database engine at speed so that anyone or any part of the application executing a query is going to get the same answer without any client-code logic getting involved. And so the database ensures integrity at a level slightly above referential integrity (accounts with non-zero balance might not be allowed to be closed, balances might not be allowed to go negative etc) using a combination of constraints, triggers and stored procedures, in increasing order of complexity as required. I'm not talking about all your business logic, just prohibiting low-level situations you feel the database should never get into due to bad client programming or a failure to do things in the right order or call things with the right parameters.

In real banking (i.e. COBOL apps) typically the database schema (usually non-relational and non-normalized - a lot of these things predate SQL) you see a lot of things like 12 monthly buckets of past balances which are updated and shifted when the account rolls over. Some of the databases these systems use are kind of hierarchical. And this is where the code is really important, because everything gets done in code. Again, it's kind of old-fashioned and subject to all kinds of problems (i.e. probably a lot like what NatWest is going through) and NoSQL is a trend back towards this code-is-king way of looking at things. I just tend to think after a long time working with these things - I don't like having systems with balances cached and I don't like systems where you really don't have point-in-time accountability - i.e. you ignore transactions after a certain date and you can see EXACTLY what things looked like on a certain date/time.

I'm sure someone has "standard" patterns of bank-like database design, but I'm not aware of them despite having built several accounting-like systems over the years - accounts and transactions are just not that complex and once you get beyond that concept, everything gets highly customized.

For instance, in some cases, you might recognize earnings on contracts on some kind of schedule according to GAAP and contracts which are paid over time. In banking you have a lot of interest-related things with different interest rates for cost of funds etc. Everything just gets unique once you start mixing the business needs in with just the basics of the accounting of ins and outs of money.

Answer 2

You don't say whether or not you have a middle tier in your app, between the UI and the database. If you do, you have a choice as to where you'll mark transactions and recalculate balances. If this database is wholly owned by the one application, you can move the calculations to the middle tier and just use the database for persistence.

Spring is a framework that has a nice annotation-based way to declare transactions. It's based on POJOs; an alternative to EJBs. It's a three legged stool of dependency injection, aspect-oriented programming, and great libraries. Perhaps it can help you with both structuring and implementing your app.

If you do have a middle tier, and it's written in an object-oriented language, I'd recommend having a look at Martin Fowler's "Analysis Patterns". It's been around for a long time, but the chapter on financial systems is as good today as it was when it was first written.