Suppose a payments transaction system where the transactions can be between users on the platform (Customer-Customer, Customer-Merchant, Merchant-Merchant) and users AND the platform (Customer-Platform, Merchant-Platform for top-ups/withdrawals/fees etc.).
User |user_id|basic user information| Customer |uses basic User model| Merchant |merchant_id|user_id|other merchant fields|
Wallet |wallet_id|balance| UserWallet |wallet_id|user_id| MerchantWallet |wallet_id|merchant_id|
Since transactions might not always be between two wallets, I’ve found the large transaction table could be normalized further by splitting the halves of the transaction and crediting the base wallet instead (also because I didn’t want to use NULL on one end of the wallets to indicate the transaction was User-Platform).
This would mean that I could use a single transaction table that would get multiple entries per transaction and leave the business layer to decide which types of transaction entries to create per transaction. This also means that I could do something like wallet.transactions and pull out the total incoming/outgoing transaction history and easily compare it with the balance to see if there were any accounting errors.
Unfortunately, this method seems to have lost a lot of the built in checking options possible with separate transaction tables where I can ensure, for example, that a Merchant-Merchant transaction type would be referenced to two Merchant wallets. The original method was to have multiple transaction tables for each type of transaction type.
This would mean that obtaining wallet.transaction history would be a much more difficult task as I would have to select from individual transaction tables where from_merchant_wallet_id=wallet_id or to_merchant_wallet_id=wallet_id, repeat it across all the transaction tables and merge the results then return it. While this would mean much smaller individual tables to query, it also means that the joins are much more complex and heavy.
Am I overdoing it?
Would really appreciate the help.