Designing a Bank Management System

A bank schema works by making every fact live in exactly one place and then stitching facts back together with foreign keys at query time, so that an account balance, a card linkage, or a loan repayment can never disagree with itself. The hard part of a banking model is not listing entities — it is choosing where money is allowed to be stored versus derived, because a duplicated balance is the most expensive denormalization bug a bank can ship.

We model eight entities — Customer, Account, Transaction, Loan, Loan_Repayment, Branch, Employee, Card — then resolve the one many-to-many relationship (an Account can carry several Cards, a Card can draw on several Accounts) into a junction table. Below, the goal is to reach a schema that is in third normal form (3NF) and to see exactly which anomaly each step removes.

The entities and what each one owns

Each entity owns the attributes that depend on its key and nothing else. Read the table as "this key determines these facts":

On Customer.Address: the ER diagram draws Address as a composite attribute (one logical "address" made of City, State, Area). A composite attribute is just a grouping for the diagram — the relational model has no nested columns, so we flatten it into three atomic columns (City, State, Area) in the DDL. That flattening is exactly what 1NF requires: every column holds a single, indivisible value. We do not split it into a separate Address table because each customer has one address that depends fully on Customer_ID — a separate table would buy nothing.

The relationships, and the one that needs a junction table

Seven of the eight relationships are one-to-many, so they need no new table — the "many" side just carries a foreign key. One Customer to many Accounts, one Account to many Transactions, one Branch to many Employees, and so on. The FK lives on the child.

Only Account ↔ Card is many-to-many: a joint current account can have a debit card for each holder, and a single card can be configured to draw on both a checking and a savings account. You cannot put a single FK on either side without losing rows, so this relationship becomes its own table, Account_Card, with a composite key.

A traced example: removing a real anomaly

Suppose a junior engineer, wanting fewer joins, denormalizes Account by stuffing the owning customer's details onto every account row:

Asha changed her phone, the teller updated only the row she happened to open (A-777), and now the bank holds two contradictory phone numbers for one customer. Which one does the fraud-alert SMS go to? This is an update anomaly, and it exists because Cust_Phone depends on Customer_ID, not on Account_ID — a transitive dependency through a non-key column. That is precisely the 3NF violation. Trace the normalization:

1. 1NF — every column already atomic (no repeating groups, no comma-lists). The composite Address was flattened to City/State/Area for this reason. ✔
2. 2NF — Account's key is the single column Account_ID, so there are no partial dependencies on "part of" the key. ✔ (2NF only bites when the key is composite, e.g. Account_Card — check that no extra column there depends on only Account_ID or only Card_ID. It has none.)
3. 3NF — Account_ID → Customer_ID → Cust_Name, Cust_Phone is a transitive dependency. ✘ Fix: drop Cust_Name/Cust_Phone from Account, keep only the FK Customer_ID, and let those facts live once in Customer. Now Asha's phone is stored in a single row; one UPDATE, no contradiction.

The eight CREATE TABLE statements below are already the post-fix, 3NF result — Account holds only Balance, Type, dates, and two FKs.

The schema in SQL

Each entity becomes a table; each one-to-many relationship is a foreign key on the child; the one many-to-many becomes Account_Card. Branch_Phone_Number is split out because a branch can have several phone numbers — a multi-valued attribute, which would violate 1NF if crammed into one column.

CREATE TABLE Customer (
    Customer_ID           INT PRIMARY KEY,
    Full_Name             VARCHAR(100) NOT NULL,
    City                  VARCHAR(50),   -- flattened from composite Address
    State                 VARCHAR(50),
    Area                  VARCHAR(50),
    Phone_Number          VARCHAR(15),
    Email                 VARCHAR(50),
    Date_of_Birth         DATE,
    Identification_Type   VARCHAR(20),   -- e.g. Passport, ID Card
    Identification_Number VARCHAR(50)
);

CREATE TABLE Branch (
    Branch_ID    INT PRIMARY KEY,
    Branch_Name  VARCHAR(50),
    Address      VARCHAR(100)
);

-- Multi-valued attribute → its own table (1NF)
CREATE TABLE Branch_Phone_Number (
    Branch_ID    INT,
    Phone_Number VARCHAR(15),
    PRIMARY KEY (Branch_ID, Phone_Number),
    FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID)
);

CREATE TABLE Account (
    Account_ID   INT PRIMARY KEY,
    Account_Type VARCHAR(20),            -- Savings, Current
    Balance      DECIMAL(15, 2) NOT NULL DEFAULT 0,
    Date_Opened  DATE,
    Branch_ID    INT,
    Customer_ID  INT,
    FOREIGN KEY (Branch_ID)   REFERENCES Branch(Branch_ID),
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);

CREATE TABLE Transaction (
    Transaction_ID   INT PRIMARY KEY,
    Transaction_Type VARCHAR(20),        -- Deposit, Withdrawal, Transfer
    Amount           DECIMAL(15, 2) NOT NULL CHECK (Amount > 0),
    Transaction_Date TIMESTAMP NOT NULL,
    Notes            TEXT,
    Account_ID       INT,
    FOREIGN KEY (Account_ID) REFERENCES Account(Account_ID)
);

