Page 1 of 1

Petty cash system

Posted: Tue Nov 11, 2003 10:07 am
by JayBird
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

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	1
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

Posted: Tue Nov 11, 2003 10:15 am
by twigletmac
Personally I wouldn't store the opening and closing balances against each record. It would be better to calculate these when the data is retrieved for display.

If you put a starting date record in with the initial balance and then mark each record either debit or credit then it would save a few headaches IMHO.

Mac

Posted: Tue Nov 11, 2003 10:22 am
by Jean-Yves
Strictly speaking, you should never allow a deletion from an accounting package. If the user makes a mistake, they should enter a contra-journal to negate the amount that they have entered, with a code indicating that the original was an error.

But then, I work with accountants most of my time, and I could just be brainwashed :D

Posted: Tue Nov 11, 2003 10:34 am
by JAM
Well Jean-Yves, you indeed have an interesting point. I'm used to this thinking also, as deleting something, makes it un-trackable. And that is not good when the words 'customer' and 'money' is in the same sentences... (Even legal issues can be involved).

However, if it's just for something small 'at home' for personal use, then it might not be as interesting. Something to think about tho.

Posted: Tue Nov 11, 2003 11:09 am
by JayBird
Interesting points

Twigletmac: I thought of that, but it seemed MORE complicated that way. This is because when a user views the informtion, it is going to be month by month, how will i know the starting balance at the begining of a given month? I will have to do calculations from when the petty cash was started. Eventually, this is going to contain masses of data. The way you are suggesting would mean i had to calculate everything from the first entry...i presume!?

Jean-Yves and JAM: Intersting point, and something to consider in the future, but at them moment our company is very small and only certain people are going to be able to access this information, but i will definately keep it in mind.

So, any other ideas?

Should i, or should i not store the opening and closing balances?

Mark

Posted: Wed Nov 12, 2003 2:36 am
by Jean-Yves
Personally, I never store any calculated fields in my tables - it's asking for a maintenance issue down the line!

To get the balance at the start of any particular month, you could work it out from a SQL query, such as:

Code: Select all

$sql = "SELECT SUM(debit_amount) FROM PettyCash WHERE date_spent BETWEEN #" . $startOfCashDate . "# AND #" . $beginningOfMonthDate . "#";
Hope this makes sense - I haven't had any coffee yet!

Posted: Wed Nov 12, 2003 4:30 am
by JayBird
look like a good idea, just wondering what the hashes are for in that statement?

Mark

Posted: Wed Nov 12, 2003 4:38 am
by Jean-Yves
Can't remember off-hand if MySQL needs them, but in SQLServer, Access, SQLBase, etc they delimit dates so that the SQL parser does not get confused. Otherwise 2003-11-12 could be viewed as 2003 minus 11 minus 12!

Posted: Wed Nov 12, 2003 4:58 am
by twigletmac
MySQL doesn't need the hash delimiters but I can't remember if you have to quote the dates or not.

Mac

Posted: Wed Nov 12, 2003 11:27 am
by JAM
Just to mention, that you can also use something as:

Code: Select all

SELECT SUM(debit_amount) FROM PettyCash WHERE month(date_spendt) = $foo
...or similiar that fits your layout.

<sidenotes>
And btw, is 'between' really supported in MySQL at all?
I've used it in MSSql (but without hash delims), so I'm kind of confused now. ;)

Posted: Wed Nov 12, 2003 2:58 pm
by JayBird
JAM : I have used between to solve this problem and it works great.

Thanks to everyone

Mark

Posted: Wed Nov 12, 2003 3:11 pm
by Saethyr
Just as a side note on this topic. I was speaking to my wife last night who is an accountant and she told me to let you all know that there is really no such thing as delete or editing records in accounting. If you allow a person to do this you run the risk of theft. A mistake should always be readded to the total through a journal entry explaining EXACTLY what happened, then you can create a second debit from the account. She also told me to let you all know if you had any questions regarding accounting stuff to holler at me and I could pass them on, she would answer as best she is able.

And yes I saw Jean's post, just passing on what she said.

Posted: Thu Nov 13, 2003 4:31 am
by JayBird
Thanks Saethyr, i have a lot more accounting stuff coming up in the next few months, so i might take you up on your offer now and then :)

Mark

Posted: Thu Nov 13, 2003 5:50 am
by Jean-Yves
JAM:
And btw, is 'between' really supported in MySQL at all?
I've used it in MSSql (but without hash delims), so I'm kind of confused now.
I did a check in the MySQL manual before posting, as I wasn't too sure either ;) , but yes, it's in the SQL Command, type and syntax index.

Posted: Thu Nov 13, 2003 10:22 am
by JAM
Yah, i just found it also. =)