The Financial Double-Record Anti-Pattern

I find it fascinating when encountering a software design anti-pattern, and realising that only a few years ago, I would have employed the same flawed solution. We’re all on the same learning journey; some are a bit further along the path, and they have accumulated more experience and maybe wisdom—That’s all.

Speaking of such an anti-pattern, I recently saw an interesting approach to recording financial transactions in a SQL Server database table. I don’t usually avoid posts on database schematics, but sometimes it’s worth looking at more helpful ways of storing data. 

The Double-Record Problem

OK, so what did I come across?

I’ve recreated the problem with a few lines of example data:

A bit of background: This image represents a table of financial transaction records for a bank or similar financial institution. An exchange between a buyer and a seller, will feature a flow of goods or services one way, and an amount of money the other way. When looking at a bank’s financial transaction records, we will see the money flow, not what the seller exchanged for it—which is fine. The above image highlights two rows in a database table for a single financial transaction.

Can you see the problem?

It is simply this: Here, one financial transaction consists of two data rows, one for the (bank) account which paid the money and an equal and opposite record for the account receiving the money. 

Why is this problematic?

Let’s list the reasons:

1. Duplication. The amounts are duplicated if sign-reversed. Moreover, the transaction description also repeats. We want to avoid unnecessary repetition.
2. Transaction. Since we are inserting two highly interdependent rows into the database, we had better ensure the rows are either both written to the table or neither is. Only one row succeeding while the other one fails would mean that money got created or destroyed in the system—and we certainly don’t want that!
3. Synchronisation. A problematic symptom of unnecessary data duplication. When we update the monetary transaction amount in one row, we must also update the transaction amount in its counterpart.

Why would we want to record financial transactions in two separate data rows?

It’s easy to fall into the trap of modelling a single transaction as two separate sub-parts: Money is deducted from one party and given to another. Yet that is a false dichotomy—it’s all one transaction. Money is conserved—when the amount goes down here, it goes up by the same amount somewhere else. 

The Single-Record Solution

So even though money is exchanged for goods and services, it is only a single transaction concerning two parties. And we ought to record it in our database like that too. The closer our code and data models the real world, the fewer misunderstandings and bugs we will have in our computer systems. 

One transaction, one data row:

What were two rows is now one. The disadvantages of the two-row anti-pattern have been jettisoned:

1. No more duplication: A single row for a single concept. 
2. Automatic transaction management: We no longer need to insert, update or delete two data rows—or none. With only one row, we have assured data consistency.
3. No synchronisation problems: Data is not duplicated, and therefore that are no potential synchronisation issues.

Conclusion

Financial transactions represent the monetary part of a trade—an exchange of goods and/or services for equivalent economic value. It’s easy to get misled into thinking that such a transaction ought to be modelled in our computer programs and databases in two separate parts: As a payment from the buyer and as a separate transfer of money to the seller. But this is flawed—and leads to unnecessary duplication, problematic data synchronisation and unneeded database transaction management. We’re better off modelling financial transactions, a payment from a buyer to a seller, as a single, inseparable record.