database design

[Adam Akhtar]

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?

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

 

[Robert Klemme]

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

 

[Todd Benson]

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

 

[Adam Akhtar]

Thanks very much for for both of your comments. I think for this script
Im going to just calcuate on teh fly but try and seperate this code so
that in the future when i become a bit better with db design I can swap
it for some other implementation.

Thank you once again!

adam

ps are there any good books on db design which arent product (i.e. MSQL)
specfic.

 

[Robert Klemme]

ps are there any good books on db design which arent product (i.e. MSQL)
specfic.

Plenty:

http://www.amazon.com/s/qid=1228565...ripbooks,k:database+design&sort=relevancerank

http://www.amazon.com/s/ref=nb_ss__...l+database+design&x=0&y=0&sprefix=relational+

http://www.amazon.com/s/ref=nb_ss_b...ooks&field-keywords=relational+theory&x=0&y=0

For example

http://www.amazon.com/dp/1590597699

You'll also find numerous hits on the web denoted to database design,
rlational theory and database normalization.

Kind regards

robert

 

[Coey Minear]

One book that I would definitely recommend is "Mastering Data
Modeling" by John Carlis and Joseph Maguire:
http://www.amazon.com/dp/020170045X

(Full disclosure: my graduate database course was taught by John
Carlis, and he was my faculty advisor for my Masters project.)

The book's focus is on creating logical data structures (LDS), which
are diagrams of the data you are interested in tracking. And from
that point of view, the book is beyond database-agnostic; the data
could be stored on index cards for all they care. The point is to
make you think about what is important to be stored in the first
place, and how it relates to other data. (One of the last chapters
does cover how to turn an LDS into a database schema.)

The book is extremely easy to read, and it has frequent "Story
Interludes" where the authors tell a story from their own experience
to help enforce a subject that was recently covered or to just give
some advice with making your own LDS's. And if you are able to follow
the process with your own data, you don't have to worry about
"normalization", because the data will have normalized itself.

 

[Todd Benson]

One book that I would definitely recommend is "Mastering Data
Modeling" by John Carlis and Joseph Maguire:
http://www.amazon.com/dp/020170045X

(Full disclosure: my graduate database course was taught by John
Carlis, and he was my faculty advisor for my Masters project.)

The book's focus is on creating logical data structures (LDS), which
are diagrams of the data you are interested in tracking. And from
that point of view, the book is beyond database-agnostic; the data
could be stored on index cards for all they care. The point is to
make you think about what is important to be stored in the first
place, and how it relates to other data. (One of the last chapters
does cover how to turn an LDS into a database schema.)

The book is extremely easy to read, and it has frequent "Story
Interludes" where the authors tell a story from their own experience
to help enforce a subject that was recently covered or to just give
some advice with making your own LDS's. And if you are able to follow
the process with your own data, you don't have to worry about
"normalization", because the data will have normalized itself.

I'll check the book out, but, I'm severely skeptical, having read
Chris Date, Celko, Vadim Tropashko. Data does not normalize itself,
and even if it did, that would not make it the best model. Maybe
those authors cover that aspect. I'll definitely hit the local book
store and see if they have it.

Thanks for the info,

Todd