Need help with changelog structure… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need help with changelog structure…

Hi, need help with a structure… I want to create a changelog, that somehow stores information about changes that were made on customer information recordset. The process should act as a kind of transaction log to reconstruct any customer information and trace back for statistical lineup. So, if I had a customer as follows: ID0001
created7/19/04 12:00 …and someone edited this Information, i.e. set the Name to "Pimp", I want to log this action, to restore on demand. I just thought on something like this entry: TimeStampTableFieldValue
7/19/04 12:00t_customerNamePim
7/19/04 12:00t_customerForenamePeter
7/19/04 12:00t_customerAgePimp
7/19/04 12:00t_customercreated7/19/04 12:00
7/20/04 12:00t_customerNamePimp<– Update customer info But this method seems not to be very effective to me =) In fact these informations are high frequently accessed and edited. And if I think of backtracking about 30k interest informations back to the first of april 2003, this can take some time. At least a statistical analysis can be driven via OLAP, but if someone wants information, the cubes can’t offer, there’s no chances to reassemble the lost infos. Has anyone a clue? Productive system is driven on W2K/SQL2000/IIS5/.Net1.1!
Thanks in advice… Greetz