Definition
- Relational model stores data in tables (relations) where each row represents a record and each column represents an attribute with a specific data type.
- Entity is a real-world concept that maps to a table: customer, account, transaction, branch.
- Relationship connects two entities using foreign keys: a customer has many accounts, an account has many transactions.
- Normalization is the process of organizing tables to reduce redundancy and avoid update anomalies, following Normal Forms (1NF, 2NF, 3NF).
- Primary key uniquely identifies each row. Foreign key references the primary key of another table to enforce referential integrity.
Think of National Bank of India's paper ledger system. Each ledger book is a table. The customer register has one row per customer. The account ledger has one row per account with the customer's ID written in the margin (foreign key). The transaction journal records every deposit and withdrawal, each linked to an account ID. If a customer's address changes, you update it in one place (the customer register), not on every transaction slip. That is what normalization achieves.
Understanding the Concepts
What is a relational model?
A relational model organizes data into tables. Each table has a fixed set of columns (attributes) and a variable number of rows (tuples). Every column has a data type, and every row is unique, identified by a primary key.
Edgar F. Codd introduced the relational model in 1970. Today, PostgreSQL, MySQL, Oracle, and SQL Server all implement it.
-- A table is a relation. Each row is a tuple. Each column is an attribute.
CREATE TABLE customers (
customer_id BIGSERIAL PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
phone VARCHAR(15),
pan_number CHAR(10) UNIQUE NOT NULL,
date_of_birth DATE NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
customer_id | full_name | email | pan_number | date_of_birth
-------------+----------------+----------------------+------------+--------------
1 | Rajesh Kumar | rajesh@email.com | ABCPK1234A | 1990-05-15
2 | Priya Sharma | priya@email.com | DEFPS5678B | 1992-08-22
How do you identify entities and relationships in a banking domain?
Start by listing the core entities in a banking system. Each entity becomes a table.
| Entity | Table Name | Description |
|---|---|---|
| Customer | customers | A person who holds one or more accounts. |
| Account | accounts | A savings or current account belonging to a customer. |
| Transaction | transactions | A debit or credit entry on an account. |
| Branch | branches | A physical bank location where accounts are opened. |
| Loan | loans | A loan issued to a customer, linked to an account for EMI deduction. |
Relationships between entities:
- A customer has many accounts (one-to-many).
- An account has many transactions (one-to-many).
- A branch has many accounts (one-to-many).
- A customer has many loans (one-to-many).
-- Complete banking schema with relationships
CREATE TABLE branches (
branch_id SERIAL PRIMARY KEY,
branch_name VARCHAR(100) NOT NULL,
ifsc_code CHAR(11) UNIQUE NOT NULL,
city VARCHAR(50) NOT NULL
);
CREATE TABLE customers (
customer_id BIGSERIAL PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
phone VARCHAR(15),
pan_number CHAR(10) UNIQUE NOT NULL,
date_of_birth DATE NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE accounts (
account_id VARCHAR(10) PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
branch_id INT NOT NULL REFERENCES branches(branch_id),
account_type VARCHAR(10) NOT NULL CHECK (account_type IN ('SAVINGS', 'CURRENT')),
balance NUMERIC(15, 2) NOT NULL DEFAULT 0.00,
status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE',
opened_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(10) NOT NULL REFERENCES accounts(account_id),
txn_type VARCHAR(10) NOT NULL CHECK (txn_type IN ('CREDIT', 'DEBIT')),
amount NUMERIC(15, 2) NOT NULL CHECK (amount > 0),
description VARCHAR(200),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE loans (
loan_id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
account_id VARCHAR(10) NOT NULL REFERENCES accounts(account_id),
loan_type VARCHAR(20) NOT NULL,
principal NUMERIC(15, 2) NOT NULL,
interest_rate NUMERIC(5, 2) NOT NULL,
tenure_months INT NOT NULL,
status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE',
disbursed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Insert sample data
INSERT INTO branches (branch_name, ifsc_code, city)
VALUES ('Mumbai Main', 'NBIN0001234', 'Mumbai');
INSERT INTO customers (full_name, email, pan_number, date_of_birth)
VALUES ('Rajesh Kumar', 'rajesh@email.com', 'ABCPK1234A', '1990-05-15');
INSERT INTO accounts (account_id, customer_id, branch_id, account_type, balance)
VALUES ('SAV2001', 1, 1, 'SAVINGS', 75000.00);
INSERT INTO transactions (account_id, txn_type, amount, description)
VALUES ('SAV2001', 'CREDIT', 75000.00, 'Initial deposit by Rajesh');
What is an ER diagram for this banking schema?
An Entity-Relationship (ER) diagram shows entities as rectangles, relationships as lines, and cardinality with notation.
+------------+ +------------+ +--------------+
| branches | | customers | | loans |
+------------+ +------------+ +--------------+
| branch_id |PK | customer_id|PK +--| loan_id |PK
| branch_name| | full_name | | | customer_id |FK
| ifsc_code | | email | | | account_id |FK
| city | | pan_number | | | principal |
+-----+------+ +-----+------+ | | interest_rate|
| 1 | 1 | +--------------+
| |
| has many | has many
| |
| * | *
+-----+------+ +-----+------+
| accounts |-------| accounts |
+------------+ +------------+
| account_id |PK | account_id |PK
| customer_id|FK | customer_id|FK
| branch_id |FK | balance |
| balance | | status |
| status | +-----+------+
+-----+------+ |
| 1
|
| has many
|
| *
+-----+--------+
| transactions |
+--------------+
| txn_id |PK
| account_id |FK
| txn_type |
| amount |
| created_at |
+--------------+
- Cardinality:
- One branch has many accounts (1:N).
- One customer has many accounts (1:N).
- One account has many transactions (1:N).
- One customer has many loans (1:N).
What is normalization and why does it matter?
Normalization is the process of organizing a database to reduce redundancy and avoid update anomalies. An update anomaly occurs when the same fact is stored in multiple rows and you must update all of them to keep the data consistent.
Without normalization, a single flat table stores everything:
-- WRONG: Unnormalized flat table
CREATE TABLE bank_flat (
account_id VARCHAR(10),
customer_name VARCHAR(100),
customer_email VARCHAR(150),
customer_pan CHAR(10),
branch_name VARCHAR(100),
branch_city VARCHAR(50),
balance NUMERIC(15, 2),
txn_type VARCHAR(10),
txn_amount NUMERIC(15, 2),
txn_date TIMESTAMP
);
-- Problems with the flat table:
-- 1. Rajesh's name is repeated on every transaction row.
-- 2. If Rajesh changes his email, you must update 500 rows.
-- 3. If you delete all transactions, you lose the customer info.
-- 4. Branch name and city are repeated on every account row.
Normalization fixes these problems by splitting the data into separate tables.
What is First Normal Form (1NF)?
- A table is in 1NF if:
- Every column contains atomic (indivisible) values.
- There are no repeating groups or arrays.
- Each row is unique (has a primary key).
-- WRONG: Not in 1NF. Phone numbers stored as comma-separated list.
CREATE TABLE customers_bad (
customer_id BIGSERIAL PRIMARY KEY,
full_name VARCHAR(100),
phones VARCHAR(200) -- '9876543210, 9123456780'
);
-- CORRECT: 1NF. Each phone number gets its own row.
CREATE TABLE customers (
customer_id BIGSERIAL PRIMARY KEY,
full_name VARCHAR(100) NOT NULL
);
CREATE TABLE customer_phones (
phone_id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
phone_number VARCHAR(15) NOT NULL
);
-- customers
customer_id | full_name
-------------+---------------
1 | Rajesh Kumar
-- customer_phones
phone_id | customer_id | phone_number
----------+-------------+-------------
1 | 1 | 9876543210
2 | 1 | 9123456780
What is Second Normal Form (2NF)?
- A table is in 2NF if:
- It is already in 1NF.
- Every non-key column depends on the entire primary key, not just part of it.
2NF applies only when the primary key is composite (multiple columns).
-- WRONG: Not in 2NF.
-- Composite key: (account_id, branch_id)
-- branch_name depends only on branch_id, not on the full key.
CREATE TABLE accounts_bad (
account_id VARCHAR(10),
branch_id INT,
branch_name VARCHAR(100), -- depends only on branch_id
balance NUMERIC(15, 2),
PRIMARY KEY (account_id, branch_id)
);
-- CORRECT: 2NF. branch_name moved to its own table.
CREATE TABLE branches (
branch_id SERIAL PRIMARY KEY,
branch_name VARCHAR(100) NOT NULL
);
CREATE TABLE accounts (
account_id VARCHAR(10) PRIMARY KEY,
branch_id INT NOT NULL REFERENCES branches(branch_id),
balance NUMERIC(15, 2) NOT NULL
);
-- Now branch_name is stored once in the branches table.
-- Updating a branch name requires changing one row, not hundreds.
What is Third Normal Form (3NF)?
- A table is in 3NF if:
- It is already in 2NF.
- No non-key column depends on another non-key column (no transitive dependencies).
-- WRONG: Not in 3NF.
-- city depends on branch_id, and state depends on city.
-- state is a transitive dependency: customer -> branch_id -> city -> state
CREATE TABLE customers_bad (
customer_id BIGSERIAL PRIMARY KEY,
full_name VARCHAR(100),
branch_id INT,
city VARCHAR(50),
state VARCHAR(50) -- depends on city, not on customer_id
);
-- CORRECT: 3NF. City and state live in a separate locations table.
CREATE TABLE locations (
city VARCHAR(50) PRIMARY KEY,
state VARCHAR(50) NOT NULL
);
CREATE TABLE branches (
branch_id SERIAL PRIMARY KEY,
branch_name VARCHAR(100) NOT NULL,
city VARCHAR(50) NOT NULL REFERENCES locations(city)
);
CREATE TABLE customers (
customer_id BIGSERIAL PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
branch_id INT REFERENCES branches(branch_id)
);
-- If Maharashtra changes its name, you update one row in locations.
-- No customer or branch rows need to change.
When should you denormalize?
Denormalization intentionally introduces redundancy to improve read performance. You do this when:
- Reporting queries join five or more tables and are too slow.
- A read-heavy dashboard needs pre-computed aggregates.
- A caching layer is not sufficient.
-- Denormalized view for the account dashboard
-- Combines customer, account, and branch info in one row
CREATE MATERIALIZED VIEW account_dashboard AS
SELECT
a.account_id,
c.full_name AS customer_name,
c.email AS customer_email,
a.balance,
a.account_type,
b.branch_name,
b.city AS branch_city,
(
SELECT COUNT(*)
FROM transactions t
WHERE t.account_id = a.account_id
) AS total_transactions
FROM accounts a
JOIN customers c ON c.customer_id = a.customer_id
JOIN branches b ON b.branch_id = a.branch_id;
-- Refresh the materialized view periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY account_dashboard;
-- Fast query without joins
SELECT * FROM account_dashboard WHERE account_id = 'SAV2001';
account_id | customer_name | balance | branch_name | total_transactions
------------+---------------+----------+--------------+-------------------
SAV2001 | Rajesh Kumar | 75000.00 | Mumbai Main | 47
What are common mistakes in schema design?
Mistake 1: Using FLOAT for money
WRONG: FLOAT loses precision for monetary calculations.
-- WRONG: FLOAT loses precision
CREATE TABLE accounts_bad (
account_id VARCHAR(10) PRIMARY KEY,
balance FLOAT NOT NULL
);
INSERT INTO accounts_bad VALUES ('SAV2001', 0.1 + 0.2);
SELECT balance FROM accounts_bad WHERE account_id = 'SAV2001';
balance
--------------------
0.30000000000000004
CORRECT: Use NUMERIC or DECIMAL for exact precision.
-- CORRECT: NUMERIC(15,2) stores money exactly
CREATE TABLE accounts (
account_id VARCHAR(10) PRIMARY KEY,
balance NUMERIC(15, 2) NOT NULL
);
INSERT INTO accounts VALUES ('SAV2001', 0.10 + 0.20);
SELECT balance FROM accounts WHERE account_id = 'SAV2001';
balance
---------
0.30
Mistake 2: Missing foreign keys
WRONG: No foreign key allows orphan transactions.
-- WRONG: No foreign key constraint
CREATE TABLE transactions_bad (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(10), -- no FK reference
amount NUMERIC(15, 2)
);
-- This succeeds even though INVALID999 does not exist
INSERT INTO transactions_bad (account_id, amount)
VALUES ('INVALID999', 5000.00);
CORRECT: Foreign key prevents orphan records.
-- CORRECT: FK enforces referential integrity
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(10) NOT NULL
REFERENCES accounts(account_id),
amount NUMERIC(15, 2) NOT NULL
);
-- This fails because INVALID999 does not exist in accounts
INSERT INTO transactions (account_id, amount)
VALUES ('INVALID999', 5000.00);
ERROR: insert or update on table "transactions" violates
foreign key constraint "transactions_account_id_fkey"
Detail: Key (account_id)=(INVALID999) is not present in table "accounts".
What should a senior or principal engineer know?
- BCNF and beyond. Boyce-Codd Normal Form (BCNF) is stricter than 3NF. It requires that every determinant is a candidate key. In practice, 3NF is sufficient for most banking schemas, but BCNF matters when there are multiple overlapping candidate keys.
- Surrogate keys vs natural keys. Use surrogate keys (auto-generated IDs) for internal references but keep natural keys (PAN number, IFSC code) as unique constraints. This makes joins faster (integer comparison) while preserving business uniqueness.
- Partitioning large tables. The transactions table in a banking system can grow to billions of rows. Use range partitioning by created_at so that queries for recent transactions only scan the latest partition.
- Soft deletes vs hard deletes. Banking regulations often require retaining data for 7-10 years. Use a status column (ACTIVE/CLOSED/ARCHIVED) instead of DELETE. This preserves audit trails.
- Schema evolution. Use Flyway or Liquibase for versioned migration scripts. Never run ad-hoc DDL in production. Every schema change should be a reviewed, versioned migration.
-- Partitioning the transactions table by month
CREATE TABLE transactions (
txn_id BIGSERIAL,
account_id VARCHAR(10) NOT NULL,
txn_type VARCHAR(10) NOT NULL,
amount NUMERIC(15, 2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (created_at);
CREATE TABLE transactions_2025_01 PARTITION OF transactions
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE transactions_2025_02 PARTITION OF transactions
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- Query only scans the January partition
SELECT * FROM transactions
WHERE account_id = 'SAV2001'
AND created_at >= '2025-01-01'
AND created_at < '2025-02-01';
What are common interview follow-up questions?
- What is the difference between 1NF, 2NF, and 3NF?
- When would you denormalize a table on purpose?
- What is a surrogate key vs a natural key?
- How do you handle a many-to-many relationship (for example, joint accounts with multiple holders)?
- What data type should you use for monetary values in PostgreSQL?
- How do you partition a table with billions of rows?
- What is the difference between a materialized view and a regular view?
- How do you handle soft deletes for regulatory compliance?
- What is referential integrity and how do foreign keys enforce it?
Visual Overview
Banking ER Diagram
==================
+-----------+ 1 * +-----------+ 1 * +--------------+
| branches |----------| accounts |----------| transactions |
+-----------+ +-----------+ +--------------+
| branch_id |PK | account_id|PK | txn_id |PK
| branch_nm | | customer_ |FK | account_id |FK
| ifsc_code | | branch_id |FK | txn_type |
| city | | balance | | amount |
+-----------+ | status | | created_at |
+-----------+ +--------------+
|
* | 1
+-----------+
| customers |
+-----------+
|customer_id|PK
| full_name |
| email |
| pan_number|
+-----------+
|
1 | *
+-----------+
| loans |
+-----------+
| loan_id |PK
|customer_id|FK
|account_id |FK
| principal |
| rate |
+-----------+
Normalization Levels
====================
Unnormalized (flat)
|
v eliminate repeating groups, add PK
1NF: Atomic values, unique rows
|
v remove partial dependencies
2NF: Every non-key depends on the whole key
|
v remove transitive dependencies
3NF: No non-key depends on another non-key
Key Takeaway
Relational modeling is the foundation of every banking database. You identify entities (customers, accounts, transactions, branches), define their attributes (name, balance, amount), and draw relationships between them using foreign keys. Each entity becomes a table. Each relationship becomes a foreign key column that references the parent table's primary key.
Normalization ensures that every fact is stored in exactly one place. First Normal Form eliminates repeating groups and comma-separated values. Second Normal Form removes partial dependencies from composite keys. Third Normal Form removes transitive dependencies where one non-key column determines another. The result is a schema where updating a customer's email requires changing exactly one row.
Denormalization is not the opposite of good design. It is a deliberate trade-off for read performance. Materialized views, pre-computed aggregates, and flattened reporting tables are all forms of denormalization. The base schema stays normalized. The denormalized structures are derived and refreshable.
For a banking system at National Bank of India, use NUMERIC(15,2) for all monetary columns, enforce foreign keys on every relationship, partition the transactions table by date for performance, and use soft deletes to comply with data retention regulations.
Remember one sentence: normalize to store facts once, denormalize to read them fast, and always use NUMERIC for money.
---