History tracking and Csla objects

[Chicagoan2016]

We are asked to implement history tracking for approved 'records' in our application. We have audit tables but for some reason there is need for complete history tracking!
Our team has created an identical database with almost the same stored procs.
I think we could use the same Csla business objects we have for the 'active' database but we aren't sure about the exact details.
Any thoughts would be appreciated.

Edit: We are using Stored procs with ADO.NET

Kind Regards

[kcabral817]

Typically I don't ask deflecting questions, but would your organization be open to a DataMart or warehouse? This sounds like a perfect fit for an ETL that might provide more value, off-load the task to a different DB instance so as not to affect OLTP response times, etc.

[hurcane]

If you are using MSSQL as the database, you might want to look at temporal tables, which is a native way of keeping table history. You don’t have to change your editable business objects much at all. You’ll need new logic to report from the temporal table(s). You can change existing tables to temporal tables.

I had a project with an inventory history log that was used to report “as of” levels for any arbitrary date. It became very slow as the history table grew. We converted the bin quantity table to be temporal and wrote SQL to generate historical bin quantity records from the history table. The modified report went from 3 minutes to generate the data to less than 5 seconds.

[Chicagoan2016]

@hurcane I will look at temporal tables.
We need to track 'history' only for 'approved' records so kind of on-demand history tracking.

[rockfordlhotka]

I'm not much of a database expert, but it seems to me that SQL Server has something called change data capture (?) where it can generate a stream of all changes. You can use that stream to know everything that changed.