When to calculate

[pachjo]

OK this one is really making my head hurt and I would really appreciate some clarity of thought from you guys please.....

This is the table in question

Transactions

TransactionID
TransactionDate
DescriptionID
TransactionAmount
RollingBalance
Void

Now my huge brain aching problem is how and when to calculate the rolling balance field.

The data can be viewed such:

All void and non void
All non void only
All void and non void by date range
All non void by date range

Example say I have 4 transactions like this with rolling balance and all non void

27/05/2007 £10 £10
28/05/2007 £10 £20
29/05/2007 £10 £30
30/05/2007 £10 £40

Now the user voids 29/05/2007 and chooses to view non void only

27/05/2007 £10 £10
28/05/2007 £10 £20
30/05/2007 £10 £30

Now the user chooses to view them all void and non void where the voids appear in blue

27/05/2007 £10 £10
28/05/2007 £10 £20
29/05/2007 £10 £30
30/05/2007 £10 £40

Now Im getting completly brain drained as to at what point do I calculate the rolling balance?

The calaculation needs to know what was the previous rolling balance entry so do I have to recalc the entire table each time the user adds a transaction?

If the dataset table holds only a subset say Jan - Feb and the user adds a transaction for Aug the program would need to reload the table with all records to get the last rolling balance value, is this cpu hungry bad programming?

(mmm what if the program always load the enire table ... but the bindingsource filters to the dates the user selects? Could I still access the entire table for calculation purposes?)

Could/Sould I always store the last rolling balance in a seperate lookup table?

Or....now talking out loud ... should I even be considering this voiding issue?

My thoughts are that I am trying to replicate a bank statement, and importing from my online banking. But if I make an error entering a transaction I wanted to be able to void it to then choose to omit from the display.

It would also allow me to void a whole block of trnasactions in a date range for say archiving purposes.

Have I lost the plot completly in my database design/functionality making it overly complicated for no good reason.

Some sane clear thoughts would be most welcome.

Thanks

 

[cjard]

Over the course of my programming life I have worked on several transactioning systems, from EPOS to bank account management systems

None of them have ever kept a rolling balance in the transactions table itself

This is ALWAYS worked out on a per-report basis using analytics and aggregates

 

[pachjo]

OK this is good to hear.

I have rethought this through and chatted to the wife to get a 'user perspective' and decided to drop the voiding of records. I seem to have completely lost the plot on my train of thought as to why I was doing it this way.

To this end I will replace the voiding operation with a deletion instead which resolves the keeping track of void and non void records.

I see what you mean re the not keeping the rolling balance in the transaction table itself but am unsure how to deal with it.

I system I wrote for my employer back in '98 stored the rolling balance in the transaction table and was calculated as part of an over night process. The database was a read only enquiry application of a snapshot of close of business the day before.

I have thought of walking away from this and doing something else application wise, but this is supposed to be a learning exercise and to quit just defeats the object.

My problem re the rolling balance is this.

The datagridview displays the transactions like an online bank statement.

The idea is that the display will default to the last 3 months transactions as depicted by the date range datetimepickers on the form. (when I was using a listview this was working OK but now I am looking to use the datagridviews .filter to achieve this)

So as I add records consecutively the rolling balance is calculated by a function call.

But this is where I lose it....

As the database grows the calculate rolling balance function will end up working on the entire table each time a transaction is appended...inefficient?

Is it bad practice to have the dgv binded to the entire table to achieve access to the rollingbalance calculate process. Would it still work if the dgv was filtered to show a date range?

If the actual dataset table is limited to only the date range then the rolling balance cannot be calculated correctly as it might be processing a transaction outside of the date range.

Is it bad practice to allow a user to add transactions for prior dates thus rejiggling all transactions after the insertion?

Our system here at work only allows transactions dated the current date or in the future, not the past. But the idea of this application of mine is to allow flexibility, I wont always be able to do my banking on the day the actual physical transaction takes place.

I am also 'trying' to create this application as if it was a professinonal product, yeah don't laugh it never will be, but I am trying to work at it as such to ..... well learn!!

 

[pachjo]

To compound the problem I think of a resolution just to be dashed again!

Based on your approach of the rolling balance....I thought!

The user selects a date range and the data is retrieved.

When retreived it also takes the last transaction the day before the begining of the selected date range to get the base of the rolling balance.

A function then calacluates the rolling balance on this subset of data

WRONG!

There is no rolling balance to be stored in the table...

So how does the calculation function know what value to use as the starting point of the roling balance?

example as at begining of time with no previous trnasactions:

01/05/07 10 10
11/05/07 25 35
21/05/07 10 45
30/05/07 50 95

example as from 21/05/2007, the calculation needs to know that prior to this the rolling balance would be 35.

21/05/07 10 45
30/05/07 50 95

So from here I think I have to calculate the whole table on the fly every time a transaction is appended??

 

[cjard]

Well, banking systems run in CYCLES, and at the end of a cycle, a statement is printed and mailed to the customer
At this time, the balance is worked out and recorded. This can then be the "brought forward" balance so youre effectively only every summing one cycles transactions

We operate cycles on calendar months but it is possible to do it on any period

Suppose a guy has 100 in his bank
every month he spends 5 in the middle of the month and adds 2 at the end of the month:

Transactions table
15 jan, -5
25 jan, +2
15 feb, -5
25 feb, +2
15 mar, -5
25 mar, +2


Balances Table
01 jan, 100
01 feb, 97
01 mar, 94

Process:
End of jan
Get balance, 100
Sum transactions for jan
Add to balance
Record balance 97 as 01 feb

End of feb
Get balance 97
Sum trans in feb

etc..


It is a very dumb idea to post transactions into a closed cycle, because then the accounts wont balance.. I.E in march, DONT go add a transaction with jan's date

 

[pachjo]

Now this is the clarity I seek

It makes perfect sense ... in a banking online situation but not so sure in a 'hap hazard home app'

I understand the idea of not posting transactions out of cycle and as I type the light might be coming on!

In my attempt to be 'flexible' I am losing sight of practicality.

I shall put into place the cycle method you say and let you know the outcome.

Thanks for the input cjard well appreciated