Best way to record payments?
-
So, I'm designing a database basically from scratch for a client and I'm trying to figure out the best way to record payments.
Every month, the members have a membership fee that needs paid.
I have created two tables, basically modeling the structure after the AdventureWorks SalesOrderHeader and SalesOrderDetail tables.
My question is, how do I handle payments?
Most examples and any documentation or any thing I've been reading only talks about accepting credit cards. Nothing I've found really covers partial payments or over payments.
Members may pay up to 6 months in advance, but they could also be on a payment plan and pay little amounts here and there.
So, how do I record the transactions, and then when the member logs on, quickly show what their balance is?
Do I need a table specifically for transaction history, and then yet another table that shows each members current balance?
Any thoughts are appreciated!
Christopher Ford
-
I looked at this post on the forum:
http://www.sqlservercentral.com/Forums/Topic403433-361-1.aspx
But that didn't really help explain anything. =)
He already had a "plan" for handling payments.
I do not really have a plan for doing this. And most of the accounting type stuff I've done has only been with online shopping carts where partial payment isn't really an option, you either pay or you don't pay. =)
So I'm looking for ideas on how people have handled this in the past and what they've found to work best.
Christopher Ford
-
I know of two basic strategies. The difference between the two is whether you apply a specific payment to a specific bill, or if it just gets applied to "the balance".
In scenario #1 (i.e. correlate the bill to any adjustments to the bill), you'd have an "invoice" table (for things needing to be paid), an payment/adjustments table and a mapping table that relates the payment/adjustment to the bill or bills that it relates to. So:
Invoices -->>---PaymentsApplied------<<------PaymentsAdjustments
(the arrows point toward the MANY side of the relation).
In this case - it also allows you to determine how much of each payment goes against each invoice (and you'd need logic to make sure that sum(detail)=Total payment).
Scenario #2 involves a single table. With an invoiceID, a transaction type, and an amount. Payments get recorded as negatives, invoices as positive.
The fun part is - the answer to getting a balance is the same in both cases. Just run a total (where invoices are positive and payments are negative) and voila - your balance. If payments can be recorded at any time - I'd keep it dynamic (don't record and store the balance anywhere).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Matt,
Perfect, thank you. That helps a lot.
I was worried about the dynamic calculation of it all and wasn't sure if that was the way to go for figuring out their balance since payments could be recorded at any time.
Christopher Ford
-
With the right covering index(es) - you'd have to have an awfully large DB before pre-generating would be worth it (IMO)...
Glad that was helpful.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Matt Miller (2/15/2008)
I know of two basic strategies. The difference between the two is whether you apply a specific payment to a specific bill, or if it just gets applied to "the balance".In scenario #1 (i.e. correlate the bill to any adjustments to the bill), you'd have an "invoice" table (for things needing to be paid), an payment/adjustments table and a mapping table that relates the payment/adjustment to the bill or bills that it relates to. So:
Invoices -->>---PaymentsApplied------<<------PaymentsAdjustments
(the arrows point toward the MANY side of the relation).
In this case - it also allows you to determine how much of each payment goes against each invoice (and you'd need logic to make sure that sum(detail)=Total payment).
Scenario #2 involves a single table. With an invoiceID, a transaction type, and an amount. Payments get recorded as negatives, invoices as positive.
The fun part is - the answer to getting a balance is the same in both cases. Just run a total (where invoices are positive and payments are negative) and voila - your balance. If payments can be recorded at any time - I'd keep it dynamic (don't record and store the balance anywhere).
I'm going to guess that implementing the logic of sum(detail)=Total Payment would best be implemented on the application side in a .Net class library?
I can't think of an "easy" way to do that in SQL when you want to apply a payment to several invoices, starting with the oldest first and going forward OR by customer choosing which invoices it gets applied to.
Christopher Ford
-
Christopher Ford (2/15/2008)
I'm going to guess that implementing the logic of sum(detail)=Total Payment would best be implemented on the application side in a .Net class library?I can't think of an "easy" way to do that in SQL when you want to apply a payment to several invoices, starting with the oldest first and going forward OR by customer choosing which invoices it gets applied to.
I would set up that logic in both places (DB and UI). In other words - they don't get to submit without the numbers tying in, but also have a secondary check in the DB, so that it will catch you should you have a "momentary lapse" when coding on the UI side.
How you apply the payments or adjustments (keep in mind you could have more than straight payments and invoices, like - discounts, reversed payments, promos, cancellations, writeoffs, late fees, etc...) is a business decision (project requirements).
While you might care to have the "oldest first" method as the default method - you may find it's not what the client had in mind (meaning - if they want to apply the payment to a specific bill - they probably want to make to control WHICH bills this applies to).
The "picking which invoice applies" would be a gridview control or something like that (showing you multiple rows, etc...)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Matt Miller (2/15/2008)
Christopher Ford (2/15/2008)
I'm going to guess that implementing the logic of sum(detail)=Total Payment would best be implemented on the application side in a .Net class library?I can't think of an "easy" way to do that in SQL when you want to apply a payment to several invoices, starting with the oldest first and going forward OR by customer choosing which invoices it gets applied to.
I would set up that logic in both places (DB and UI). In other words - they don't get to submit without the numbers tying in, but also have a secondary check in the DB, so that it will catch you should you have a "momentary lapse" when coding on the UI side.
How you apply the payments or adjustments (keep in mind you could have more than straight payments and invoices, like - discounts, reversed payments, promos, cancellations, writeoffs, late fees, etc...) is a business decision (project requirements).
While you might care to have the "oldest first" method as the default method - you may find it's not what the client had in mind (meaning - if they want to apply the payment to a specific bill - they probably want to make to control WHICH bills this applies to).
The "picking which invoice applies" would be a gridview control or something like that (showing you multiple rows, etc...)
Exactly, they will be able via a gridview control to pick which invoices they want to pay.
And yes...all those other wonderful things you mentioned, promo's, write-off's etc.
I *should* be able to do all of that in the PaymentsApplied table with a TransactionType column that would define what kind of transaction it was, payment, charge, discount, writeoff, etc. As long as it's tied back to invoice, that should let me reconcile things later correct?
Christopher Ford
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply