Sample Header Ad - 728x90

What are the best practices of a banking relational database?

-1 votes
1 answer
57 views
**I don't have any particular problem in mind, I am just looking for the best practices.** I am creating a bank database. Nothing too important or involving real money, nevertheless, I think it would be cool to have 'banking-grade security', besides, it is a learning opportunity to do it, as if it was real money. I was wondering what are the best practices used by the banking industry, in order to have a fool-proof reliable performant and concurrent system for writing financial transactions in a database. Let's consider the simplest case: Users have one single account, and they can do transactions with other users. **If anyone has an article, youtube video, blog, etc,** detailing best practices for databases in the banking industry: isolation levels, how to handle concurrent reads/writes, the type of IDs (snowflakes, sequential, etc), how to design the tables, how to handle replication, how to handle backups, how to make it performant, how to scale it, etc. Here is how I was doing it, with two tables in a postgres SQL database: An user table, and a ledger table. These items was how I was imagining it being secure: 1. Locking the database with password. For example, postgres would mean creating an user with a password, where the database belongs to. 2. All monetary values being an integer, where it is possible to display the least denomination. For example, if USD, then, USD 0.50 would be Integer(50). 3. Concurrent connections. Allow multiple connections into the database for performance. 4. ACID transactions, with serializable isolation level, on all reads/writes of the ledger table, to prevent phantom reads of user balance, etc. (I don't know if it hurts performance). 5. Withdraws or deposits can be thought of money going to or coming from a special user (say, user id 0) in the ledger table. So, if user 123 makes a deposit, money will flow from 0 to 123. If user 777 makes a withdraw, money will flow from 777 to 0. User 0 can be thought of the bank itself, and tracks all of the bank's debts. **That is what I thought. However, I don't know if I missed any security practice, or if there exists a better way of doing things.**
User
----------
user_id,
username,
email,
Ledger
----------
id,                # Unique ID for the transaction.
from_user_id,      # User where the money is coming from.
to_user_id,        # User where the money is going to.
from_balance_id,   # Balance of from_user after transaction.
to_balance_id,     # Balance of to_user after transaction.
amount,            # The amount to be sent, an integer.
date,              # Date and time of when it happened.
description,       # Description.
That's the postgres schema I am using:
CREATE TABLE ledger (
    id bigint primary key,
    from_user_id bigint not null,
    to_user_id bigint not null,
    from_balance bigint not null,
    to_balance bigint not null,
    value bigint not null,
    transfer_date int not null,
    description varchar(256),
    FOREIGN KEY (from_client_id) REFERENCES "user"(id),
    FOREIGN KEY (to_client_id) REFERENCES "user"(id)
);
Asked by phy0072 (1 rep)
May 9, 2025, 05:05 PM
Last activity: May 10, 2025, 08:38 AM