On Fri, Dec 5, 2008 at 2:55 AM, Adam Akhtar <adamtemporary / gmail.com> wrote:
> 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! :
> $50" revenues.
> 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.
>
> id  category_name
> 1   entertainment
> 2   transport
> 3   utilities
>
> 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
> 1) 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?
> 2) 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?

It really depends on your traffic for updates, inserts, deletes, and
selects.  When it comes to monetary transactions, well, they tend to
be somewhat final.  I think your design is fine (but, personally would
use another RDBMS).

>
> Are these solutions ok? or do they violate some database desing
> principle. are they any good introductory texts on db design?

These ideas are pretty good.  You could also have a watermark table
depending on how difficult your select statements will turn out.

Just as a side note, I have rarely seen a database design that handles
time-sensitive data (like cash flow) correctly.  Especially if reports
are important.  Don't let that bother you, though.

I think you have a good setup so far.

hth,
Todd