Building a Real-World Wallet
Mobile money platforms like M-Pesa, PhonePe, and Google Pay need to track accounts, balances, and transactions securely. In this capstone, you'll design the database schema for QuickCart Wallet, a mobile money service used in India and Kenya.
By the end, you'll understand:
- Why certain tables and constraints matter
- How to design for data integrity (CHECK constraints, NOT NULL)
- How to write reporting queries that answer real business questions
- How to handle complex aggregations (money in vs money out)
Schema Design โ Why These Tables?
Two core tables: wallet_accounts and wallet_transactions
A mobile wallet needs to track who has money (accounts) and where it moved (transactions). Let's build them step by step.
wallet_accounts
Stores customer wallet information:
- account_id โ Primary key, auto-increment
- customer_id โ Links to customer record (NOT NULL)
- phone โ Unique phone number (UNIQUE, NOT NULL)
- balance โ Current balance with CHECK (>=0) to prevent negative
- currency โ INR or KES (CHECKed list of valid values)
- status โ active, frozen, or closed (CHECKed list)
- created_at โ Timestamp for auditing
wallet_transactions
Stores every money movement:
- txn_id โ Primary key, auto-increment
- from_account โ Who sent money (FK to wallet_accounts)
- to_account โ Who received money (FK to wallet_accounts)
- amount โ Money moved, always > 0
- txn_type โ deposit, withdrawal, transfer, or payment (CHECKed)
- description โ User-provided note
- status โ success, failed, pending, or reversed (CHECKed)
- created_at โ When the transaction occurred
โข Constraints over application logic: CHECK (balance >= 0) prevents negative balances at the database level, not just in code.
โข Foreign keys: from_account and to_account reference wallet_accounts, ensuring no orphaned transactions.
โข Status column: Transactions can fail or be reversed. The status column tracks this without deleting the record (audit trail).
Schema Creation
Here's the complete SQL to create both tables with all constraints:
Create wallet_accounts
Create wallet_transactions
Run both queries above in psql to set up your tables. After you create them, move to the next section to seed data.
Seed Data โ 15 Accounts, 30 Transactions
Let's insert realistic wallet data. We'll create 15 accounts from India and Kenya with varying balances, then add 30 transactions.
Q1: Insert 15 Wallet Accounts
Q2: Insert 30 Wallet Transactions
Run both inserts. After this, your wallet has 15 accounts with 30 real-world transactions distributed across currencies and statuses.
Write Reports โ 5 Queries
Now that you have data, write queries to answer business questions. These are the types of reports wallet managers run daily.
Q3: Total Balance by Currency
Question: How much money is in wallets by currency?
โโโโโโโโโโโผโโโโโโโโโโโผโโโโโโโโโโโโโ
KES | 7 | 234718.59
INR | 8 | 178004.78
Key insight: Majority of funds are in KES. Currency mix helps with cross-border flows.
Q4: Transaction Volume by Type
Question: What types of transactions are most common, and what's the total value?
โโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโ
deposit | 8 | 23606.27
payment | 11 | 21042.27
transfer | 5 | 14464.60
withdrawal | 6 | 10376.06
Key insight: Payments are the most frequent transaction type. Deposits are the highest value (salary inflows).
Q5: Top 5 Accounts by Balance
Question: Which customers have the most money in their wallets?
โโโโโโโโโโโโโโโโโผโโโโโโโโโโโผโโโโโโโโโ
+254722467350 | 48723.29 | KES
+254728611881 | 43922.79 | KES
+91-3210987654 | 31456.78 | INR
+91-5432109876 | 31456.78 | INR
+254788112233 | 31456.78 | KES
Key insight: Top users are in Kenya with KES accounts. This could inform VIP customer programs.
Q6: Failed Transactions
Question: What transactions have failed, and why? (for support/debugging)
โโโโโโโโผโโโโโโโโโโผโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโ
6 | 2000.00 | transfer | Sent to friend
8 | 1800.00 | payment | Mobile recharge
Key insight: 2 failed transactions out of 30 = 6.7% failure rate. Investigate these in your payment gateway logs.
Challenge โ Money In vs Money Out
Question: For each account, how much money came in (received) and how much went out (sent)?
This is a complex query because you need to:
- Sum transactions where the account is the to_account (money in)
- Sum transactions where the account is the from_account (money out)
- Handle accounts with no transactions (use LEFT JOIN and COALESCE)
Expected output sample:
โโโโโโโโโโโโโโโโโผโโโโโโโโโโโผโโโโโโโโโโ
+254722467350 | 7288.36 | 588.72
+91-9876543210 | 3750.00 | 8984.56
+254728611881 | 5000.00 | 6345.67
...
What this query teaches:
- CASE statements: Conditional aggregation (sum only when condition is true)
- COALESCE: Handle NULL sums from accounts with no transactions
- LEFT JOIN: Include accounts even if they have no transactions
- OR in JOIN: Match accounts as either sender or receiver
Test Your Knowledge
You've designed your first database.
Join the Best AI Program: The AI-Era Data Engineer โ 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer โ