Database Design for Invoices With Monthly Installments


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?


Answers:


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.