We have a users table that has userID as a key and all the other user information. I need to create a useraccount table that will track the debits/advance deposits and credits/sales transactions performed by the user. This solution needs to be scalable. i.e. both the number of users as well as number of transactions per users are likely to be very high. We should be able to quickly able to find the current balance of a user to accept or decline a sales transaction.
I don't need an actual table definition, but need some pointers.