Auto calculation
Moderator: General Moderators
Auto calculation
Hi there
I want to make a cash transaction record (suppose as bank statement) by php and mysql.
Suppose I have the following data in a mysql table:
-----------------------------------------------------------------------------------------------
id | ----date--- | particular | --form-- | -debit- | -credit- |comsn| balance
====================================================================
1 | 13/06/2010 | Previous | ---------- | ------- | --------- | ------- | 3534.89
-----------------------------------------------------------------------------------------------
2 | 13/06/2010 | -deposit- | --cash-- | ------- | 82500.0 | 412.5 | 85622.39
-----------------------------------------------------------------------------------------------
3 | 14/06/2010 | withdraw | -cheque | 26850 | --------- | 134.5 | 58638.14
-----------------------------------------------------------------------------------------------
4 | 14/06/2010 | -deposit- | --cash-- | ------- | 20387.5 | 101.9 | 78923.7
-----------------------------------------------------------------------------------------------
5 | 15/06/2010 | -deposit- | --cash-- | ------- | 20400.0 | 102.0 | 99221.7
-----------------------------------------------------------------------------------------------
6 | 16/06/2010 | withdraw | -cheque | 22660 | --------- | 113.3 | 76448.4
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
problem is, suppose I have to delete row#3 for some reason at later. Then I have to do a complete update query for the whole table or atleast from row#3 to onward for correcting the value of BALANCE column because
BALANCE of row#4 = BALANCE of row#3 + CREDIT of row#4 - DEBIT of row#4 - COMSN (commision) of row#4
so after deletion of row#3, calculation of column#4 will be with row#2 instead with row#3 and then value of column#5 also will be changed and so on up to the last entered row of the table.
Thus I have to process a -
$row2 = mysql_query("SELECT balance FROM register WHERE id=2");
$row4 = mysql_query("SELECT credit,debit,comsn FROM register WHERE id=4");
$newbalance = $row2['balance'] + $row4['credit'] - $row4['debit'] - $row4['comsn'];
mysql_query("UPDATE register SET balance='$newbalance' WHERE id='4';");
and thereafter for each row after row#4.
It seems that this is a simple task for mysql. But think of a table register which has more than 20000 rows. then it becomes completed and creates overload for the mysql database. Moreover, if I have 2000 customers and all are trying to delete/insert/edit the table at the same time, then it will create a serious load on the mysql.
I want to know if there is any system so that I can link the columns inside mysql table (like we do in excel with a formula) so that if someone delete an interim row, calculation of BALANCE column for next rows will be done internally in the mysql table for which I don't have to run 3 queries ( 2 SELECT and 1 UPDATE) for correcting balance column in each row?
or there is some aggregate function to do the above query for each row in a single query?
Any help will be appreciated.
I want to make a cash transaction record (suppose as bank statement) by php and mysql.
Suppose I have the following data in a mysql table:
-----------------------------------------------------------------------------------------------
id | ----date--- | particular | --form-- | -debit- | -credit- |comsn| balance
====================================================================
1 | 13/06/2010 | Previous | ---------- | ------- | --------- | ------- | 3534.89
-----------------------------------------------------------------------------------------------
2 | 13/06/2010 | -deposit- | --cash-- | ------- | 82500.0 | 412.5 | 85622.39
-----------------------------------------------------------------------------------------------
3 | 14/06/2010 | withdraw | -cheque | 26850 | --------- | 134.5 | 58638.14
-----------------------------------------------------------------------------------------------
4 | 14/06/2010 | -deposit- | --cash-- | ------- | 20387.5 | 101.9 | 78923.7
-----------------------------------------------------------------------------------------------
5 | 15/06/2010 | -deposit- | --cash-- | ------- | 20400.0 | 102.0 | 99221.7
-----------------------------------------------------------------------------------------------
6 | 16/06/2010 | withdraw | -cheque | 22660 | --------- | 113.3 | 76448.4
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
problem is, suppose I have to delete row#3 for some reason at later. Then I have to do a complete update query for the whole table or atleast from row#3 to onward for correcting the value of BALANCE column because
BALANCE of row#4 = BALANCE of row#3 + CREDIT of row#4 - DEBIT of row#4 - COMSN (commision) of row#4
so after deletion of row#3, calculation of column#4 will be with row#2 instead with row#3 and then value of column#5 also will be changed and so on up to the last entered row of the table.
Thus I have to process a -
$row2 = mysql_query("SELECT balance FROM register WHERE id=2");
$row4 = mysql_query("SELECT credit,debit,comsn FROM register WHERE id=4");
$newbalance = $row2['balance'] + $row4['credit'] - $row4['debit'] - $row4['comsn'];
mysql_query("UPDATE register SET balance='$newbalance' WHERE id='4';");
and thereafter for each row after row#4.
It seems that this is a simple task for mysql. But think of a table register which has more than 20000 rows. then it becomes completed and creates overload for the mysql database. Moreover, if I have 2000 customers and all are trying to delete/insert/edit the table at the same time, then it will create a serious load on the mysql.
I want to know if there is any system so that I can link the columns inside mysql table (like we do in excel with a formula) so that if someone delete an interim row, calculation of BALANCE column for next rows will be done internally in the mysql table for which I don't have to run 3 queries ( 2 SELECT and 1 UPDATE) for correcting balance column in each row?
or there is some aggregate function to do the above query for each row in a single query?
Any help will be appreciated.
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Auto calculation
I'm really not sure how banks track balances, but this doesn't look like the way to do it. Further, why would you ever delete a row? If the transaction was reversed/disputed or whatever I would think you would reverse it by a credit or debit as a separate transaction.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: Auto calculation
Hello AbraCavader,
It was just an example, you should not worrid about it. Think of it as your personal cash register. And what is the reason of deletion is not the main fact, fact is how it can be done in a easy and simple way. And there always should be an edit/delete option whatever it would be, either a bank register or a personal cash register because any time you can reveal an error that was happened by you in past during data inputing. So there must have a option for editing.
If you know any simple solution please share it.
Thanks
It was just an example, you should not worrid about it. Think of it as your personal cash register. And what is the reason of deletion is not the main fact, fact is how it can be done in a easy and simple way. And there always should be an edit/delete option whatever it would be, either a bank register or a personal cash register because any time you can reveal an error that was happened by you in past during data inputing. So there must have a option for editing.
If you know any simple solution please share it.
Thanks
Re: Auto calculation
Transactions that cancel each other out, and do not affect the balance can simply be hidden from the end user. I don't believe transactions would ever be deleted. Also, I wouldn't store the balance in the transaction table rows, instead I would somehow calculate it on demand.
Re: Auto calculation
Dear Benjamin
Lets think a little bit different. Ok, I should not delete transaction forever. But, suppose on 14/6/2010(id =4), I deposited 20000 USD in bank but during data input, I inserted 20387.5 USD mistakenly and that was remained overlooked for the whole month.
At the end of month when there was a consolidation work I found there was a gap between practical cash-in-hand and result from mysql. Then I searched my cheque book and pay-slip and checked every record. Suddenly I found that on 14/6/2010, although my I deposited 20000 USD (found from my pay-slip) but inserted as 20387.5 USD.
Then what will you do? Will you leave your mistake as it is only for the cause that php/mysql can't do this or you have to correct it? And when there will be a question of correction, there must have a option of editing/updating past data. You must have to updating the record of 14/06/2010. So how will you do this?
Again, I also thought about your second option, that is I will calculate the balance seperately outside of mysql table. But suppose you have a table of 2 years transactions record (2008 to 2010), but you want to see the transaction between 15-Jan-2010 to 15-Feb-2010. Then how will you calculate the balance outside of mysql table? If you want to calculate outside of table, either you have to calculate it from the beginning point of your mysql entry or have to store balance periodically in mysql table.
My second formula is more practical in this case. But I want more easy method rather than this. Like in excel in our pc, if we want cumulative result on column C what we do, we put a equation in cell C2 like this
C2=C1+B2 and on C3 =C2+B3 and so on like below:
-----------A-------- B---C
1---10/10/2010---10--0
2---11/10/2010---20--30
3---12/10/2010---30--60
4---13/10/2010---40--100
I want to know whether there is something like this linking one cell with another cell inside mysql so that if We change in B1/B2/B3, the cumulative result will be changed automatically on C1/C2/C3 by linking formula.
And I am quite sure that there must be an way to do that type of job in mysql. When we go to the bank for taking our bank statement, we do not take the whole life-cycle record, we only take periodical statement and in the bank statement there is a column of showing balance after each transaction (deposit/withdraw). How do they do it?
Lets think a little bit different. Ok, I should not delete transaction forever. But, suppose on 14/6/2010(id =4), I deposited 20000 USD in bank but during data input, I inserted 20387.5 USD mistakenly and that was remained overlooked for the whole month.
At the end of month when there was a consolidation work I found there was a gap between practical cash-in-hand and result from mysql. Then I searched my cheque book and pay-slip and checked every record. Suddenly I found that on 14/6/2010, although my I deposited 20000 USD (found from my pay-slip) but inserted as 20387.5 USD.
Then what will you do? Will you leave your mistake as it is only for the cause that php/mysql can't do this or you have to correct it? And when there will be a question of correction, there must have a option of editing/updating past data. You must have to updating the record of 14/06/2010. So how will you do this?
Again, I also thought about your second option, that is I will calculate the balance seperately outside of mysql table. But suppose you have a table of 2 years transactions record (2008 to 2010), but you want to see the transaction between 15-Jan-2010 to 15-Feb-2010. Then how will you calculate the balance outside of mysql table? If you want to calculate outside of table, either you have to calculate it from the beginning point of your mysql entry or have to store balance periodically in mysql table.
My second formula is more practical in this case. But I want more easy method rather than this. Like in excel in our pc, if we want cumulative result on column C what we do, we put a equation in cell C2 like this
C2=C1+B2 and on C3 =C2+B3 and so on like below:
-----------A-------- B---C
1---10/10/2010---10--0
2---11/10/2010---20--30
3---12/10/2010---30--60
4---13/10/2010---40--100
I want to know whether there is something like this linking one cell with another cell inside mysql so that if We change in B1/B2/B3, the cumulative result will be changed automatically on C1/C2/C3 by linking formula.
And I am quite sure that there must be an way to do that type of job in mysql. When we go to the bank for taking our bank statement, we do not take the whole life-cycle record, we only take periodical statement and in the bank statement there is a column of showing balance after each transaction (deposit/withdraw). How do they do it?
Re: Auto calculation
In your first scenario, the money was indeed in the account until the problem was noticed. It would make sense to add another transaction to correct the mistake. In this case the user would see both the original transaction and the second, which is inline with that would happen on a bank statement.
As for the second question, calculating the balance for a specific date etc, I believe what banks do is store balance information for the start or end of each month in a separate table. If you know the starting balance was $100 on April 1st, on the last day of April you can compute the ending balance and insert a record which would indicate the starting balance for May.
I'm not 100% certain this is how banks do this, but it would make sense to do it this way. I've built transactional systems before and there are a lot of things that can come back to bite you if it's not built correctly from the start.
As for the second question, calculating the balance for a specific date etc, I believe what banks do is store balance information for the start or end of each month in a separate table. If you know the starting balance was $100 on April 1st, on the last day of April you can compute the ending balance and insert a record which would indicate the starting balance for May.
I'm not 100% certain this is how banks do this, but it would make sense to do it this way. I've built transactional systems before and there are a lot of things that can come back to bite you if it's not built correctly from the start.
Re: Auto calculation
A discussion like this can be addressed very simply. MySQL is a relational database. Relational databases are based on mathematical set theory, as developed around 1970 by Dr. E. F. Codd, an IBM mathematician. The SQL language was developed to conform to the definitions and rules established by Dr. Codd. SQL is guaranteed to work ONLY if these definitions and rules are followed. One of the rules states that all fields should contain "fully dependent" data (that is, data in a record should be fully dependent on the primary key of that record). I don't think that's true in your schema. Read http://databases.about.com/od/specificp ... /a/3nf.htm.
Re: Auto calculation
Hello califdon
I know mysql cant be used as microsoft excel, i.e linking one cell with another internally by a mathmetical formula but my intention was to find a simpler way to do that type of job by mysql query. I can still make a script in which someone could delete or edit a transaction from the middle and mysql could still store the balance column and update after deletion. But that requires complex calculation and requires several queries in one operation (i.e by Procedure command) which ultimately will create a lot of pressure on mysql engine. This type of complex query is okay for personal(single) use, but if you want to make for certain number of users and they use it simultaneously (such as portfolio manager for stock market), I am afraid that this could crash the mysql database at any time. So I am looking for some self join /join command which can do the complex queries in a shorter process as much as it can, but it seems it needs a man who is more expertised in mysql and have practical experiences on developing mysql related scripts. Thats why prices of the automation softwares for trancsaction is in so skyhigh level. Anyway thanks to all for advicing and guiding me from yours point of view. Good luck!
I know mysql cant be used as microsoft excel, i.e linking one cell with another internally by a mathmetical formula but my intention was to find a simpler way to do that type of job by mysql query. I can still make a script in which someone could delete or edit a transaction from the middle and mysql could still store the balance column and update after deletion. But that requires complex calculation and requires several queries in one operation (i.e by Procedure command) which ultimately will create a lot of pressure on mysql engine. This type of complex query is okay for personal(single) use, but if you want to make for certain number of users and they use it simultaneously (such as portfolio manager for stock market), I am afraid that this could crash the mysql database at any time. So I am looking for some self join /join command which can do the complex queries in a shorter process as much as it can, but it seems it needs a man who is more expertised in mysql and have practical experiences on developing mysql related scripts. Thats why prices of the automation softwares for trancsaction is in so skyhigh level. Anyway thanks to all for advicing and guiding me from yours point of view. Good luck!
Re: Auto calculation
I think it isn't me who needs the good luck, I am not trying to do something that relational databases are not designed to do. What I described is simply how the "relational model" works. That's what every relational database is based upon. There are many ways to use the relational model to apply to particular requirements. All I am saying is that if you're going to use a relational database, you must become aware of what works and what doesn't work. Fields that are not fully dependent on the primary key don't work.