1

I work on contracting Company database " sql server " . I'm lost what’s the best solutions to calculate their customers balance accounts.

  1. Balance table: create table for balance and another for transactions. So my application add any transactions to transactions table and calculate the balance according to balance table value.

  2. Calculate balance using query: so I'll create transactions table only.

Note: the records may be up to 2 million records for each year, so I think they will need to backup it every year or some thing like that.

any new ideas or comments ?!

James Z
  • 12,209
  • 10
  • 24
  • 44
Anas
  • 83
  • 1
  • 8
  • Read https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server, it is very helpful to decide which method is better. – Marwan Almukh Oct 28 '17 at 05:35

4 Answers4

0

Comments on your 2 ways:

  1. Good solution if you have much more queries than updates (100 times or more). So, you add new transaction, recalculate balance and store it. You can do it in one transaction but it can take a lot of time and block user action. So, you can do it later (for example, update balances onces a minute/hour/day). Pros: fast reading. Cons: possible difference between balance value and sum of transactions or increasing user action time
  2. Good solution if you have much more updates than reads (for example, trading system with a lot of transactions). Updating current balance can take time and may be worthless, because another transaction has already came :) so, you can calculate balance at runtime, on demand. Pros: always actual balance. Cons: calculating balance can take time.

As you see, it depends on your payload profile (reads or writes). I'll advice you to begin with second variant - it's easy to implement and good DB indexies can help you to get sum very fast (2 millions per year - not so much as it looks). But, it's up to you.

Backs
  • 24,430
  • 5
  • 58
  • 85
0

I would have a transactions table and a balances table as well, if I were you. Let's consider for example that you have 1 000 000 users. If a user has 20 transactions on average, then getting balance from a transaction table would be roughly 20x slower than getting balance from a balances table. Also, it is better to have something than not having that something.

So, I would choose to create a balances table without thinking twice.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
0

Definitely you must have a separate Balance table beside transaction table. Otherwise during read balance your performance will be slower day by day as transaction increasing and transactions will be costly as other users may lock the transaction table to read balance at the same time.

Esty
  • 1,882
  • 3
  • 17
  • 36
-1

This question would seem to have a lot of opinion, and I was tempted to close it.

But, in any environment where I've been where customers have "balances", a critical part of the business is knowing the current balance for each customer. This means having a historical transaction table, a current balance amount, and an auditing process to ensure that the two are aligned.

The current balance would be maintained whenever the database is changed. The "standard" method is to use triggers. My preferred method is to encapsulate data changes in stored procedures, and have the logic for the summarization in the same procedures used to modify the transaction data.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786