Normalizing tables for payment on delivery of a single monthly invoice with entire balance due

Question

I'm trying to wrap my head around the proper way to design tables for a billing policy where the customer can pay for each delivery as they are completed, or they can receive a monthly invoice with the entire balance due. This is a small bit of what I have already...

I'm not sure where to go... should I create a table for paid in full and monthly statement and relate them to the invoice table... or should I relate those to the payment table... or am I all over the place....

Answer 1

It's a lot more complicated than you think. You also have to account for partial payments, overpayments, payments on account, refunds and other real world complications.

The only way to approach this which will actually reflect the ugliness of the real world is to treat individual charges as one thing (your deliveries), summary documents (monthly invoice/statement) as another thing, and payments/refunds as another thing.

From there you need to join these three separate things together with relationships. The temptation is to join monthly invoices to payments, directly, but that would be a mistake. At least, it would make your life more complicated than it needs to be.

The relationship between charges and invoices is relatively easy. If your customer gets a monthly invoice, then all their packages from that month point at that invoice. This makes a many to one relationship from Package to Invoice.

The more complex part is the relationship between payments and charges. For this you need a new table Payment_Application which is a many-to-many intersection between charges (Packages) and Payments. This intersection entity will also have the amount of money paid for each package. This is important because you can record here whether the amount paid was too much, too little or the correct amount.

If you want to get more sophisticated, you could normalize the payment information so that you have a payment header and detail. This lets you keep information about a payment you receive separate from information about how you choose to apply that payment.

Consider the following:

Here you have an (optional) invoice header and an invoice detail (Package) as well as a payment header and an (optional) payment detail, which may or may not relate to an invoice detail. An arrangement like this will cover the various ugly things that happen when you need to track the exchange stuff for money.