table structure for Paypal subscriptions

[seany123]

Hello,

I want to allow for users to pay a monthly or yearly subscription for a premium service.

what would be the best table structure for managing the payments and subscriptions?

 

I already have a table which retrieves the paypal IPN and saves the details into a table and assigns the users unique id.

 

so from there should i be updating the users table which (which holds the users username/email/pass/cookies etc) with subscription details?

 

or should i create another table which holds the subscription details for each user?

 

Also what date/time format should i be using to know when the user subscription starts/ends?
whats the best way to handle auto-renewals?
 

any help would be great

 

Thanks

[requinix]

Is the recurring aspect happening on your site or in PayPal?

[seany123]

Is the recurring aspect happening on your site or in PayPal?

Thanks for your reply, we have a PayPal subscription button, which creates a recurring payment on PayPal.

there are 2 buttons, which allow for 30-day recurring or 1-year recurring.

 

Sean

[requinix]

PayPal will IPN you every time stuff happens with the subscription: charged, charge failed, subscription cancelled, etc. It's all documented somewhere.

 

When the message comes in you check what it means and update the user's access accordingly. Successful charge? Keep the subscription. Failed charge? I suggest keeping the subscription active because PayPal will try again. Cancelled? End the subscription.

 

In the database I highly recommend logging all IPN messages. Every single one. It's remarkably useful for customer support and account troubleshooting.

 

Onto your questions.

 

what would be the best table structure for managing the payments and subscriptions?

Two tables for IPN message logs (one indexes the message itself, one stores the individual details of each message) and one table for subscription history.

 

Beyond that it depends on your application. You may want to put a field in the user table indicating whether they have an active subscription. Personally I wouldn't, and I would just look up in the subscriptions table to see what they have active.

 

so from there should i be updating the users table which (which holds the users username/email/pass/cookies etc) with subscription details?

 

or should i create another table which holds the subscription details for each user?

A user could have multiple subscriptions, even if only one is active at once. For example they pay, use the subscription, stop, and then come back later. So that means it must have its own table.

 

Also what date/time format should i be using to know when the user subscription starts/ends?

Times won't be very useful. Stick with DATEs.

 

whats the best way to handle auto-renewals?

PayPal will do that.

 

What you do depends on what you want. You could just store that the subscription is active and turn that off when billing fails or the subscription (in PayPal) is cancelled. You could store the subscription is active and the next expected payment date. You could store the subscription is active, the next payment date, the most recent successful payment date, the number of failed payments, and so on.

 

Store what you need to know now. As long as you're recording the full details of all IPN messages then you can always change what you're storing later as your application evolves because you can fill in missing data from those IPN records you've accumulated.

[seany123]

PayPal will IPN you every time stuff happens with the subscription: charged, charge failed, subscription cancelled, etc. It's all documented somewhere.

 

When the message comes in you check what it means and update the user's access accordingly. Successful charge? Keep the subscription. Failed charge? I suggest keeping the subscription active because PayPal will try again. Cancelled? End the subscription.

 

In the database I highly recommend logging all IPN messages. Every single one. It's remarkably useful for customer support and account troubleshooting.

 

Onto your questions.

 

Two tables for IPN message logs (one indexes the message itself, one stores the individual details of each message) and one table for subscription history.

 

Beyond that it depends on your application. You may want to put a field in the user table indicating whether they have an active subscription. Personally I wouldn't, and I would just look up in the subscriptions table to see what they have active.

 

A user could have multiple subscriptions, even if only one is active at once. For example they pay, use the subscription, stop, and then come back later. So that means it must have its own table.

 

Times won't be very useful. Stick with DATEs.

 

PayPal will do that.

 

What you do depends on what you want. You could just store that the subscription is active and turn that off when billing fails or the subscription (in PayPal) is cancelled. You could store the subscription is active and the next expected payment date. You could store the subscription is active, the next payment date, the most recent successful payment date, the number of failed payments, and so on.

 

Store what you need to know now. As long as you're recording the full details of all IPN messages then you can always change what you're storing later as your application evolves because you can fill in missing data from those IPN records you've accumulated.

This is a really great reply!

I'll be able to get something working from what you have outlined.

 

Thanks