4

I'm developing a website (with payments) that needs to support multiple payment gateways. I'm going to use omnipay package (thanks God there is a package to handle this) but now I was wondering what is the best way to store all the payment information in the database without binding it to a specific gateway.

My first idea is to have the following tables:

  • gateway(gateway_id, gateway_name,...)
  • payment(payment_id, payment_amount,...)
  • transaction(transaction_id, gateway_id, payment_id, transaction_reference, transaction_type transaction_status, transaction_request_data, transaction_response_data...). The type can be something like 'authorize', 'purchase', 'refund', 'void', etc and the status something like 'successful', 'failed', 'pending', etc.

In this way, I can have a list of gateways (Paypal, Stripe, TargetPay, WorldPay) and each payment can have multiple transactions (a payment attempt can fail at the first time and then be attempted again and work but then voided or refunded) but one transaction belongs to a single payment. Each transaction was executed using a specific gateway and this will also be stored.

I don't know if this is the best approach (maybe it's too simple?) and I would like to hear some other ideas.

Thanks!

TJ is too short
  • 827
  • 3
  • 15
  • 35

2 Answers2

1

Your initial structure is a good start. Other things that I would include are:

  • responses, which would hold the response messages sent back from the gateway in response to your purchase calls
  • callbacks. An increasing number of gateways supply POST messages on the notify url to the caller. These indicate change of status messages from the gateway such as cancelled transactions or chargebacks
  • refunds and voids, which I would store in a single table.
  • responses to the callbacks, refunds and voids, which you could store in the same table as the regular responses if you structured it correctly

Some of the data you would probably have to store as JSON blobs because the structure of messages from each gateway would be different. Some of the data you may want to encrypt, for example if it contains data that could identify a customer or perhaps a credit card

delatbabel
  • 3,601
  • 24
  • 29
  • I was thinking in storing both the request and response in the transaction table (transaction_request_data and transaction_response_data) in a standard format (JSON as you said, for example). Do you think that is better to save them on their own table? Also, the refunds and voids are related to a payment, so I was also thinking in storing them as transactions (in the transaction table). Do you think is wrong to handle all these operations as "transactions"? – TJ is too short Feb 01 '16 at 15:59
  • I would add the response in a separate table. There could be multiple responses, callbacks, etc, for a single payment request. Whether you store the request data in a separate table or not is a matter for debate. – delatbabel Feb 02 '16 at 03:47
  • can you please make an example? I'm not sure how to store the responses in that way. – TJ is too short Feb 02 '16 at 16:07
  • Probably not because it goes a bit beyond explaining the basics. How you store data also depends on what framework you're using. e.g. in Laravel you would use a model class with a $casts parameter indicating a column with cast = array, then just assign an array value to that column and it will get converted to JSON for you. How you do that in other frameworks is probably beyond the scope of this discussion -- you should check the framework documentation. In the most generic case you would do json_encode() and json_decode manually when assigning or retrieving data from the column. – delatbabel Feb 05 '16 at 08:54
0

I believe the best answer is: it depends on how close the data provided / required by the gateway provided to each other. If these are close to each other, or you can map the various status types, then store all of these in a single transactions table. If not, then you need to maintain separate tables for each payment gateways.

However, even in the latter case I would have a single transactions table that consolidates the most important and common data accross all providers, such as amount, payment status, and store only the vendor specific data in their corresponding tables.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • I totally agree with you @Shadow. I think this is a very complex issue because each gateway might have its own data and there is also another thing: there are different types of operations: 'authorize', 'purchase', 'refund', 'void', ... and each operation might have its own data as well. So I'm not sure how can I implement a solid database structure that will be able to handle all of this cases. – TJ is too short Feb 01 '16 at 15:16