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.

  • It depends on your exact situation. In general, it is cheaper to select subsets of one big table than to merge a lot of little tiny tables together with UNION. But there are situations where the tiny tables design works better. Feb 27, 2012 at 13:46
  • What do you have so far?
    – Aaron
    Feb 27, 2012 at 13:53
  • @BryceAtNetwork23 For now, I'm handling two PGs and I have separate tables for both of them. But I have to add more PGs in future. So was thinking if I should continue doing this, as I have to keep adding more tables each time. Its a choice between increasing number of tables and one table with increasing number of columns and records. I'm a bit confused.
    – Bibhas
    Feb 27, 2012 at 14:34
  • @Bibhas, Is it possible to share with us the solution you used? I am having the same doubt. Jun 11, 2014 at 21:23
  • @MarcioSimao we went with single table with properties like 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.
    – Bibhas
    Jun 12, 2014 at 7:21

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

  • 1
    Thanks for clearing that up. I guess it was right in front of me, but just needed someone to point it out. :)
    – Bibhas
    Feb 28, 2012 at 18:02
  • @BryceAtNetwork23, If i have many gateways, such as 5 or more, do you think i will have difficulties to get the data? I am asking this because i think the master transaction table will have to do many left joins in each payment gateway type. Jun 12, 2014 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.