Definition
- One-to-one – Each row on each side relates to at most one row on the other, modeled by a shared primary key or a unique foreign key. Used to split sensitive or rarely-used columns.
- One-to-many / Many-to-one – One parent relates to many children, modeled by a foreign key on the child side. The two names describe the same link from different perspectives.
- Many-to-many – Rows on both sides relate to many rows on the other, modeled with a junction table holding two foreign keys and often a composite primary key.
- Junction table – Also called a bridge or associative table, it holds the relationship and any attributes of that relationship, such as a date or status.
At the National Bank of India, each account has exactly one KYC record and each KYC record belongs to exactly one account: that is one-to-one. One account has many transactions, and each transaction belongs to one account: that is one-to-many from the account side and many-to-one from the transaction side. A customer can hold many products (savings, credit card, loan) and each product type is held by many customers: that is many-to-many, recorded in a separate holdings table pairing customer with product.
Understanding the Concepts
What is a one-to-one relationship?
A one-to-one relationship means a row on each side maps to at most one row on the other. You model it by putting a foreign key on one table that references the other and making that foreign key unique. Often the child shares the same primary key value as the parent.
-- At the National Bank of India, each account has exactly one KYC record
CREATE TABLE accounts (
account_id VARCHAR(20) PRIMARY KEY,
holder_name VARCHAR(100) NOT NULL,
balance NUMERIC(15,2) NOT NULL DEFAULT 0.00
);
CREATE TABLE kyc_details (
account_id VARCHAR(20) PRIMARY KEY, -- Shared PK = one-to-one
aadhaar_number VARCHAR(12) NOT NULL,
pan_number VARCHAR(10) NOT NULL,
verified_at TIMESTAMP NOT NULL,
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
package com.bank.model;
import javax.persistence.*;
@Entity
@Table(name = "accounts")
public class Account {
@Id
private String accountId;
private String holderName;
private java.math.BigDecimal balance;
@OneToOne(mappedBy = "account", cascade = CascadeType.ALL)
private KycDetails kycDetails;
}
@Entity
@Table(name = "kyc_details")
public class KycDetails {
@Id
private String accountId;
private String aadhaarNumber;
private String panNumber;
@OneToOne
@MapsId // Shares the same PK as Account
@JoinColumn(name = "account_id")
private Account account;
}
One-to-one is used to split a table for good reasons: separating rarely-used or large columns, isolating sensitive data like KYC details under tighter access control, or extending a table without altering it.
What is a one-to-many relationship?
A one-to-many relationship means one parent row relates to many child rows. You model it by putting a foreign key on the child table referencing the parent's primary key, with no uniqueness constraint so many children can share the same parent.
-- One account has many transactions
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(20) NOT NULL,
amount NUMERIC(15,2) NOT NULL,
txn_type VARCHAR(10) NOT NULL, -- DEBIT or CREDIT
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
CREATE INDEX idx_txn_account ON transactions(account_id);
-- One account → many transactions
SELECT a.holder_name, t.amount, t.txn_type
FROM accounts a
JOIN transactions t ON a.account_id = t.account_id
WHERE a.account_id = 'SAV2001';
package com.bank.model;
import javax.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
@Entity
@Table(name = "accounts")
public class AccountWithTransactions {
@Id
private String accountId;
private String holderName;
private BigDecimal balance;
@OneToMany(mappedBy = "account", fetch = FetchType.LAZY)
private List<Transaction> transactions;
}
@Entity
@Table(name = "transactions")
public class Transaction {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long txnId;
private BigDecimal amount;
private String txnType;
private LocalDateTime createdAt;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "account_id")
private AccountWithTransactions account; // Many transactions → one account
}
This is the most common relationship in any schema: one account to many transactions, one customer to many accounts, one order to many line items.
How is many-to-one different from one-to-many?
Many-to-one is not a different mechanism. It is the same relationship seen from the child's perspective. From the account you look one-to-many at its transactions; from a transaction you look many-to-one at its account. The foreign key lives on the "many" side in both descriptions.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class RelationshipViewService {
private final DataSource dataSource;
public RelationshipViewService(DataSource dataSource) {
this.dataSource = dataSource;
}
// One-to-many view: "Show me all transactions for this account"
public void showAccountTransactions(String accountId) throws SQLException {
String sql = "SELECT t.txn_id, t.amount, t.txn_type "
+ "FROM transactions t WHERE t.account_id = ?";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println("Txn " + rs.getLong("txn_id")
+ ": " + rs.getBigDecimal("amount"));
}
}
}
}
// Many-to-one view: "Show me which account this transaction belongs to"
public String getAccountForTransaction(long txnId) throws SQLException {
String sql = "SELECT t.account_id FROM transactions t WHERE t.txn_id = ?";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, txnId);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getString("account_id") : null;
}
}
}
}
What is a many-to-many relationship?
A many-to-many relationship means rows on both sides can relate to many rows on the other. You cannot model this with a single foreign key because neither side can hold a list of references. Instead you introduce a junction table with one foreign key to each side.
-- Customers can hold many products, products are held by many customers
CREATE TABLE customers (
customer_id VARCHAR(20) PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE products (
product_id VARCHAR(20) PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(50) NOT NULL -- SAVINGS, CREDIT_CARD, LOAN
);
-- Junction table: the many-to-many link
CREATE TABLE customer_products (
customer_id VARCHAR(20) NOT NULL,
product_id VARCHAR(20) NOT NULL,
acquired_date DATE NOT NULL,
credit_limit NUMERIC(15,2), -- Relationship attribute
PRIMARY KEY (customer_id, product_id), -- Composite PK
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
CREATE INDEX idx_cp_product ON customer_products(product_id);
-- Find all products for Rajesh
SELECT p.product_name, cp.acquired_date, cp.credit_limit
FROM customer_products cp
JOIN products p ON cp.product_id = p.product_id
WHERE cp.customer_id = 'C001';
-- Find all customers who hold a savings account
SELECT c.name, cp.acquired_date
FROM customer_products cp
JOIN customers c ON cp.customer_id = c.customer_id
WHERE cp.product_id = 'SAVINGS_001';
package com.bank.model;
import javax.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDate;
@Entity
@Table(name = "customer_products")
public class CustomerProduct {
@EmbeddedId
private CustomerProductId id;
@ManyToOne
@MapsId("customerId")
@JoinColumn(name = "customer_id")
private Customer customer;
@ManyToOne
@MapsId("productId")
@JoinColumn(name = "product_id")
private Product product;
private LocalDate acquiredDate;
private BigDecimal creditLimit; // Relationship attribute
}
@Embeddable
public class CustomerProductId implements java.io.Serializable {
private String customerId;
private String productId;
}
The junction table is the natural place to store attributes of the relationship itself, such as when Priya acquired the product or her credit limit.
What are the rules for indexing relationships?
Foreign keys on the many side and in junction tables should be indexed for join performance. Without an index, a join does a sequential scan on the child table.
-- Transactions: FK on the many side must be indexed
CREATE INDEX idx_txn_account ON transactions(account_id);
-- Junction table: both FKs should be indexed
-- The composite PK already indexes (customer_id, product_id)
-- But queries by product_id alone need a separate index
CREATE INDEX idx_cp_product ON customer_products(product_id);
package com.bank.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class JoinPerformanceService {
private final DataSource dataSource;
public JoinPerformanceService(DataSource dataSource) {
this.dataSource = dataSource;
}
// This join is fast because account_id is indexed on transactions
public void getAccountStatement(String accountId) throws SQLException {
String sql = "SELECT t.txn_id, t.amount, t.created_at "
+ "FROM accounts a "
+ "JOIN transactions t ON a.account_id = t.account_id "
+ "WHERE a.account_id = ? "
+ "ORDER BY t.created_at DESC";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getLong("txn_id") + ": "
+ rs.getBigDecimal("amount"));
}
}
}
}
}
What about cascade and delete behavior?
You must decide the cascade and delete behavior deliberately. Should deleting a customer delete their holdings? Should deleting an account delete its KYC record?
-- CASCADE: deleting an account also deletes its KYC
CREATE TABLE kyc_details_with_cascade (
account_id VARCHAR(20) PRIMARY KEY,
aadhaar_number VARCHAR(12) NOT NULL,
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
);
-- RESTRICT: prevent deleting a customer if they have products
CREATE TABLE customer_products_restrict (
customer_id VARCHAR(20) NOT NULL,
product_id VARCHAR(20) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT
);
-- SET NULL: if a manager leaves, set managed_by to NULL
CREATE TABLE account_assignments (
account_id VARCHAR(20) PRIMARY KEY,
managed_by VARCHAR(20),
FOREIGN KEY (managed_by) REFERENCES employees(employee_id) ON DELETE SET NULL
);
package com.bank.model;
import javax.persistence.*;
@Entity
public class AccountWithCascade {
@Id
private String accountId;
// CASCADE: JPA deletes KYC when account is deleted
@OneToOne(mappedBy = "account", cascade = CascadeType.ALL, orphanRemoval = true)
private KycDetails kycDetails;
// LAZY: transactions are NOT loaded until accessed
@OneToMany(mappedBy = "account", fetch = FetchType.LAZY)
private java.util.List<Transaction> transactions;
}
What are common mistakes with relationships?
The most common mistake is missing the foreign key constraint entirely, which allows orphan rows. Another mistake is using a join table for a one-to-many relationship, adding unnecessary complexity.
-- WRONG: no foreign key constraint, allows orphan transactions
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(20) -- no FK constraint!
);
-- CORRECT: foreign key enforces referential integrity
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(20) NOT NULL
REFERENCES accounts(account_id)
);
What should a senior engineer know?
A senior engineer should prefer explicit junction entities over @ManyToMany when the junction table carries its own attributes. They should also understand the N+1 query problem with lazy-loaded relationships and solve it with JOIN FETCH or entity graphs.
package com.bank.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.bank.model.AccountWithTransactions;
public interface AccountRepository extends JpaRepository<AccountWithTransactions, String> {
// N+1 problem: loading 100 accounts and then lazy-loading
// transactions for each one generates 101 queries
// Fix: JOIN FETCH loads account + transactions in one query
@Query("SELECT a FROM AccountWithTransactions a "
+ "JOIN FETCH a.transactions WHERE a.accountId = :id")
AccountWithTransactions findWithTransactions(String id);
}
Relationship summary: One-to-One: Account ←→ KYC (shared PK or unique FK) One-to-Many: Account → Transactions (FK on child) Many-to-One: Transaction → Account (same FK, child view) Many-to-Many: Customer ←→ Product (junction table)
Visual Overview
┌────────────────────────────────────────────────────────────┐ │ Database Relationship Types │ ├────────────────────────────────────────────────────────────┤ │ │ │ One-to-One (Account ↔ KYC): │ │ ┌──────────┐ ┌──────────────┐ │ │ │ accounts │ │ kyc_details │ │ │ │ SAV2001 │────→│ SAV2001 (PK) │ Shared primary key │ │ │ SAV2002 │────→│ SAV2002 (PK) │ │ │ └──────────┘ └──────────────┘ │ │ │ │ One-to-Many (Account → Transactions): │ │ ┌──────────┐ ┌──────────────┐ │ │ │ accounts │ │ transactions │ │ │ │ SAV2001 │←───┐│ txn_1 → SAV2001 │ │ │ │ │←───┤│ txn_2 → SAV2001 │ FK on child │ │ │ │←───┘│ txn_3 → SAV2001 │ │ │ └──────────┘ └──────────────┘ │ │ │ │ Many-to-Many (Customer ↔ Product): │ │ ┌───────────┐ ┌──────────────────┐ ┌──────────┐ │ │ │ customers │ │ customer_products│ │ products │ │ │ │ C001 │←→│ C001, SAVINGS │←→│ SAVINGS │ │ │ │ C002 │←→│ C001, CARD │←→│ CARD │ │ │ │ │ │ C002, SAVINGS │ │ LOAN │ │ │ └───────────┘ └──────────────────┘ └──────────┘ │ │ Junction table │ └────────────────────────────────────────────────────────────┘
Key Takeaway
Relationship cardinality describes how many rows on one side can associate with rows on the other, and there are three underlying shapes. A one-to-one relationship means each row on each side maps to at most one row on the other. You model it by putting a foreign key on one table that references the other and making it unique, often by letting the child share the parent's primary key. At the National Bank of India, accounts and KYC details are one-to-one with a shared primary key, keeping sensitive KYC data in a separate table with tighter access control.
A one-to-many relationship means one parent relates to many children. You model it by putting a foreign key on the child side referencing the parent's primary key, with no uniqueness so many children share one parent. Many-to-one is not a different mechanism; it is the same relationship viewed from the child. From an account you look one-to-many at its transactions, and from a transaction you look many-to-one at its account. The foreign key lives on the many side either way. This is the most common relationship in any schema.
A many-to-many relationship means both sides can relate to many rows on the other. A single foreign key cannot express it, so you introduce a junction table with one foreign key to each side and a composite primary key. The junction table is also where you store relationship attributes like the date Priya acquired a product or her credit limit on a card. In JPA, a senior engineer should prefer an explicit junction entity over @ManyToMany when the junction carries attributes.
The key implementation details are indexing foreign keys on the many side and in junction tables for join performance, choosing cascade behavior deliberately, and solving the N+1 query problem with JOIN FETCH.
Remember one sentence: one-to-one is a unique foreign key, one-to-many and many-to-one are the two views of a foreign key on the child, and many-to-many needs a junction table with two foreign keys.
---