I asked this question over in stack exchange as well but I'm curious to hear the opinions from my fellow CF developers to see if anyone has any insight on this. If you'd like to chime in on the stack exchange thread instead, click here.
I'd like to know the correct/optimal way to create a database schema for a ColdFusion based online store that allows customers to pay their invoices in full or in X number of monthly installments.
I've played around with a couple different ways of accomplishing this and the first issue that came up for me was whether the scheduled payments should be calculated on the fly (by ColdFusion) or whether each scheduled payment should exist as a record in a database.
I'm going to assume that the best-practices way would be to have a separate database table to store these scheduled payments (even if it's just one record for a pay-in-full situation). Here's the database schema I have been playing around with:
The current plan is to create a single invoice when an order is placed. Then I would create X number of scheduled payments records depending on how many payments the customer is going to use to pay off the invoice. Then as payments come in, I will add the record to the payments table and then create a joining record in the r_ScheduledPayments_Payments table with the amount that was applied towards the scheduled payment(s).
Is this how most online stores, e-commerce applications or banks handle this type of setup? In a way it's kind of like a bank loan so perhaps a banking model would be appropriate. I couldn't find any other decent examples online so you're seeing my first draft of a possible solution. I'd love to hear ideas on how to best accomplish this from a CFML dev's perspective.
AdChoices