SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best way to record payments?


Best way to record payments?

Author
Message
Christopher Ford-327546
Christopher Ford-327546
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 454
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

Christopher Ford-327546
Christopher Ford-327546
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 454
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

Matt Miller (4)
Matt Miller (4)
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30273 Visits: 19009
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?
Christopher Ford-327546
Christopher Ford-327546
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 454
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

Matt Miller (4)
Matt Miller (4)
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30273 Visits: 19009
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?
Christopher Ford-327546
Christopher Ford-327546
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 454
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

Matt Miller (4)
Matt Miller (4)
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30273 Visits: 19009
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?
Christopher Ford-327546
Christopher Ford-327546
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 454
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search