[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

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
0
HMoellendorf
Asked:
HMoellendorf
1 Solution
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now