CODEWITHSUNDEEP
mysqldatabase-designpayment
user622378
user622378

Reputation: 2336

payment transactions and order table?

What is the best way to handle payment transactions to the database?

Here is what I came up with:

Orders Table

Payments Table

For example, if there is two payment transactions from OrderID-123 in the Payments table.

One is Decline and other one is Sucesss

If there is a row of Sucesss, then Orders.Paid will become 1

Or what is better solution?

Upvotes: 3

Views: 5787

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

Decline and success are not enough in my experience. Among colorful use-cases, you may experience successful payments pending clearing of funds (e.g. e-checks), refunds (made by you), reversals (made by the customer/api provider), partial refunds/reversals (e.g. the dog's toy but not his food, within the same order), reversed refunds/reversals.

Not to mention the variety of other cases inherent to subscriptions, e.g. subscription upgrades and downgrades, subscription changes, cancellations, cancelled cancellations, locked, and what not.

Needless to say, issues become even thornier if you ever need to deal with affiliate payments, user-less invoices, different billing/shipping contacts, resellers, etc.

The precise answer depends on your specific requirements, obviously, but I've found that you're almost always better off starting with a model that satisfies the rigors of T-ledger accounting (i.e. debit/credit with a chart of accounts), and then working your way up towards your specific products.

Upvotes: 7

Related Questions