Customer balance system

Hi EE Community,

I don't know to realize a customer balance system. I have a system, where all bills are entered. I wan't now to create a customer balance in a table. Should all values positive or i.e. bills negative? How would you realize it?

Kind regards
Henning
LVL 3
HMoellendorfAsked:
Who is Participating?
 
Beverley PortlockCommented:
You have two options here.

1) Simply maintain a list of all transactions on the customer account. The customer balance is then the sum of all the transactions at any point in time.

2) To apply the balances to a running total for a customer. You only have one total to worry about and it is always current.

There are disadvantages to either method. My preference is to store all transactions and sum up the value to a point in time. This may be CPU intensive, but it means you have a transaction history (eats space!!!!) and you can reproduce statements.

I suppose that at a minimum you want something like

CREATE TABLE IF NOT EXISTS tblCustTrans (
     transId INT auto_increment,
     transCustCode VARCHAR(20) NOT NULL,
     transDate DATE NOT NULL,
     transReason VARCHAR(50) NOT NULL,
     transAmount double(10,2) NOT NULL DEFAULT '0.00',
     PRIMARY KEY( transId )
)


then something like

SELECT SUM(transAmount) FROM tblCustTrans WHERE transCustCode = '......'

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.