I'm writing an application that will involve recurring billing of a monthly (or weekly) fixed amount, and it can last until subscription is canceled. The customer can pay several periods in advance. He can cancel subscription, and then come back after certain unpaid periods. I need the sistem to let me know when a period is past due.
So I'm burning my brain on how to design the database (maybe is not a database issue but a programming one),
Has any one come to this kind of applications? what approach has been taken?
If you turn off recurring billing, your subscription will expire on the date shown and you won't be billed in the future.
Here's a simple recurring billing definition: it's a subscription. Or more precisely, the recurring billing definition is an ongoing payment on a periodic basis for a product or service. A subscriber will provide their credit card information to pay for the goods or services on a periodic basis.
Recurring billing software is used by many subscription-based businesses to assist with the management of recurring invoices and payments. Companies that use recurring billing software might include gyms and fitness studios, monthly gift boxes, or streaming services.
Setting up a recurring series with Square is easy. Invoices are free to send and you only pay a competitive processing rate once they're paid securely online. You can set up recurring invoices to be automatically emailed to your clients at the frequency you choose.
I think you may be trying to get too clever with the design and overthinking it. If you think about the business problem, each payment interval is effectively an invoice. Why not just create an invoices table and let a scheduled job insert an invoice at certain intervals based on the periodicity of each account and whether it is active during that interval.
By having an actual invoice row you get an InvoiceID that you can reference when seeking payment from a customer and track the payment status individually for each billing.
Sometimes simple is best.
I think you are over complicating it.
Create a table user:
pk id_user
nm_user
fl_status (active, canceled, pendent, etc)
Create a table subscription one user to many subscriptions:
pk id_subscription
fk id_user
fl_type (maybe there are different subscriptions, with different prices)
dt_in
dt_out
Create a table payment one subscription to many payments:
pk id_payment
fk id_subscription
fl_type (card, payment order, something else)
fl_status (generated, sent, confirmed, canceled because of subscription canceled, canceled because of something else, etc)
dt_generated
dt_sent
dt_confirmed
dt_canceled
[I think you will need another fields to follow and confirm the payment, but it depends of your payment model)
Now you will need to build a few robots that will run everyday at some specific time.
If you get all the active clients and the last payment of each one you will know if a new payment needs to be generated if the last confirmed payment was made more than x days compared to the actual date (it depends if it is prepaid, postpaid, etc). If yes, generated a new payment order.
A robot will send a email or something with the order (and flag then as).
Another robot will confirm the payment using your payment model.
Off course, you need to define your model very well because each user status needs a robot to keep things going until it gets to be canceled or sent to a judge because lack of payment. it's a lot of job to do, but there's no big deal.
ps: if it comes to be a more complex system the database will persist, you will have all the information you need, you have a log of every order, you know what happened to each of then because they have dates and status. You can estimate, montly, how many due dates you will have, how many will pay after a day, after two, and so on.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With