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)