Schema designing for handling multiple payment gateways

database-design

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.

Best Answer

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.

Related Solutions

Tables for different sources of payments

I normally go with modified version of (2).

payment_type (payment_type_id, PK(payment_type_id) );
payments(payment_id , payment_type_id, [other_attrubutes],
  PK(payment_id), UNIQUE(payment_id,payment_type_id), FK(payment_type_id) 
);

paypal_payment(payment_id , payment_type_id, [paypal_attributes], 
 PK(payment_id), FK(payment_id , 
payment_type_id)   REFERENCES payments(payment_type_id,payment_type), 
 CHECK  (payment_type_id='paypal')
)
manual_payment(payment_id , payment_type, [manual_attributes], 
 PK(payment_id), FK(payment_id , 
payment_type_id)   REFERENCES payments(payment_type_id,payment_type), 
 CHECK  (payment_type_id='manual')
)

Check constraints on each detail table ensures that manual table details never go into paypal table, and vice versa. Unique constraint allows detail tables to have a reference to 2 columns.

(3) is also not so bad, but I don't like exclusive arcs - adding new payment type will require changes to payments, and trigger used to enforce that only one of columns is not null.

Schema for dynamic number of attributes

Option 3 sounds like your best option as it gives you all the flexibility you need, fits into a RDBMS perfectly and follows normalisation.

Option 1: requires schema changes when new alerts are added to the system.

Option 2: More joins = slower queries.

Option 4: This does give you the flexibility you need but if you were going to do it this way then maybe consider using a document store like MongoDB.