The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Looking for a better database structure

Hello,
I'm currently working on an application for monitoring financial activities.
I need to differentiate between two main categories: income and outlay, and sub catgories of those two main categories.

In order to achive that I've create 4 tables
prefix_income - a table for all the activities under the income category
prefix_income_cat - a table for all the sub categories of the income categroy
prefix_outlay - a table for all the activities under the outlay category
prefix_outlay_cat - a table for all the sub categories of the outlay categroy

Other than been able to differentiate between incomes / outlays by subcategories, I have different payment types. These types are stattic so there is no need for an extra table for them.

The following sql statements are the ones that I've used to create the tables, not that there are some fileds that are irrlevant to my question so you should just ignore them.

Please note that because I'll need to preform a lot of summing up with this table I've add the field `balance` that saves the current balance in each activity so i wont have to use the sum function, but this makes insertation and removal a lot heavier, so I'm not sure if I should use this method or not.

Now what I want to get from my select query is the following data
sum: (the sum of all the activities in the rage with type = 1)';'(the sum of all the activities in the rage with type = 2)
balance: (*only if there is no better way than using the balance)
redemption: (the redemption field)
type: (the type field)

I've created a query that retrives those results but its very heavy and I believe that it can be majorly improved...
The query: