im new to databases and im currently using a flatfile db called
KirbyBase which is pretty nifty.
Im making a simply budgeting script which allows me to log expenses
e.g. “big mac lunch : -$3.50” and any incomes such as “Lottery Win! :
One table called receipts keeps these individual expenses / incomes
which can be assigned a category id. This category id is used to lookup
the category title in a seperate category table.
One thing that i will have to do often is calculate the overal balance
for a certain category i.e. how much money i have spent.
There are a few ways i thought of to do this
- just calculate it on the fly each time its needed by selecting the
records for the category and summing up. For this simple script its ok
as records wont ammount to that much. But for the sake of learning lets
assume that this could collect a lot of records. Is this ok?
- create a field in the category table called “balance” and everytime a
new record is added to the receipts table, update the relevant
categories “balance” field in the categories table by performing a
similiar calculation to the one above. Or should i be making a seperate
table entirely for this?
Are these solutions ok? or do they violate some database desing
principle. are they any good introductory texts on db design?