The problem of developing a banking database

Question

I am trying to create a database for banking operations. I created a table for each loan, deposit, settlement account, and also to check payment methods, debit cards and cash.

My question is, how should I handle transactions between tables, knowing that transactions are possible between all tables?

For example, a client can withdraw money using a debit card, transfer money from a current account to a loan, or deposit money for verification using a check.

My first solution is to create one transaction table for all transactions and Cardinality (0 ... 1 n) so that there is only one type of payment and one account, so should I go with it or just create a transaction table for each relationship between the two tables?

Answer 1

If "I created a table for each account, loan, deposit, current account" means that you have more than four tables, then you are doing something very wrong. You must have one table for clients and one table for transactions. A transaction is money moving from one account to another account, therefore, field identifiers, transaction date, credit account, debit account, amount will be indicated in a simple transaction table. In accounting, transactions are often used that include several credit and debit accounts, therefore they cannot be supported in the simple transaction scheme described above.

If you want to present loans, then you probably need two more tables: one table contains the atomic details of all loans (date given, creditor account, total amount, nominal interest rate, etc.) and another table contains the projected payments of each loan .

There is no need for additional tables representing deposits or checking accounts: they can be represented as accounts with a type field indicating what type they are.