0

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!

1
  • 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 at 13:42
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.

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.