This is more of a question that requires feedback. I'm designing a database that handles multiple payment gateways. A payment gateway mostly requires a table for order details before making the payment(this is common for all PGs), and a table for transaction details, for storing response after making the payment.
Now to handle multiple payment gateways, I can either keep a single transaction table, stuffing it with all the fields available from all the payment gateways and a field that says which PG that row is from;
Or, I can create separate transaction tables for each of the PG with prefix like paypal_
or bank_
etc, each having the fields each of them need.
I'm just not sure which is the more optimum way of doing it. Also need to learn it for similar scenarios I might come across in future.
paypal_transaction_id
,bank_transaction_id
etc. We didn't have too many payment gateways, so it worked for us. Might not work with those who support many PGs.