5

This question is an extension/expansion of an already answered question on StackOverflow: Database Design for Invoices

However, I'd like to expand on the idea and find out the correct way to allow customers to pay for invoices using some type of installments (monthly payment) system.

In the answered question, the voted answer utilized a database schema that had the following tables:

  1. Orders table: used for draft/pending orders
  2. Orders Details table: used for line items of pending orders
  3. Invoices table: used to represent finalized (immutable) orders and how much the customer owes.
  4. Payments table: when a customer makes a payment the total amount goes in here.
  5. InvoicesPayments table: this shows how much of each payment should apply to a particular invoice. This table is needed because sometimes payments can apply to more than one invoice.

(for sake of simplification and because this question's focus is the table design I've left out details like triggers, etc.)

Simplified Database Schema

How can I allow for invoices to be broken up into monthly installments? The requirements are as follows:

  1. At the time the order is placed the customer will be able to select how much they want to pay as a down payment and then pay X number of monthly installments until the balance is paid.
  2. For now we can ignore things like interest rates.
  3. Sometimes a customer will make a payment that will cover more than one monthly payment. For example, lets say they forget to pay one month and next month they pay for the current month and the past month in a single payment.

Any help/guidance you can offer would be most appreciated. Thank you!

-- AFTERNOON UPDATE --

After considering the responses so far and doing some tinkering on my own, what do you think about having a schema like the following:

schema 2.0

In the above proposed schema, all invoices will have a ScheduledPayments record. If the invoice is due all at once (pay in full), it will have one record in there. If the invoice is paid in installments it will have X number of records for each payment (plus any down payment).

Then, when a payment is made, a record is added to the payments table and one or more records is added to the relationship table "ScheduledPaymentsPayments" which will connect a payment to both invoice(s) and monthly payment(s).

What do you think of this solution? Do you foresee any problems with it or can you suggest any alternatives that might be better?

Community
  • 1
  • 1
Dave L
  • 3,095
  • 3
  • 16
  • 25
  • Does it matter which invoice a payment is made against? (Please say 'no') – Strawberry Feb 01 '16 at 16:30
  • @Strawberry yes. By tracking which invoice(s) a payment is applied to you can tell which invoices have been satisfied. I think I know what you might be getting at. On a macro level only the customer total statement balances are important. However for this project, I need to make sure each invoice has its balance satisfied. – Dave L Feb 01 '16 at 16:33
  • But why does it matter? A customer has an account. The balance of that account is simply the total amount of invoices less the total amount paid. – Strawberry Feb 01 '16 at 16:38
  • @Strawberry for simplicity sake I didn't include all of the project details, however for this particular application each invoice is sold by an agent who gets paid a commission at the time an invoice is paid. A customer can have multiple invoices which were sold by different "agents". Therefore I need to track which invoice(s) a payment applies to. – Dave L Feb 01 '16 at 16:41
  • If it was my system, I would do it on the basis that the oldest invoice gets paid first. That just seems easier (and fairer) – Strawberry Feb 01 '16 at 18:23
  • That makes sense to me as well and if I could make that call I would. Unfortunately the system needs to be able to accept payments in any order (non-chronological). – Dave L Feb 01 '16 at 18:49
  • 1
    I happen to have two clients that pay invoices in an utterly random and arbitrary fashion, with amounts that correspond to nothing that makes any sense to me. If I had to work out which payment belonged to which invoice, I seriously would lose the plot. But good luck with that! – Strawberry Feb 01 '16 at 18:55

1 Answers1

0

You can add columns to the Orders table for DownPaymentAmount, MonthlyPaymentAmount, NumberOfMonthlyPayments, and that satisfies the first requirement.

The third requirement looks like the database can already handle it with the InvoicesPayments table, with the logic being handled in the app layer.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Using your method of storing the installment terms in the orders table and calculating everything on the fly how would you know which payment number a particular payment applies to? Wouldn't I need a separate table to track that info? – Dave L Feb 01 '16 at 16:36
  • That also could be calculated on the fly rather than stored in a table, but if you want it to be easier to see, you could create a "payment schedule" table. – Tab Alleman Feb 01 '16 at 16:46
  • I like the idea of storing the payment schedule in a table since it takes some of the load off of the application. If I create a payment schedule table, how would I associate payments with a payment schedule record? I assume I would need another relationship between scheduled payments and payments? Or would I simply eliminate the relationship between Invoices/Payments and instead link them through a Payments/PaymentSchedule table? – Dave L Feb 01 '16 at 16:49
  • 1
    I'm having to guess at what an "Invoice" is in your model (and how or why it differs from a scheduled payment), but I suspect you'll solve this with a three-way bridge table that joins PaymentScheduleID, InvoiceID and PaymentID, so that the same payment can be used to cover multiple scheduled payments and/or multiple invoices. This would replace the InvoicePayment bridge table. – Tab Alleman Feb 01 '16 at 16:52
  • 1
    Good point. I could see someone defining each monthly payment as a separate "invoice". However, the way I've been looking at it is that when someone makes a purchase, the system generates an "invoice" for the total but they are making incremental (monthly) payments towards that single invoice. I'm not sure which method is more common in practice. I can see pros and cons for each way of doing it. – Dave L Feb 01 '16 at 16:57