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.

CodePudding user response：

Personally, I will go for the second solution (the separation of tables) for one simple reason, maintainability, so each table will be taught and will have to manage a single type of transaction (SRP: Single Responsibility Principle). The day we want to add data for the PAYPAL gateway for example, the impact will be minimal and we will have only one table to manage.

Then for the reading case, sometimes we will need to display the list of transactions regardless of their type, in this case we can use views.