I will be using MongoDB (which is NoSQL) and MSSQL at the same time to store data (that’s why there are Foreign Keys)
Each account will be assigned a Wallet.
A Wallet can have thousands of transactions. Each Wallet Transaction should implement double entry accounting - which means for every transaction, there will be a credit and debit document. To implement double entry accounting, there is a WalletTransactions (which stores common fields) and a WalletTransactionLines (which stores actual details i.e. which wallet is credited, debited) collection.
Every WalletTransactions document will contain an array of WalletTransactionLines . WalletTransactions will have at least 2 WalletTransactionLines documents, one for credit, one for debit, always.
Every WalletTransactions document will have a WalletTransactionReceipt object. Every WalletTransactions document may or may not have value for each field of WalletTransactionReceipt object.
An account can also Top-up to their own wallet using i.e. Apple Pay. This action is not a wallet-to-wallet transaction, but an unknown-to-wallet transaction. This means that no wallet is debited, and only the account’s wallet is credited (meaning for one WalletTransactions, there’s only one WalletTransactionLines)
Some comments:
- I don’t want to derive the wallet balance from
WalletTransactionLines
- It would be (computationally) expensive to derive the balance each time a transaction is executed
