Petty cash system
Posted: Tue Nov 11, 2003 10:07 am
I am currently building an application to track petty cash expenditure. This info is stored in a MySQL database, the formatting of which is shown below
Here is all the data in the database:-
The system works perfectly the way i have it at the moment, but the problem comes if someone wants to delete and entry.
Say they want to delete the entry with id 25, all the subsequent rows will need to be re-evaluated for the correct opening and closing balances.
So, two questions
Am i going about this the correct way - if so, do i just itterate through subsequent rows and redo calculations on each one.
Is there a better way to achieve this? If so, how?
Thanks
Mark
Code: Select all
Field Type Null Key Default Extra
------------ ---------- ------ ------ ------- --------------
id int(11) PRI (NULL) auto_increment
date_spent date YES (NULL)
opening_balance float(4,2) YES (NULL)
debit_amount float(4,2) YES (NULL)
description text YES (NULL)
category text YES (NULL)
receipt_no int(6) YES (NULL)
closing_balance float(4,2) YES (NULL)
vat int(1) YES (NULL)Here is all the data in the database:-
Code: Select all
1 2003-10-02 102.68 30.00 Windows Windows 1011 72.68 0
2 2003-10-03 72.68 4.50 Binding Misc 1012 68.18 1
3 2003-10-03 68.18 7.00 Cleaner Cleaner 0 61.18 0
4 2003-10-08 61.18 4.05 Post Post 1013 57.13 0
5 2003-10-08 57.13 1.65 Card Misc 1014 55.48 1
6 2003-10-10 55.48 7.00 Cleaner Cleaner 0 48.48 0
7 2003-10-06 48.48 18.93 Food Scotland 1015 29.55 1
8 2003-10-14 29.55 200.00 Cash in Cash in 0 229.55 0
9 2003-10-15 229.55 3.58 Binding Misc 1016 225.97 1
10 2003-10-15 226.25 1.55 Fly spray Misc 1017 224.42 1
11 2003-10-16 224.42 0.66 Milk Misc 1018 223.76 1
12 2003-10-16 223.76 4.05 Post Post 1019 219.71 0
13 2003-10-17 219.71 7.00 Cleaner Cleaner 0 212.71 0
14 2003-10-17 212.71 1.75 Binding Misc 1020 210.96 1
15 2003-10-17 210.96 1.52 Post Post 1021 209.44 0
16 2003-10-20 209.44 2.99 Day Book Misc 1022 206.45 1
17 2003-10-21 206.45 4.05 Post Post 1023 202.40 0
18 2003-10-23 202.40 3.06 Post Post 1024 199.34 0
19 2003-10-24 199.34 5.00 Bulb Misc 1025 194.34 1
20 2003-10-19 194.34 1.93 Cleaning materials Misc 1026 192.41 1
21 2003-10-24 192.41 7.00 Cleaner Cleaner 0 185.41 0
22 2003-10-27 185.41 20.00 Mobile credit Misc 1027 165.41 0
23 2003-10-24 165.41 52.85 CIPD various Misc 1028 112.56 1
24 2003-10-29 112.56 0.99 Binbags Misc 1029 111.57 1
25 2003-10-30 111.57 1.00 Newspaper Misc 1030 110.57 1
26 2003-10-31 110.57 7.00 Cleaner Cleaner 0 103.57 0
27 2003-11-06 103.57 30.00 Windows Windows 1031 73.57 0
28 2003-11-06 73.57 18.30 Binding Misc 1032 55.27 1
29 2003-11-06 55.27 12.34 Post Post 1033 42.93 0
30 2003-11-07 42.93 7.00 Cleaner Cleaner 0 35.93 0
31 2003-11-10 35.93 200.00 Cash in Cash in 0 235.93 0
32 2003-11-03 235.93 46.51 Food Scotland 1034 189.42 1Say they want to delete the entry with id 25, all the subsequent rows will need to be re-evaluated for the correct opening and closing balances.
So, two questions
Am i going about this the correct way - if so, do i just itterate through subsequent rows and redo calculations on each one.
Is there a better way to achieve this? If so, how?
Thanks
Mark