9

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.

|improve this question
  • 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. – Walter Mitty Feb 27 '12 at 13:46
  • What do you have so far? – Aaron Feb 27 '12 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 '12 at 14:34
  • @Bibhas, Is it possible to share with us the solution you used? I am having the same doubt. – Marcio Mazzucato Jun 11 '14 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 '14 at 7:21
6

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.

|improve this answer
  • 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 '12 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. – Marcio Mazzucato Jun 12 '14 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.