How to go about managing payments in a web app?

September 10, 2007 11:27 AM

PHP/TransactionFilter: Designing a web application where users will have actual monetary accounts. What is the best way to approach this?

I'm currently drafting a spec for a web application. One aspect of this application is that users can buy 'credit' for using certain parts of the service. The user will be able to purchase this credit (using paypal) in chunks of 5, 10, 20 dollars. Each time they use the service, a fraction of their credit (say 40 cents) will be used up, until they run out, whereby they will be able to purchase more credit.

My question is, what would be the best way to go about implementing this programatically? I don't have much experience with transaction based applications, but from what I've read, a transaction/rollback system might be the way to go (correct me if I'm wrong)?

The application will be running on LAMP, using the CakePHP framework. Payments to the site will be made using PayPal, for the moment. I'm pretty knowledgeable with Cake and I have some experience with the PayPal API.

Any advice as to how to approach this would be great. Thanks in advance!

posted by ReiToei to Computers & Internet (8 answers total)

Best answer: I did something like this years ago. In my case I kept a table of all transactions, whether credit or debit. To get their current balance I summed everything up. Periodically I would archive the transactions to another table, and replace all those entries with a single entry for that period.

Yeah, you generally need a transactional system for this. You could probably get by with some kind of locking mechanism if php supports it. The typical problem is the ATM one.

You and your wife got to an ATM at the same time. You have $100 in your account. You both withdraw $100. Here's a sequence of events that could be bad:

* ATM checks your balance: you have $100
* ATM checks your wife's balance: you have $100
* You withdraw $100
* Wife withdraws $100

so you just withdrew $200 from a $100 account. If you place a lock around the whole checking-debit phase then it goes like this:

* ATM checks your balance: you have $100
* ATM checks your wife's balance: whoops, need to wait
* You withdraw $100
* ATM resumes checking your wife's balance: 0
* 2nd withdrawal denied.

It's better to have your RDBMS do this for you than to roll your own. Each DB has it's own mechanism for this, generally, you start a transaction and then either end the transaction when you're finished, or rollback if you encounter an error. I know postgres, oracle and MSSQL support transactions. It's been almost a decade since I've really used MySQL but it did not support transactions at the time (I think it does now though)
posted by RustyBrooks at 11:42 AM on September 10, 2007 [1 favorite]

Make sure that each "charge" has a unique ID, so that you can prevent multiples from going through.
posted by smackfu at 12:19 PM on September 10, 2007

Oh I actually just now noticed the Paypal part. I used paypal for my system also. They have an Instant Payment Notification system which is quite simple to use, and which has it's own unique ids and what not. You just have to be careful to make sure you understand the request they send you and process it properly.
posted by RustyBrooks at 1:16 PM on September 10, 2007

Response by poster: Hi Rusty, thanks for all your help. Your answer really helped me get my head around the concepts. I'll probably use a similar model for my transaction table. I've used the PayPal IPN before in a previous eCommerce project, so that shouldn't be a big deal.

MySQL apparently has support for transactions since version 4.0, which is good news. I also found this useful thread for setting things up in CakePHP.

Trying to plan as much as possible before I start writing any code :)
posted by ReiToei at 1:51 PM on September 10, 2007

Another important consideration is actual transaction records. Some make the mistake of simply recording a balance and then adjusting that balance.

It's much better to keep transactions, and simply build a balance by calculating it from those balances.

SELECT ROUND(SUM(amount),2) AS balance FROM transactions WHERE user_id = 123;

Transactions amounts are simply recorded as a positive double for deposits, and a negative double for withdrawls/charges.
posted by sycophant at 3:41 PM on September 10, 2007

Response by poster: Cheers sycophant. I've been browsing the mysql forums and the guys there agree with you.
posted by ReiToei at 5:08 PM on September 10, 2007

ReiToei writes "MySQL apparently has support for transactions since version 4.0,"

Yes, but you MUST use InnoDB tables ONLY. And what sycophant and RustyBrooks said.
posted by orthogonality at 2:00 AM on September 11, 2007

Response by poster: "Yes, but you MUST use InnoDB tables ONLY. And what sycophant and RustyBrooks said."

Yep, I just discovered that on the CakePHP Google group.
posted by ReiToei at 5:55 AM on September 11, 2007