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:
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:
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!
