Schema designing for handling multiple payment gateways

Question

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.

Answer 1

It depends on how different the data is between the payment types.

For the sites I support at work, we have one table that stores data for all payment types. That works for us because our payment types are basically 4 kinds of credit cards and company purchase order. Most of our customers pay with credit cards, so there isn't a whole lot of deviation in the data. Of course, queries for those credit card customers always yield NULL values in the PONumber field. Likewise, queries for PO customers yield NULLs in all the credit card related fields.

If there are a lot of different fields in your data, you could try one master transaction table with individual tables for each payment gateway. Each payment gateway type table would have a foreign key of transaction_id, which would link back to the master transaction table.

On the other hand if your payment gateway types all have similar fields, then I would stick with one transaction table.