Storing Payment Details and Subscription Details

Question

I am trying to create a subscription based payment module for a project.

There are three plans

1. Will cost 49$ but right now it will be free till we acquire users.
2. 499$ which will have 15 days trial
3. 799$ which will also have 15 days trial.

We are using Stripe for Payment Integration.

I have a users table with as follows

users(name, email, password, ....)

Now i want to store the following

1. Define and Maintain Plans
2. Which plan a user is subscribed to
3. When a user is subscribed to a plan - details regarding the payments - upgrade - downgrade etc.

Can some one share how i can do it via Mysql tables and if i am missing any key information that i need to store ?

Answer 1

Here it is a little sketch of what i would do in this scenario :

Tables

• Users

  id, nameemail, password, ...

• MaintainPlans

  id, user_id, name, ...

• Payments

  id, name, user_id, ...

• Operations

  id, type, user_id, name, ...

Notes

• The user_id field reference of course the id primary key of the user table
• Each tables can,of course, contains more columns containing more information or relations(i have inserted only the essentials.
• The Operations type field should contain an enumeration with the possible value (upgrade, downgrade), could be defined into and enumerator or into another table referenced (even better) ...