handling payment installments in php / database

[eluser]codeninja[/eluser]
Hi,

I am trying to implement a multi installment payment processor. I am using CI and the built in class to do this. Right now I am struggling with defining a proper database schema to handle the multi-installment payments. How should I structure so that invoices are created properly? Want to keep the accounting as simple as possible.

[eluser]smilie[/eluser]
What do you mean with multi-installment?

Cheers,
Smilie

[eluser]Narkboy[/eluser]
I would suggest:

Table: invoices - contains user id, dates, total value.

Table: payments - contains invoice id, dates, payment value.

If needed:

Table: basket - contains item ids, invoice id. You could put the backet id into invoices though.

Then you can create a statement very easily - for user id, get invoices, for invoices get payments and basket contents.

You'll need to calculate outstanding balance because it won't be stored any where, but that's a reporting issue in any case.

You may also need:

Table: payment_schedule - contains invoice id, payment interval, payment value (or percentage etc)

Which can be used to automatically email an invoice or a reminder to the customer to ask for a payment. You'll need an automated (or manual) function to sweep the db, check the time since last payment against the interval and work out if apayment is due.

Basically, all you're doing is allowing invoices to be raised and not paid (in full) immediatly. It's the same as a sales schema, but with the extra layer of payments.