Recording history

[fiveshorts]

Hi, again.

OK, so I'm still building this financial trading DB.

It's now occurred to me that a 'historical performance' functionality would be very attractive.

As a new month (or even day) begins, various performance values of the trader's account are logged to new, waiting fields, allowing all manner of comparative studies to calculate against still-stored previous months' totals.

But how do I capture, say, a trader's nett profit/loss on a monthly basis, automatically?

Any help appreciated,

Thanks

[Simon K]

Generally, you should not "store" calculated data - so on that basis the historical data should be recalculated whenever you need it (unstored calcs).

However, one of the reasons for storing calc'd data (aka denormalising data) is performance gains when you have very large volumes of data.

That is to say, if you have a very large amount of data, it may be more effective to store the updated historical data on the incidence of each new transaction (ie moves the overhead of the calculation to when the tx occurs rather than all in one go when you need the historical data).

But, once you denormalise data you have to make decisions as to how the denormalised data will be maintained (scripting can be fragile)

And, you have to have routines that will check/fix the denormalised data if the something untoward happens in the database and the data becomes corrupted/incomplete.

I would add that you will find it difficult to envisage all of the denorm'd requirements at the outset (especially when the project is being built as you go) which would also necessitate constant amendments to the denormalisation scripts.

So in short, unless you have large volumes of data - probably better to recalc when you need the comparatives etc.

HTH

Simon

Edited January 11, 2008 by Guest

[fiveshorts]

Thank you Simon, that's a big help and very sensible advice.

thanks.