CREATE TABLE Loan (
    Loan_ID       INT PRIMARY KEY,
    Loan_Type     VARCHAR(20),           -- Home, Personal, Car
    Loan_Amount   DECIMAL(15, 2),
    Interest_Rate DECIMAL(5, 2),
    Start_Date    DATE,
    End_Date      DATE,
    Account_ID    INT,
    FOREIGN KEY (Account_ID) REFERENCES Account(Account_ID)
);

CREATE TABLE Loan_Repayment (
    Repayment_ID   INT PRIMARY KEY,
    Repayment_Date DATE,
    Amount         DECIMAL(15, 2),
    Loan_ID        INT,
    FOREIGN KEY (Loan_ID) REFERENCES Loan(Loan_ID)
);

CREATE TABLE Employee (
    Employee_ID INT PRIMARY KEY,
    Name        VARCHAR(100),
    Role        VARCHAR(50),
    Email       VARCHAR(50),
    Branch_ID   INT,
    FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID)
);

CREATE TABLE Card (
    Card_ID     INT PRIMARY KEY,
    Card_Type   VARCHAR(20),             -- Credit, Debit
    Expiry_Date DATE,
    Card_Limit  DECIMAL(15, 2),
    Customer_ID INT,
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);

-- Junction table resolving the Account ↔ Card many-to-many
CREATE TABLE Account_Card (
    Account_ID INT,
    Card_ID    INT,
    PRIMARY KEY (Account_ID, Card_ID),
    FOREIGN KEY (Account_ID) REFERENCES Account(Account_ID),
    FOREIGN KEY (Card_ID)    REFERENCES Card(Card_ID)
);

Why the naive Transaction model is wrong

The textbook Transaction above has exactly one Account_ID. That correctly records a deposit or withdrawal, but it cannot honestly represent a transfer, which is two-sided: money must leave one account and arrive in another, atomically. With one FK you are forced into one of two broken shortcuts:

• Write a single row with the source account and pray a second row gets written for the destination — if the second insert fails, money vanishes (or is created). There is no constraint tying the two halves together.
• Add a nullable Counterparty_Account_ID to Transaction — now deposits carry a meaningless NULL column, and you still have no guarantee both legs balance.

The honest fix is double-entry: keep Transaction as the event ("Transfer, ₹5,000, 14:32") and add a Transaction_Leg table with one row per affected account and a signed amount, where every transaction's legs must sum to zero. Then a transfer is one Transaction with two legs (−5,000 on A-501, +5,000 on A-777), wrapped in a SQL transaction so it commits all-or-nothing. This is how real ledgers are built, and it is why banks never store Balance as the single source of truth — balance is the running sum of legs, and the stored column (if kept at all) is a cache reconciled against the ledger.

Pitfalls

• Storing Balance as truth. A Balance column that is UPDATEd directly drifts out of sync with the transaction history the moment one write is missed or double-applied. Treat it as a derived cache reconciled against the ledger, or compute it from the legs. Reconciliation jobs exist precisely because someone trusted the column.
• The single-FK transfer. Modeling Transaction with one Account_ID silently makes transfers unrepresentable — the bug surfaces months later as a balance that won't reconcile. Use two signed legs (above).
• Putting Account_ID on Card instead of a junction table. It looks simpler and passes the demo, but the day a customer links a second account to one card, you either lose the link or duplicate the card row. The M:N must be a junction table.
• Letting customer details leak onto Account (or transaction) rows. The 3NF violation traced above. Joins feel like a cost; contradictory customer data is a far larger one, especially when KYC/AML records must be authoritative.
• Soft-deleting accounts that still own transactions. Foreign keys from Transaction/Loan to Account mean you cannot simply DELETE an account with history. Add a status column and keep the row — ledgers are append-only by regulation.
• Floats for money. Use DECIMAL, never FLOAT/DOUBLE; binary floating point cannot represent 0.10 exactly and rounding errors accumulate into reconciliation breaks.

Takeaways

• Normalize until every non-key fact depends on the key, the whole key, and nothing but the key (3NF): one fact, one place, one UPDATE to change it.
• One-to-many relationships need only a foreign key on the child; only the genuine many-to-many (Account ↔ Card) earns a junction table with a composite key.
• A composite attribute (Address) is a diagram convenience — flatten it to atomic columns for 1NF; a multi-valued attribute (branch phones) needs its own table.
• Money is special: never trust a stored balance, never model a transfer with a single account FK, and always use DECIMAL — the schema's job is to make an unbalanced ledger impossible to represent.

Synthesized from Elmasri & Navathe, Fundamentals of Database Systems (composite vs. multi-valued attributes, ER-to-relational mapping, normal forms); Silberschatz, Korth & Sudarshan, Database System Concepts (functional dependencies and 3NF); and the double-entry ledger model documented in Martin Kleppmann, Designing Data-Intensive Applications and in payment-ledger engineering write-ups (e.g. Square/Stripe and the TigerBeetle ledger design notes). Re-authored and deepened for this guide — fixed the copy-pasted "HMS"/"Hospital Management System" references, explained the Address composite-to-atomic flattening, added an explicit 1NF→2NF→3NF trace with a worked update anomaly, and added the double-entry correction to the naive single-account Transaction model.