# Database design **URL:** \ **Category:** Ruby\ **Created:** [December 5, 2008, 10:01am UTC](https://www.ruby-forum.com/t/database-design/154935 "2008-12-05T10:01:33Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![scudco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@scudco](https://www.ruby-forum.com/u/scudco)\ **Post date:** [December 5, 2008, 10:01am UTC](https://www.ruby-forum.com/t/database-design/154935/1 "2008-12-05T10:01:33Z")
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? ---
**Author:** ![scudco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@scudco](https://www.ruby-forum.com/u/scudco)\ **Post date:** [December 5, 2008, 10:21am UTC](https://www.ruby-forum.com/t/database-design/154935/2 "2008-12-05T10:21:26Z")
2008/12/5 Adam A. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): > id category\_name > 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). > 1. 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 ---
**Author:** ![scudco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@scudco](https://www.ruby-forum.com/u/scudco)\ **Post date:** [December 5, 2008, 6:19pm UTC](https://www.ruby-forum.com/t/database-design/154935/3 "2008-12-05T18:19:08Z")
On Fri, Dec 5, 2008 at 2:55 AM, Adam A. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > id category\_name > 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 ---
**Author:** ![scudco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@scudco](https://www.ruby-forum.com/u/scudco)\ **Post date:** [December 6, 2008, 1:15pm UTC](https://www.ruby-forum.com/t/database-design/154935/4 "2008-12-06T13:15:46Z")
On 06.12.2008 04:58, Adam A. wrote: > ps are there any good books on db design which arent product (i.e. MSQL) > specfic. Plenty: [http://www.amazon.com/s/qid=1228565209/ref=sr\_st?keywords=database+design&rs=1000&page=1&rh=i%3Astripbooks%2Ck%3Adatabase+design&sort=relevancerank](http://www.amazon.com/s/qid=1228565209/ref=sr_st?keywords=database+design&rs=1000&page=1&rh=i%3Astripbooks%2Ck%3Adatabase+design&sort=relevancerank) [http://www.amazon.com/s/ref=nb\_ss\_\_1\_11?url=search-alias%3Dstripbooks&field-keywords=relational+database+design&x=0&y=0&sprefix=relational+](http://www.amazon.com/s/ref=nb_ss__1_11?url=search-alias%3Dstripbooks&field-keywords=relational+database+design&x=0&y=0&sprefix=relational+) [http://www.amazon.com/s/ref=nb\_ss\_b?url=search-alias%3Dstripbooks&field-keywords=relational+theory&x=0&y=0](http://www.amazon.com/s/ref=nb_ss_b?url=search-alias%3Dstripbooks&field-keywords=relational+theory&x=0&y=0) For example [http://www.amazon.com/dp/1590597699](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 ---
**Author:** ![scudco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@scudco](https://www.ruby-forum.com/u/scudco)\ **Post date:** [December 8, 2008, 4:52pm UTC](https://www.ruby-forum.com/t/database-design/154935/5 "2008-12-08T16:52:37Z")
One book that I would definitely recommend is “Mastering Data Modeling” by John Carlis and Joseph Maguire: [http://www.amazon.com/dp/020170045X](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. ---
**Author:** ![scudco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@scudco](https://www.ruby-forum.com/u/scudco)\ **Post date:** [December 8, 2008, 10:49pm UTC](https://www.ruby-forum.com/t/database-design/154935/6 "2008-12-08T22:49:51Z")
On Mon, Dec 8, 2008 at 9:45 AM, Coey M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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 ---
**Author:** ![scudco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@scudco](https://www.ruby-forum.com/u/scudco)\ **Post date:** [December 6, 2008, 5:05am UTC](https://www.ruby-forum.com/t/database-design/154935/7 "2008-12-06T05:05:02Z")
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.