Re: database design

2008/12/5 Adam Akhtar <[e-mail]>:
> 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?

Yes, because in that case you would use a more powerful RDBMS anyway
and processing large volumes of data is where they excel.

Of course, if you frequently need that aggregate sum, it may make
sense to cache the calculated balance somewhere and reuse it. You
could do that with a materialized view or triggers (at least in
Oracle).

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

I'd separate it because the current balance of a category is not
really a property of the category. Rather, the category is used as a
criterion to calculate this - and there might be more criteria, e.g. a
time range. So, I'd rather leave this out of the category table and if
you want to cache it, put it into a different table. You can even
introduce a view that will calculate these values on demand and later
only change the view and keep your application code unchanged.

Kind regards

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

No, sounds perfectly reasonable (apart from the "balance in the
category table" thingy).

Kind regards

robert

-- 
remember.guy do |as, often| as.you_can - without end