building monthly expenses application

[Violet_82]

Hi all,
just to practice a bit more with asp.net and c#, I was thinking to build a small application to keep an eye at my monthly and yearly expenses (rent, car, bills, food shopping). I have a few ideas but I thought I'd check with you guys what's the best way to build this, mostly when it comes down to store and retrieve info from the database.
I'm thinking to start with a page containing a form with the following input fields:

Rent:
-Rent cost
Car:
-Car cost
Bills:
-Bills
Food
-Food shopping

When I submit the form the data will be saved in the database (I will obviously allow for blanks in the above form as not all the expenses occur on a monthly basis).
Then there will be another page which displays my results at the end of the month, something like this:

October 2012
Rent cost: this month you've spent xxx£
Car cost: this month you've spent xxx£
Bills cost: this month you've spent xxx£
Food cost: this month you've spent xxx£

November 2012
Rent cost: this month you've spent xxx£
Car cost: this month you've spent xxx£
Bills cost: this month you've spent xxx£
Food cost: this month you've spent xxx£

December 2012
Rent cost: this month you've spent xxx£
Car cost: this month you've spent xxx£
Bills cost: this month you've spent xxx£
Food cost: this month you've spent xxx£

Now let's turn to the database side: here is where it gets complicated, well at least for me.
1)What's better, to keep all the data in one table, or do one table for each month?
2)If 1 table how should I organise the information in it?
3)if two tables how does my application know when to start a new table for a new month?

Aside from these questions, if anybody can suggest a better/easier way to build this, that'll be great (bearing in mind that I'm not a wizard with c# and, SQL etc)

Cheers

[JorgeM]

Based on your description, i'd say that your database would have multiple tables, but not a table dedicated to each month or type of bill. You can do this with one table ("expenses". The table would contain a primary field that identifies each record by ID number, you would also have a date field to capture the date of the transaction, a field to identify the type of expense, then a cost field, and maybe some type of comments/description field for additional information about the transaction. For the type of expense field, i would envision that this field's data type is of type integer. You would then need another table called "Expense Types" and in this table, you have an ID field and expenseName field that has the string value of the expense. For example..

ID      expenseName
-------------------
1       Rent
2       Car
3       Food

Store the id for each expense in your "expenses" table.

Then for the reporting aspects, just use creative SQL techniques to group your data by month, sum your costs, etc...

[Violet_82]

Thanks JorgeM.
A few points. The amount of fields in the “expenses” sounds good, but I was wondering whether having 2 tables will not create additional problems when it comes to getting out the data from the table. Also, presumably the two tables will have to be linked somehow otherwise what’s the point of storing an int reference to another table? Any hint about how to do that, what I need to look into?
About grouping data, where should the data be organised/grouped, in its own SQL table or after I extract the data? What I mean is, if you think about the way I want to display my results, as my previous post, how can I extract that data? I should filter all the “transactions” that took place in November (so presumably grouping transactions by month), then add up all different types of transactions (1,2,3,4,5) for that specific month, correct?

[JorgeM]

Also, presumably the two tables will have to be linked somehow otherwise what’s the point of storing an int reference to another table? Any hint about how to do that, what I need to look into?

this is where you get into the relational part of "relational databases". I am by no means a DB expert, but your example isnt really that complicated. When you have multiple tables that are related in some way where you have a primary key in one table and a foreign key in another, the way to get the related data is via SQL JOINS.

About grouping data, where should the data be organised/grouped, in its own SQL table or after I extract the data?

So there is no right or wrong answer. it also depends if you are trying to process information in real time or you archive large amounts of data and you do back-end processing. This usually determines or guides the database design. For example, in a data warehousing scenario where huge amounts of data is stored used for reporting, you wouldnt want multiple tables related and alot of SQL JOINS since there is a cost (cpu/memory requirements). In your example, my approach would be to organize the data at run time, not in a seperate table. However, there are some instances where you may have a seperate table used where say its very costly to get the data organized and grouped properly and it would take too long to run at run time AND you really dont need real-time data. in that case, you may have a job that runs every hour, day, etc... that inserts the "organzed/grouped" data in this other table.

What I mean is, if you think about the way I want to display my results, as my previous post, how can I extract that data?

For your example, i suspect that you'd have to develop a SQL query that groups your data by month, then by expense type. hard to say what the actual query would look like without the actual tables. SQL provides methods you can use to extract month, year, day information from a datetime data type for example. Or, your application can handle the presentation of data as well.

I should filter all the “transactions” that took place in November (so presumably grouping transactions by month), then add up all different types of transactions (1,2,3,4,5) for that specific month, correct?

yes, i think you have the right idea. it may benefit you to do some reading on db design and maybe go over a few sql tutorials on SQL select and SQL joins to get you going and help you figure out how you want to go about this.

[Violet_82]

Cool, thanks. OK so I'll build the interface first, and then will have a look at a few SQL tutorials, I'm sure I'll post back. I'll close this thread as my questions will no doubt be more specific
Cheers