0

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.).

Users:

User
|user_id|basic user information|

Customer
|uses basic User model|

Merchant
|merchant_id|user_id|other merchant fields|

Wallets:

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.

Transactions:

|payment_id|wallet_id|transaction_type|amount|

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.

MMTransaction
|transaction_id|from_merchant_wallet_id|to_merchant_wallet_id|amount|

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.

Josh
  • 113
  • 1
  • 10
  • Why even differentiate between "user" and "merchant"? These are just two different roles, and a user may be a customer sometimes, and a merchant others, even if they're mostly a merchant, or mostly a customer. Consider the implication - you no longer need to hold separate wallet information for each. Now the transaction is simply between two wallets. – PaulProgrammer May 25 '20 at 02:24
  • 3
    Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy May 25 '20 at 02:26
  • 1
    Think about the implication of trying to encode business logic in the database (e.g. "merchant-merchant transaction must be between two merchants". Why is this important? Is there something special about the transaction? Why couldn't the special logic be handled in the service layer? – PaulProgrammer May 25 '20 at 02:26
  • This approach is in fact not over-normalization. It's under-normalization. You have failed to notice that wallets and transactions and users (and the user classes) are the same thing regardless of what role they play in the business transaction. – PaulProgrammer May 25 '20 at 02:28
  • @PaulProgrammer The distinction is because a Merchant may have multiple users so a single MerchantWallet will not be bound to a single user while a UserWallet would definitely be bound to a single User. Also, they have different balance limits and withdrawal features but I do recognise that these may be handled on the business logic layer. – Josh May 25 '20 at 02:30
  • Also, I do see that the underlying Wallet object is the same and transactions between each other do not care about what type of wallet they are transferred to, hence the first portion of the question where I show the normalized version where the base Wallet gets separated into inherited tables that record the FK to the respective User/Merchant tables. However, the reason why I considered storing transaction records horizontally, is the perhaps irrational fear that some leg of a transaction gets mishandled somewhere resulting in a nightmare though I understand that atomic transactions do help – Josh May 25 '20 at 02:36
  • Another thing I've considered is that storing everything across multiple tables would mean that multiple joins have to be made constantly to retrieve complete transaction history as opposed to doing unions which could be delegated to the business layer which could be less of a drag on database performance. – Josh May 25 '20 at 02:43
  • Also, the biggest problem I had with making everything agnostic to the type of owner of the wallet was that I didn't know how to structure a transaction between platform-user and platform. A unified transactions table would store information betwen A and B but creating a user to represent the platform didn't seem very neat. The alternative would be to use NULL to represent platform transactions but it disregards the possibility that the NULL record could be due to an error somewhere else and I would have an incorrect transaction record in the DB. – Josh May 25 '20 at 02:44
  • Add a user-wallet table that shows which users have what kinds of rights to which wallets. Some users may have full access to multiple wallets. Some users might have read-only rights. Some users might be able to accept a transaction, but not create a transaction. Some users may be allowed to create a transaction up to a limited size etc. – PaulProgrammer May 28 '20 at 18:16
  • Not sure why a "platform" transaction is so hard. Again, don't encode business logic into the database. Use the application layer for business logic. – PaulProgrammer May 28 '20 at 18:17
  • Initial funding might be the hardest part .. so allow NULL as a value that means "initial funding". If the application makes an error during that process, it could have just as easily made an error funding from a "platform" wallet. – PaulProgrammer May 28 '20 at 18:19
  • You cannot model out logic errors. – PaulProgrammer May 28 '20 at 18:19
  • The points you raised are very valid and I will take them back to the drawing board. Thank you for taking the time to answer. – Josh May 30 '20 at 03:39

0 Answers0