1

I'm writing a simple banking database schema. It's for learning purposes, not for real a bank. Now I'm stuck at designing financial transactions.

Transactions can be of different types:

  • Money transfer between accounts inside the bank. This type of transaction has both sender and receiver accounts.
  • Money withdrawal. This type of transaction has only an account from which money has been withdrawn.
  • Money deposit. This type of transaction has only an account to which money has been deposited.
  • Paying for services. This type of transaction has an account and service type that was paid for.

Also, all transaction types have an amount and date created. How would be better to design the database schema having many transaction types? It also should be easy to get a list of all transactions of an account. The simplest way to design it is to just create a separate table for each transaction type. Example schema:schema

But this design has a few problems.

  • There is no unique transaction id.
  • It is not easy to get a list of all transactions (the only way is to unite all transactions from all tables).
  • If a new transaction type appears, you need to update the logic of getting a list of transactions.

Another idea is to create a table transaction that has a unique id for all transactions, type of transaction, amount and date created. Schema:schema This schema solves the first problem with the uniqueness of transaction id. But there are still two other problems.

Do you know how it is designed in real bank systems? I would be glad for any advice! Thanks in advance!

Improve this question
3
  • Please read stackoverflow.com/questions/59432964/… - I'm not in the industry, but the author of that answer is and that provides the most insight into how an account/ledger system would/should work for a small/mid size entity (larger, more complex entities need to queue transactions, "pause" transactions, etc)
    – bbaird
    Oct 13, 2021 at 13:42
  • @zenno2 I like the idea on table one, basically, you have four table transactions each recording unique characteristics. in the second table, you have all transactions in one table. As tinlyk stated If you want to get fancy you could have separate transaction tables for Savings and for Loans
    – Joshua Pchumba
    Feb 24 at 17:47
  • Just for funsies, as someone who actively writes online banking software for a NASDAQ listed company, I can tell you that one table for transactions is sufficient for most needs, unless you're actually a real bank, in which case you're using a complex ledger based system, which really needs advanced theory behind the design, and has to deal with various regulations depending on your market. And fwiw, our actual table structure for just accounts + transactions is around 30 tables. :D (there's other data ya know)
    – jcolebrand ♦
    Feb 24 at 23:45

1 Answer 1

Reset to default
0

@zenno2 - you may be over thinking this if what you describe at the very top is the extent of transactions types. This is essentially a basic relationship with Account (which is primary, or core)

Share(s) - For example, Account 01 might have one savings account, one checking account and one Car loan, so Savings(1), Savings(2) and Loan(1) Transactions are everything here; deposits, payments, transfers - all in the same table. Just codes indicate what each line is for: D for deposit, W for withdrawal, T for Transfer. (A "payment" would be a withdrawal)

If you want to get fancy you could have separate transaction tables for Savings and for Loans - that would allow for more granular data in smaller design. Then just keep in mind what @bbaird posted above - if you can get your mind around that then you are halfway home.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.