Recurring invoice database development

Question

I am writing an application that will include periodic billing for a monthly (or weekly) fixed amount, and may continue until canceled. The customer may pay several periods in advance. He can cancel the subscription and then return after certain unpaid periods. I need a system to inform me of late payments.

So, I burn my brain on how to create a database (maybe this is not a database problem, but a programming one),

Does anyone come up with this kind of application? What approach was taken?

Answer 1

I think you might be too smart with design and overdo it. If you are thinking about a business problem, each payment interval is actually an account. Why not just create a table of invoices and let the scheduled task insert an invoice at certain intervals depending on the frequency of each account and whether it is active during this interval.

Having the actual line of the invoice, you get an InvoiceID, which you can refer to when searching for a payment from the client and track the status of the payment separately for each invoice.

Sometimes easier.

Answer 2

I think you complicate this too much.

Create a table user:

pk id_user nm_user fl_status (active, canceled, pendent, etc) 

Create a subscription to the table for one user for many subscribers:

 pk id_subscription fk id_user fl_type (maybe there are different subscriptions, with different prices) dt_in dt_out 

Create a payment table for 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 create several robots that will work every day at a specific time.

If you receive all active customers and the last payment of each of them, you will find out whether a new payment should be generated if the last confirmed payment was made more than x days compared to the actual date (this depends on the prepayment, postpayment, etc. .d.). If yes, a new payment order has been generated.

The robot will send an email or something in that order (and then a flag).

Another robot will confirm the payment using your payment model.

Of course, you need to clearly define your model, because each user status needs a robot so that everything is in order until it is canceled or sent to the judge due to lack of payment. it is a lot of work, but there is no big deal.

ps: if it’s a more complex system, the database will be saved, you will have all the necessary information, you have a log of each order, you know what happened to each of them, because they have dates and status, you can evaluate how much time, how much time you will have, how much it will pay after a day, after two, etc.