Mysql – How I should design tables for transaction (payment) for banking

database-designMySQLoracle

My team and I are trying to design a new simple database system to manage transaction of customers who use their debit cards. These are some problems we haven't yet decided:

When it comes to 'payment by card' process, there should be a transaction to deal with it, there might be pending status before the process will have completed. So it is necessary to create 2 separate tables, one of which manage the transaction status from that payment , and the other only about the successful result after the transaction completes. Or is there any other way around?

We have considered a 'isPending' field (bollean or bit) should be added but since we have not decided which table should contains it so it is still a problem. But anyway, assuming that the tables have completed, should this filed only update (.e.g from true to false, 1 to 0,etc) or there should be a new records should be created?

We hope that our questions do not confuse you. Please give some recommendation to help our system more improved! Thank you! (We just hope that we do not post in the wrong section, so if that was the case, we would be very sorry)

Best Answer

i think there are a lot of different solutions in the field. There are different workflows on wirecard, Computop, Saferpay and so on. Basicly i prefer these steps.

  1. Store the initial Step (init payment) with timestamp, useragend... (Table A)
  2. Put this id to the Payment-Request to the prowider as an optional field.
  3. Often you get an response to yor success-url and to a notify-url. I fetch the full response and store it as a json string ( for debug ) (Table B)
  4. Update Table A with the encrypted data from the Response ( SUCCESS|FAIL ... )
  5. If success, update the shopping basket If you have problems, you need as much information as you can have. Specially for seamless payment.
Related Question