Petty cash system

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Petty cash system

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post 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!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

look like a good idea, just wondering what the hashes are for in that statement?

Mark
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post 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!
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

MySQL doesn't need the hash delimiters but I can't remember if you have to quote the dates or not.

Mac
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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. ;)
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

JAM : I have used between to solve this problem and it works great.

Thanks to everyone

Mark
User avatar
Saethyr
Forum Contributor
Posts: 182
Joined: Thu Sep 25, 2003 9:21 am
Location: Wichita, Kansas USA
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Yah, i just found it also. =)
Post Reply