mysql - database design for maintaining customer money detail -

mysql - database design for maintaining customer money detail -

i have maintain business relationship balance customer

for have create table customer(id,name,address,mobno,balance);

balance field store amount.

then amount deposited , withdrawl creating table transaction transaction(id,amount,type,adddate); type deposit/withdrawl,

adddate timestamp , amount deposited/withdrawl.

i performing calculation on amount deposited/withdrawl amount , 1 time again storing balance.

with info client (1,'ajay'.'india',23324,400);

transaction

(1,400,d); balance 400 (2,300,w); balance 100 (3,700,d); balance 800 (4,200,w); balance 600

i facing problem if 1 wants update transaction. (3,500,d); balance 600 //new balance after other info not show proper value.

i want know proper way store amount , transaction or there batter way.

an after update trigger looks job:

create trigger balanceupdate after update on transaction each row begin set @diff = new.amount - old.amount; if(new.type = "withdrawal") set @diff = -@diff; end if; update client set balance = balance + @diff id = new.customer_id; end

this alter little depending on real table structure, thought correct. compare value before update value after update, , adjust balance in right direction difference.

here's quick demo on test data: http://sqlfiddle.com/#!9/3a43b/2

a little more tweaking required if transaction type changed withdrawal deposit, , vice versa above should plenty of start there if necessary.

mysql database-design