Payment System ER Model

Problem Design the data model for a payment system: produce an ER diagram with all entities, attributes, and relationships, then write SQL queries against the schema.

Functional requirements

• Model users, accounts/wallets, payment methods (cards, bank accounts), transactions (debit/credit, transfers, refunds), and merchants.
• Support querying balances, transaction history, and reconciliation-style aggregates.

Non-functional requirements

• Money correctness: amounts as exact decimals (never float), immutable transaction records (append-only ledger; corrections are new entries), auditability.

Key components

• Entities: User(id, name, kyc_status), Account(id, user_id, currency, balance), PaymentMethod(id, user_id, type, masked_details), Transaction(id, from_account, to_account, amount, currency, status, created_at), LedgerEntry(txn_id, account_id, direction, amount) for double-entry.
• Relationships: User 1—N Account, User 1—N PaymentMethod, Transaction N—2 Account (source/destination), Transaction 1—N LedgerEntry.

Deep dives / trade-offs

• Double-entry ledger vs a mutable balance column (derive balance = Σ ledger, cache it; how to keep the cache honest).
• Status modelling for async payments (pending/settled/failed/reversed) and idempotency keys for retried requests.
• Sample SQL to be ready for: per-user balance (SUM with GROUP BY), top merchants by volume last 30 days, detecting accounts whose ledger sum ≠ stored balance, monthly settlement totals with window functions.