So at any given point in time one of our customers may want to know what he/her balance might be.
This is relatively simple and a good use of triggers. Maintaining a current balance table is typically expected in any high transaction financial system. You need to know if a withdrawal/payment can be made before you even include the transaction to the main table. Real time calculation every time you want to check this can be painful.
In order to do this we would have to add up all the transactions to come up with a balance for this year or even as a whole.
Why would you ever want a balance for 'this year'? You want to know if another transaction can occur, no? If you're looking to do a kind of banking report, as to the balance as per a particular date, what I recommend you look at is running totals in SSRS. This way you can pull their transactions and let the report do the day to day valuations.
We're thinking about running a ETL twice a day to extract this aggregated data to a data warehouse which would contain the total balance for each customer.
Overkill. Proper setup and indexing means that you won't require this out of date system to handle immediate balance expectations. You do this when you're trying to do BI work on 'your spending for the year, by category' type stuff... or whatever (depends on the questions you want to ask). I wouldn't do this for balance reporting.
We have very little experience in dealing with database with tons of records, any sources that I can read and learn about would be greatly appreciated.
To echo the above, 10-20 million records isn't a lot anymore. Just need to get yourself straightened out for indexing, expected usage, query techniques, etc. so you can set it up right.
EDIT: Sorry, nasty necro here. I really should have looked at the dates on the OP.
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA