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.

Areas to go deep

• Entity/relationship design across accounts, payment methods, transactions, and ledger entries.
• Ledger vs mutable balance, async payment status, and idempotency of retried requests.
• The SQL the schema must answer (balances, top merchants, reconciliation).