What is a good way to handle payment transactions to the database?
I came up like this:
Orders Table
- OrderID (primary key)**
- MemberID (FK)
- OrderTotal
- Status (Pending, Processing, Completed, cancel) //Order Status
- Paid (0, 1, 2) //0 Not Paid, 1 Paid, 2 Refund
order_payment Table
- PaymentID (primary key)
- OrderID (related to Orders table)
- DateTransaction
- TransactionStatus
If there is a row of order_payment.TransactionStatus = ‘Sucess’ then Orders.Paid become 1
If TransactionStatus = ‘Decline’ then Orders.Paid will still remain 0 as default
order_payment table are updated via payment gateway